Hi,
I want to use the dbms_lock in a pl/sql procedure. The EXECUTE_CATALOG_ROLE has granted execute rights on this package. So I granted that role to my user and tried to compile my procedure, but:
PLS-00201: identifier 'DBMS_LOCK' must be declared
After direct grant of dbms_lock to the user the procedure compiles without any error.

Do you have any idea, what the problem is?

Thanks
Alex

grant via a role do not apply to stored objects. grant execute directly to the procedure owner

Hi,
I'm really not a newby with Oracle ... but you are right
Is there no other way than granting the dbms_lock directly - via dynamic sql or so? I don't like another connection to sys in my installation routine ...

Alex

whether dynamic or not, the grant must be executed from sys.

okay, thank you, so I will do that.
Is there any reason, why dbms_lock is not grantable by system, like other dbms packages are?

Alex

no idea what oracle's thinking on that one. e.g., dbms_lock is not granted to system at all, dbms_aq is granted to system with grant privs, dbms_alert is granted to system without grant privs.

you could grant execute to system (or some other user) with grant option, then do grants to other users from that schema/

Hi,

Are u using DBMS_LOCK inbuilt procedure in a PLSQL package/PLSQL Procedure, then it is not possible.

The other alternative is,

save the procedure with the extension .sql and run it from the command promt.Now the DBMS_LOCK Procedure will work fine.

To run the SQL Script :

i) go to the directory where u actually created the Script
ii) Get connected to the Database. say SQLPLUS username/password@database
iii)@script_name.sql [ where script_name is the name of the script]

If u have any input parameters , then give it while running the script like
@script_name.sql param1 param2
and access the param1 and param2 inside the script as $1 [param1] and $2 [param2].

or To get the input paramater inside the script :
accept var_name prompt 'enter path?' [ var_name not needed to initialize]
And u can the var_name inside the script using &.[ say &var_name...]

I hope this info will help.

Thanks ,
Swarna

what do you mean "it is not possible"? I just explained that it is possible, and simply requires granting execute to the user. Try reading the other responses - you may learn something.

yeah....i just explained the other way to do......So try to learn from others!!!

Dont u know abt the meaning of Not possible ?

you said "Are u using DBMS_LOCK inbuilt procedure in a PLSQL package/PLSQL Procedure, then it is not possible."

It is very much possible to use dbms_lock within a stored pl/sql object (package, procedure, function). It is simply required to grant execute to the owner of the stored object. Maybe you don't understand the meaning of "not possible"?

Oracle - DBForum is the place to discuss abt Oralce and not abt Not Possible.

Then why did you previously ask "Dont u know abt the meaning of Not possible ?" You seem to be ignoring your own advice.