Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: PL/SQL:How to make sure a function exists?

    Well, I facing "Insufficient Privileges" problem while running a PL/SQL function:

    Here are the steps I followed: In my Windows command prompt I logged in as follows:

    Code:
    sqlplus / as sysdba
    I got the SQL> prompt over there and then I tried to grant privilige on my function "age" as follows:

    Code:
     SQL> GRANT EXECUTE on AGE to SCOTT;
    where, AGE is the function name and SCOTT is the user

    I'm referring the following documentation:

    Oracle/PLSQL: Grant/Revoke Privileges

    I got the error ORA-0402: function body doesn't exist. How can I save my function body before issuing GRANT ?

    Please help !

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    with SQL when you are logged into the database, you can only access your own objects;
    unless & until you explicitly state that the object belongs to a different user.

    >GRANT EXECUTE on AGE to SCOTT;
    the statement above will only succeed when it is issued by the same user that owns & CREATED the AGE function.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2013
    Posts
    41
    I'm surprised to notice that despite getting above error in command prompt which I mentioned in my question, when I tried to run query for creating the function in SQL Plus, I got the message " Function Created". And it worked for me but still wondering how did it happen ?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't know what you see.
    I don't know what you do.
    I can't be sure that you accurately reported what you think you saw & did.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Quote Originally Posted by Jack_Tauson_Sr View Post
    I'm surprised to notice that despite getting above error in command prompt which I mentioned in my question, when I tried to run query for creating the function in SQL Plus, I got the message " Function Created". And it worked for me but still wondering how did it happen ?
    As anacedent said, in your first post you failed to run a function that's owned by another user because you didn't specify the owner
    e.g. GRANT EXECUTE on <OWNER>.AGE to SCOTT;

    In your last post you created a function in your own schema (i.e. you are it's owner). You don't need to specify the owner's name as you are the owner.

    I suggest that you don't understand fully the concept of 'USER' and 'SCHEMA' in an Oracle context. Try reading this article:
    Understand Exactly What a Schema Is
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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