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
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???
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.
Thanks....that was a tremendous help!!!
"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.
Click Here to Expand Forum to Full Width