Home » SQL & PL/SQL » SQL & PL/SQL » Package to include a sleep inside a procedure (Linux 2.6.39-400.286.3.el6uek.x86_64 - Oracle 11.2.0.4.0)
Package to include a sleep inside a procedure [message #663398] Thu, 01 June 2017 20:08 Go to next message
Nagesh1985
Messages: 10
Registered: April 2016
Junior Member
Hi,
Users are requesting execute access to DBMS_LOCK package to incorporate in their procedure. As a DBA, I am not very comfortable providing that. What are the risks involved in giving DBMS_LOCK access to application users ? Is there any alternative that we can use instead ?
Oracle is suggesting a cover package for this (lock_100_to_200), but the syntax does not work.

Thanks
Nagesh
Re: Package to include a sleep inside a procedure [message #663399 is a reply to message #663398] Thu, 01 June 2017 20:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I would ask which of the below is desired & why?
SQL> DESC DBMS_LOCK
PROCEDURE ALLOCATE_UNIQUE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME			VARCHAR2		IN
 LOCKHANDLE			VARCHAR2		OUT
 EXPIRATION_SECS		NUMBER(38)		IN     DEFAULT
PROCEDURE ALLOCATE_UNIQUE_AUTONOMOUS
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME			VARCHAR2		IN
 LOCKHANDLE			VARCHAR2		OUT
 EXPIRATION_SECS		NUMBER(38)		IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID				NUMBER(38)		IN
 LOCKMODE			NUMBER(38)		IN
 TIMEOUT			NUMBER			IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE			VARCHAR2		IN
 LOCKMODE			NUMBER(38)		IN
 TIMEOUT			NUMBER			IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID				NUMBER(38)		IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE			VARCHAR2		IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID				NUMBER(38)		IN
 LOCKMODE			NUMBER(38)		IN     DEFAULT
 TIMEOUT			NUMBER(38)		IN     DEFAULT
 RELEASE_ON_COMMIT		BOOLEAN 		IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE			VARCHAR2		IN
 LOCKMODE			NUMBER(38)		IN     DEFAULT
 TIMEOUT			NUMBER(38)		IN     DEFAULT
 RELEASE_ON_COMMIT		BOOLEAN 		IN     DEFAULT
PROCEDURE SLEEP
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS			NUMBER			IN
Re: Package to include a sleep inside a procedure [message #663400 is a reply to message #663399] Thu, 01 June 2017 21:11 Go to previous messageGo to next message
Nagesh1985
Messages: 10
Registered: April 2016
Junior Member
I am specifically looking at the SLEEP function.
We need a SLEEP function inside the procedure which basically serves to exit the loop when the threshold is reached. The threshold can be anywhere between 5 seconds to 5 hours.
Re: Package to include a sleep inside a procedure [message #663401 is a reply to message #663400] Thu, 01 June 2017 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Nagesh1985 wrote on Thu, 01 June 2017 19:11
I am specifically looking at the SLEEP function.
We need a SLEEP function inside the procedure which basically serves to exit the loop when the threshold is reached. The threshold can be anywhere between 5 seconds to 5 hours.
Since EXECUTE applies at the package level, I would create a new package that only invokes DBMS_LOCK.SLEEP & GRANT EXECUTE on the new package to the application schema owner.
Re: Package to include a sleep inside a procedure [message #663402 is a reply to message #663401] Thu, 01 June 2017 22:25 Go to previous messageGo to next message
Nagesh1985
Messages: 10
Registered: April 2016
Junior Member
Can you help in coding the package only for the SLEEP function. I am not very proficient with PL/SQL

Nagesh
Re: Package to include a sleep inside a procedure [message #663404 is a reply to message #663402] Thu, 01 June 2017 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Let me help you to write it.
First write the code to create a procedure that takes one parameter of type INTEGER and does nothing, you should be able to do it as you wrote one in your previous topic.

Re: Package to include a sleep inside a procedure [message #663408 is a reply to message #663402] Fri, 02 June 2017 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
A procedure that does nothing other than call another procedure is about as simple as PL/SQL gets.
Stop convincing yourself it's hard and give it a try.
Re: Package to include a sleep inside a procedure [message #663417 is a reply to message #663408] Fri, 02 June 2017 08:14 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
it actually very easy to do. Make the following procedure in a privileged account like system. setup up a public synonym and since the procedure is running with definer rights it has full access to the dbms_lock without actually giving access to the full DBMS_LOCK


create or replace PROCEDURE Sleep (V_number_of_seconds IN NUMBER)
   IS
BEGIN
  DBMS_LOCK.Sleep (V_number_of_seconds);
  RETURN;
END Sleep;
/

create public synonym sleep for system.sleep;
GRANT EXECUTE ON SLEEP TO PUBLIC;

[Updated on: Fri, 02 June 2017 09:31]

Report message to a moderator

Re: Package to include a sleep inside a procedure [message #663421 is a reply to message #663417] Fri, 02 June 2017 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You give him no chance to learn. Smile

Re: Package to include a sleep inside a procedure [message #663429 is a reply to message #663421] Fri, 02 June 2017 13:33 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
sorry Michel, I just realized that he is probably still in class. Your right (of course)
Previous Topic: Passing value from one cursor to the other one.
Next Topic: Sequence generation for Parallel Processing
Goto Forum:
  


Current Time: Thu Mar 28 23:45:45 CDT 2024