• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1882
  • Last Modified:

Help with ORA-00942 when compiling package

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.


CURSOR c_get_capture_sqls IS
		select distinct ash.client_id,du.name,
        	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

1 Solution
johnsoneSenior Oracle DBACommented:
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.
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
tmp VARCHAR2(30);
  SELECT ename INTO tmp FROM emp WHERE empno=123;
  RETURN tmp;

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.

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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now