| DBMS_LOCK'.sleep not working. Alternate? [message #179994] |
Thu, 29 June 2006 10:54  |
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  |
anacedent
Messages: 7061
Registered: July 2005
Location: +32° 58' 41.00"N -1... |
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 |
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 |
anacedent
Messages: 7061
Registered: July 2005
Location: +32° 58' 41.00"N -1... |
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 |
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]
|
|
|
| Re: DBMS_LOCK'.sleep not working. Alternate? [message #180032 is a reply to message #179994 ] |
Thu, 29 June 2006 21:00 |
anacedent
Messages: 7061
Registered: July 2005
Location: +32° 58' 41.00"N -1... |
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 |
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 |
Michel Cadot
Messages: 26624
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Never grant something to public if it is not necessary to everybody.
Just grant a privilege to those who need it.
Regards
Michel
|
|
|
]
Members
Search
Help
Register
Login
Home
