Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2004
    Posts
    17

    Unanswered: grants for dbms_lock

    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

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    grant via a role do not apply to stored objects. grant execute directly to the procedure owner
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Sep 2004
    Posts
    17
    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

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    whether dynamic or not, the grant must be executed from sys.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Sep 2004
    Posts
    17
    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

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    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/
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Sep 2004
    Posts
    17
    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

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    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.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Sep 2004
    Posts
    17
    yeah....i just explained the other way to do......So try to learn from others!!!

  10. #10
    Join Date
    Sep 2004
    Posts
    17
    Dont u know abt the meaning of Not possible ?

  11. #11
    Join Date
    Apr 2004
    Posts
    246
    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"?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  12. #12
    Join Date
    Sep 2004
    Posts
    17
    Oracle - DBForum is the place to discuss abt Oralce and not abt Not Possible.

  13. #13
    Join Date
    Apr 2004
    Posts
    246
    Then why did you previously ask "Dont u know abt the meaning of Not possible ?" You seem to be ignoring your own advice.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •