Solved

Help with ORA-00942 when compiling package

Posted on 2007-12-07
2
1,846 Views
Last Modified: 2008-02-01
Hi,
I am trying to compile a package in my custom schema. Where I am using v$sql and v$sql_bind_capture to capture all sqls with their bind values. I am getting PL/SQL: ORA-00942: table or view does not exist error. But from my custom schema I am able to execute the query and see the results. But it is giving the error when it thru a package. Can anyone help me. As these are SYS views do I need to create any synonym and views.

Regards

Sairam
CURSOR c_get_capture_sqls IS

		select distinct ash.client_id,du.name,

        	sql_bind_capture.value_string

        	from v$active_session_history ash,

        	dimuser du,

        	v$sql sql,

        	v$session sesion,

        	v$sql_bind_capture sql_bind_capture

         	where to_number(substr(ash.client_id,instr(ash.client_id,'/')+1))=du.dimuserid

         	AND ash.sample_time between sysdate - 60/2500 and sysdate

        	AND ash.sql_id = sql.sql_id (+)

        	And sql.HASH_VALUE = sql_bind_capture.HASH_VALUE

        	AND ash.SESSION_ID =sesion.SID (+)

        	AND ash.sql_id = sql_bind_capture.sql_id;

Open in new window

0
Comment
Question by:sairamb
2 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 20427868
The problem is privileges.

Your account probably is granted the DBA role.  PL/SQL does not understand privileges granted through a role.  Never has, and probably never will.  You need to log in as SYS and grant the select privileges directly to your user.
0
 
LVL 9

Accepted Solution

by:
joebednarz earned 125 total points
ID: 20428542
I agree with the problem being a priv not granted.

One thing to remember with PL/SQL... if you create a procedure/package/function, by default, the privileges that are in place for the owning schema are the ones "given" to the user who is executing.  So, for instance:

SQL> connect scott

SQL> CREATE FUNCTION scott.show_emp
IS
tmp VARCHAR2(30);
BEGIN
  SELECT ename INTO tmp FROM emp WHERE empno=123;
  RETURN tmp;
END;
/

SQL> REVOKE select ON emp FROM joe;
SQL> GRANT execute ON show_emp TO joe;

SQL> connect joe
SQL> SELECT * FROM scott.EMP;  // returns an error

SQL> SELECT scott.show_emp FROM dual;  // returns employee #123's name

So, if you desire to have your PL/SQL procedure return data from a particular table, most likely, the schema owner (for the table) has not granted privs on the tables to the schema owner (for the procedure).  Finally, grant execute privilege for the procedure to whomever needs to get the data.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now