Oracle : Can't access sys.v_$instance in a PL/SQL programm unit

Hi.

I tried to execute following SQL statement in a PL/SQL programm unit. But it raise an error when I try to compile the programme unit.

SQL> SELECT instr(lower(instance_name),'tst')
  2  FROM sys.v_$instance;

INSTR(LOWER(INSTANCE_NAME),'TST')
---------------------------------
                                3

SQL> CREATE OR REPLACE procedure test as
  2  ext number;
  3  begin
  4  SELECT instr(lower(instance_name),'tst')
  5  into ext
  6  FROM sys.v_$instance;
  7  dbms_output.put_line(ext);
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
6/10     PL/SQL: ORA-00942: table or view does not exist
SQL>

Please help.

Regards
Hiranya
hiranyaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
If you have access to this from sql*plus by describing it, but it doesn't work in a procedure, it is because your grant to the table is via a role. There are two ways around this. Have the dba do a DIRECT GRANT to your user, not through a role.

The reason is that pl/sql procedures are, by default, run with definer rights. Definer rights procedures suspend all roles while executing. If you turn this into an invoker rights procedure, then the procedure will execute with roles enabled, but whoever tries to run the procedure will also need rights to sys.v_$instance to run the procedure. This is the trade-off. Definer rights, you can grant execute on the procedure and anyone can do what the procedure enables, but the grants have to be direct, not roles.

Invoker rights, the roles stay active, but only those who have the grants to the objects in the procedure can access them.

To turn your procedure into an invoker rights procedure do this:

CREATE OR REPLACE procedure test AUTHID CURRENT_USER As
ext number;
begin
SELECT instr(lower(instance_name),'tst')
into ext
FROM sys.v_$instance;
dbms_output.put_line(ext);
end;
/

See here for additional information
http://www.experts-exchange.com/Databases/Oracle/Q_21762459.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hiranyaAuthor Commented:
RCorfman......

Thank you very much for your answer.
I granted the select on priveleges to v_$instance. Now its working. Thanks a lot.

Regards
Hiranya.
0
RCorfmanCommented:
invoker rights vs. definer rights is one of the items in Oracle that is not well understood... in fact, most people have never even heard of it. Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.