Delays in Oracle

[Guest_imported]

I'm trying to create a stored procedure that has a delay in Oracle. I know in SQL Server you can use:

WAITFOR DELAY '00:00:10'

and it will pause for 10 seconds.

How would I accomplish this in oracle?

 

[ThomVF]

execute dbms_lock.sleep(60); -- 60 seconds

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Guest_imported]

I've tried using dbms_lock.sleep(30); in my stored procedure, but I get compilation errors. When I just run:

BEGIN
dbms_lock.sleep(30);
END;

it works fine, but I've tried everything I can think of to try to get it to work in my stored procedure. I've tried nested blocks, with execute, without execute, and many other things but I always get this error: "PL/SQL procedure/package was created with compilation errors."

The stored procedure I'm trying to create is below. Any help is appreciated.

CREATE OR REPLACE PROCEDURE
pos_sp_delayproc(InpSource IN char := 'P', InpData IN number := 0, RetCode OUT number, RetString OUT char)
AS
BEGIN
execute dbms_lock.sleep(30);
RetCode := 0;
RetString := 'Delayed for 30 Seconds!';
INSERT INTO stored_procedure_call values('pos_sp_delayproc',InpSource,NULL,InpData,NULL,RetCode,RetString,sysdate());
END;

 

[Turkbear]

Hi,
a couple of things:

Did you try to view the error using 'show error' when in SqlPlus?
If so, what error was reported?
Does the owner of the SP have sufficient rights to run the dbms_lock built_in proc ?
Have you tried with 'execute immediate'?

 

[sem]

You should be granted EXPLICIT(not via role) right to EXECUTE dbms_lock package.

 

[jee]

You don't use the "Execute" command from PL/SQL - so that will never work. I would suspect a priv problem related to the "owner" of the stored procedure - or perhaps the error is somewhere else?? Try removing the '()' that follows SYSDATE in your insert statement.

 

[karluk]

Sorry to disagree with you, jee, but I think sem is right. Having privileges granted through a role rather than explicitly to an id is a common cause of this kind of problem.

However, Turkbear has the best advice of all - use the "show errors". Right now we are just guessing because we don't know what the actual error is.

 

[Guest_imported]

I tried again this time without the 'execute' call and used 'show error' and the error I got was:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2 PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
5/2 PL/SQL: Statement ignored
8/2 PL/SQL: SQL Statement ignored
8/14 PLS-00201: identifier 'STORED_PROCEDURE_CALL' must be declared

Using this procedure:

CREATE OR REPLACE PROCEDURE
pos_sp_delayproc(InpSource IN char := 'P', InpData IN number := 0, RetCode OUT number, RetString OUT char)
AS
BEGIN
dbms_lock.sleep(30);
RetCode := 0;
RetString := 'Delayed for 30 Seconds!';
INSERT INTO stored_procedure_call values('pos_sp_delayproc',InpSource,NULL,InpData,NULL,RetCode,RetString,sysdate());
END;

I've tried using execute immediate but that gives the same error as without any execute call. Thanks for your help so far, I'm not used to actually getting a response from these things

 

[Turkbear]

Is the owner of the proc the same one you logged in as when you did
the

BEGIN
 dbms_lock.sleep(30);
END;

From the errors it appears that the proc has no rights to sys.dbms_lock or to the table stored_procedure_call .
( Use the fully qualified name for the table in any case)

 

[karluk]

The problem is as sem indicated - the id has execute rights on dbms_lock through a role rather than granted directly to the id. That will cause the kind of error you are seeing.

You have two choices - have sys grant execute on dbms_lock to the id you're using, or to use the sys id to create pos_sp_delayproc and grant execute on it to the desired id.

As Turkbear indicates, you also have an unrelated error on stored_procedure_call. That may also be a permissions problem.

 

[Guest_imported]

I'm attempting to create the stored proc as the same user that ran:

BEGIN
dbms_lock.sleep(30);
END;

Is there a way to specifically state the owner of the proc when it is created? I'm fairly new to Oracle so I don't know alot...

 

[Guest_imported]

Hmmm, thanks for your speedy replies.

The last question I have is basically how would I got about using the sys id to create the stored proc and grant execute on it to this id? If anyone can give me a bit of help or point me in the right direction I would reall appreciate it.

 

[sem]

The problem with referencing "foreign" objects in stored procedures has its origin in Oracle's "early binding". All references are normally resolved (and permissions are checked) during compile time rather than during runtime. Using SET ROLE may cause procedure to be invalidated during a session. So the roles are prohibited in stored procedures. That's why your anonymous block runs well, however your stored procedure doesn't. You shouldn't create procedure under SYS, you need EXPLICIT right to execute SYS's procedure only:

grant execute on dbms_lock to <YOU>

Creating procedures (as well as any other object) as SYS is really bad idea.
And again, even if SYS creates procedure in your schema, it will be compiled using your rights, thus will not be compiled. So ask your DBA to grant you this right.