Home » SQL & PL/SQL » SQL & PL/SQL » How to resolve the problem of : identifier 'DBMS_LOCK' must be declared
How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245573] Mon, 18 June 2007 02:57 Go to next message
vikram.goswami
Messages: 5
Registered: June 2007
Location: Banagalore
Junior Member
*************************************************************
*************************************************************
I am getting error in compiling the function TestLock.

The error is as:
identifier 'DBMS_LOCK' must be declared
*************************************************************
*************************************************************

CREATE OR REPLACE FUNCTION TestLock RETURN NUMBER IS
tmpVar NUMBER;
v_lockhandle varchar2(200);
v_result number;

BEGIN
tmpVar := 0;
SYS.dbms_lock.sleep(5);

execute dbms_shared_pool.keep('DBMS_LOCK');

execute dbms_lock.allocate_unique('control_lock', v_lockhandle);


v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);

if v_result <> 0 then
dbms_output.put_line(
case
when v_result=1 then 'Timeout'
when v_result=2 then 'Deadlock'
when v_result=3 then 'Parameter Error'
when v_result=4 then 'Already owned'
when v_result=5 then 'Illegal Lock Handle'
end);
end if;


RETURN tmpVar;
END TestLock;

/


Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245583 is a reply to message #245573] Mon, 18 June 2007 03:08 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. Check that you have EXECUTE rights on DBMS_LOCK.
2. Check there is a valid public synonym on it.

HTH.
Michael
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245586 is a reply to message #245573] Mon, 18 June 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.

Regards
Michel
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245606 is a reply to message #245586] Mon, 18 June 2007 03:58 Go to previous messageGo to next message
vikram.goswami
Messages: 5
Registered: June 2007
Location: Banagalore
Junior Member
I tried granting access on DBMS_LOCK to PUBLIC, but getting error as mentioned below:
--------------------------------------------------------------

SQL> GRANT EXECUTE ON DBMS_LOCK TO PUBLIC;
GRANT EXECUTE ON DBMS_LOCK TO PUBLIC
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_LOCK


SQL> GRANT EXECUTE ON DBMS_LOCK TO luser;
GRANT EXECUTE ON DBMS_LOCK TO luser
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_LOCK


SQL>
--------------------------------------------------------------
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245610 is a reply to message #245606] Mon, 18 June 2007 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe someone is currently using dbms_lock.

Once again:
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts

Regards
Michel
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245611 is a reply to message #245586] Mon, 18 June 2007 04:03 Go to previous messageGo to next message
vikram.goswami
Messages: 5
Registered: June 2007
Location: Banagalore
Junior Member
The version of SQL*Plus is:

SQL*Plus: Release 9.2.0.7.0
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245618 is a reply to message #245610] Mon, 18 June 2007 04:09 Go to previous messageGo to next message
vikram.goswami
Messages: 5
Registered: June 2007
Location: Banagalore
Junior Member
Thanks Michel,

I have some quries which i could not found on google.

1.How do I find out who is currntly using DBMS_LOCK.
2.If I do not have access to use DBMS_LOCK then can I ask my DB admin to provide me access?
3. Is there any chances of missing out DBMS_LOCK in-built package whicle installation of oracle?
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245620 is a reply to message #245618] Mon, 18 June 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. query v$access
2. why not? You have to justify why you want to use it. He has to justify why he doesn't want to use it if he doesn't want to grant you the access
3. Very less likely, it is part of the standard database creation. Query dba_objects

Regards
Michel
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #245627 is a reply to message #245620] Mon, 18 June 2007 04:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As an addition to Michel's answers:
make very sure that you actually need dbms_lock. This is a powerful and potentially dangerous package, that is hardly ever needed.
There are some usages for it, but too often these involve programmatic referential integrity...
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #388220 is a reply to message #245627] Mon, 23 February 2009 22:27 Go to previous messageGo to next message
immaad
Messages: 1
Registered: February 2009
Junior Member
You should write a wrapper on DBMS_LOCK functions to be safe and grant access to those functions.

check this ...
http://hashfactor.wordpress.com/2009/02/24/using-the-oracle-dbms_lock-package/
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #428520 is a reply to message #245573] Wed, 28 October 2009 10:45 Go to previous messageGo to next message
keaydin
Messages: 1
Registered: October 2009
Location: Cedar Grove
Junior Member

Hi,

I have a similar requirement in my procedure, and I dont have access to DBMS_LOCK with the user I need to write the proc.

It will be almost impossible to get additional access since it's a tightly controlled environment.

Are there any additional solution for delaying execution of the next command other than sleep?

Thanks,
Kemal
Re: How to resolve the problem of : identifier 'DBMS_LOCK' must be declared [message #428525 is a reply to message #428520] Wed, 28 October 2009 11:50 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only solution is the one mentioned by immaad: create a wrapper procedure to call the function you are allowed to execute.

Regards
Michel
Previous Topic: Missing right parenthesis?
Next Topic: updating a LONG column (merged)
Goto Forum:
  


Current Time: Sat Sep 24 18:10:31 CDT 2016

Total time taken to generate the page: 0.08533 seconds