?
Solved

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

Posted on 2006-03-28
3
Medium Priority
?
1,166 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:hiranya
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
RCorfman earned 1000 total points
ID: 16318348
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
 

Author Comment

by:hiranya
ID: 16318603
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16318607
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question