Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOCK'.sleep not working. Alternate?
DBMS_LOCK'.sleep not working. Alternate? [message #179994] Thu, 29 June 2006 10:54 Go to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
We're having troubles using the DBMS_LOCK.Sleep() procedure. The error is:

Error Text = PLS-00201: identifier 'DBMS_LOCK' must be declared

for

dbms_lock.sleep(120);

We figure it has something to do with privledges to execute the procedure as the command seems to exist when we use describe DBMS_LOCK. Is there an alternative procedure that can used to perform a sleep. We're looking for 1 minute to 2 minute sleep time before subsequent statements are executed.

Thanks.
Re: DBMS_LOCK'.sleep not working. Alternate? [message #179997 is a reply to message #179994] Thu, 29 June 2006 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SYS.dbms_lock.sleep(120);
Re: DBMS_LOCK'.sleep not working. Alternate? [message #180015 is a reply to message #179994] Thu, 29 June 2006 16:40 Go to previous messageGo to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
We tried that prefix too but still the same error message.
Re: DBMS_LOCK'.sleep not working. Alternate? [message #180017 is a reply to message #179994] Thu, 29 June 2006 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please use cut & paste and show exactly how you are invoking it.
Re: DBMS_LOCK'.sleep not working. Alternate? [message #180030 is a reply to message #179994] Thu, 29 June 2006 20:44 Go to previous messageGo to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
CREATE OR REPLACE PROCEDURE Tourism_Cache_Refresh
(WhoCalling PLS_INTEGER) AS
PRAGMA autonomous_transaction;
v_job_exists pls_integer;
v_html varchar2(32767):='@';
loopcnt pls_integer := 0;
jobno number;
BEGIN
IF (WhoCalling = 0) THEN
SELECT count(*) INTO v_job_exists FROM USER_JOBS WHERE LOWER(what) like '%tourism_cache_refresh(1);%';
IF v_job_exists = 0 THEN
DBMS_JOB.SUBMIT(jobno, 'BEGIN Tourism_Cache_Refresh(1); END;', SYSDATE+5/1440, null);
COMMIT;
END IF;
ELSE
utl_http.set_response_error_check(false);
WHILE v_html != 'TourismCacheRefreshInitiated=1' AND loopcnt < 10
LOOP
IF (loopcnt > 0) THEN
dbms_lock.sleep(120);
END IF;
v_html := utl_http.request('http://www.MySite.com?db=1');
loopcnt := loopcnt + 1;
END LOOP;
END IF;
END;

[Updated on: Thu, 29 June 2006 20:44]

Report message to a moderator

Re: DBMS_LOCK'.sleep not working. Alternate? [message #180032 is a reply to message #179994] Thu, 29 June 2006 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
grant execute on dbms_lock to luser; -- schema who owns procedure
Re: DBMS_LOCK'.sleep not working. Alternate? [message #238155 is a reply to message #179994] Wed, 16 May 2007 21:25 Go to previous messageGo to next message
programmer.ph
Messages: 1
Registered: May 2007
Location: Philippines
Junior Member
execute the DBMS Lock to public

GRANT EXECUTE ON DBMS_LOCK TO PUBLIC;

Re: DBMS_LOCK'.sleep not working. Alternate? [message #238203 is a reply to message #238155] Thu, 17 May 2007 01:50 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never grant something to public if it is not necessary to everybody.
Just grant a privilege to those who need it.

Regards
Michel
Previous Topic: Conditional Compilation in PLSQL
Next Topic: Correlated sub-query
Goto Forum:
  


Current Time: Sat Dec 03 01:20:38 CST 2016

Total time taken to generate the page: 0.07434 seconds