Retrieve SID name within Stored Procedure

[rhasson]

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???

[lesstjm]

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.

[rhasson]

Thanks....that was a tremendous help!!!

rhasson

[mkeefe]

"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.