Retrieve SID name within Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Retrieve SID name within Stored Procedure

  1. #1
    Join Date
    Mar 2001
    Posts
    2

    Red face

    I am trying to compile the following stored procedure and getting the following error:

    PL/SQL: SQL Statement ignored
    PLS-00201: identifier 'SYS.V_$DATABASE' must be declared

    CREATE OR REPLACE PROCEDURE get_sid_name AS

    -- Declare

    lv_sid_name varchar2(9);

    Begin

    SELECT name
    INTO lv_sid_name
    FROM v$database;

    DBMS_OUTPUT.PUT_LINE(lv_sid_name);

    End;
    /

    I can run the SELECT minus the INTO clause in SQLPLUS and have no problems retrieveing the SID name. Can anyone think of a reason of why the code above would not compile???

  2. #2
    Join Date
    Jan 2001
    Posts
    515

    I had the same proble

    You have to log on as sys and grant the procedure select privilege on v$database. For some reason procedures can only use the priviliges granted to the owner that are not in roles. So when you run the code in sqlplus as the owner it will use the privileges that are part of the owner's roles, but when the procedure is stored in oracle it can only use privileges that the owner has that are not part of a role.

  3. #3
    Join Date
    Mar 2001
    Posts
    2
    Thanks....that was a tremendous help!!!

    rhasson

  4. #4
    Join Date
    Mar 2001
    Posts
    18
    "For some reason procedures can only use the priviliges granted to the owner that are not in roles."

    that's because of early binding, and the way explicitly granted privileges are evaluated at run time, and the ways roles can be set on/off.

Posting Permissions

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



Click Here to Expand Forum to Full Width