Stored Procedure fails to compile

I have a stored procedure that refers to sys view dba_tables. When I try to compile this procedure(I am using Toad), it fails giving me following error:
PL/SQL: ORA-00942: table or view does not exist
I have verified that this view does exists in schema sys.
Note: The user I am using to log-in has dba previledges.

Here is my stored procedure:
CREATE OR REPLACE PROCEDURE procSearchString (searchString IN VARCHAR2) IS
myTable       VARCHAR2(180);
myColumn       VARCHAR2(180);
strSQL            VARCHAR2(10000);
colName     VARCHAR2(1024);
strPattern  VARCHAR2(1024);
i                  INTEGER;
t_exist        PLS_INTEGER;
CURSOR myCur is
select dba_tables.table_name as myTable, dba_tab_columns.column_name as myColumn from dba_tables, dba_tab_columns where dba_tables.owner='SA' and dba_tab_columns.DATA_TYPE = 'VARCHAR2' and dba_tables.TABLE_NAME <> 'search_result' and dba_tables.TABLE_NAME=dba_tab_columns.TABLE_NAME order by dba_tables.table_name;
--Above line fails to compile
BEGIN
blah blah...
END procSearchString;
/
daddy007Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
This is a simple privilege issue as drs66 indicated.  Oracle does not use roles in PL\SQL, so even though the user can select from these tables (actually views)  in SQL*Plus or TOAD because of the DBA role, that doesn't help for a PL\SQL procedure.  You need to log in as the owner of the objects (SYS) then for each one:
"grant select on [object_name] to [your user name];"
0
 
Daniel StanleyDatabase engineerCommented:
role privs are void in pl/sql.

as sys user grant select priviledge on dba_tables and dba_tab_columns

regards,
daniels
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
First of all is this query working in sql. I mean just take the select statement and try to run it.

if the select itself is not working in sqlplus or toad, then
1) does this user have privilege on select_catalogue_role ?
2) does this user have select any table privilege
 
from dba user:
grant select any table to user1;

If works in sql, but it doesnt work in pl/sql, then you can go ahead as per daniel.

grant select on dba_tables to user1; similarly for the other tables which you need.

Thanks
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
awking00Commented:
What happens if you add the authid current_user statement?

CREATE OR REPLACE PROCEDURE procSearchString (searchString IN VARCHAR2)
AUTHID CURRENT_USER IS
myTable      VARCHAR2(180);
...
0
 
sadia_rubbaniCommented:
Hello,

          the actual problem is that the table exist in sys schema and your login user has only dba right. solution is that you login as sys and grant the right of select, delete, update on the following table to your user----> mean sys user grant the rights to your user. then you login by your user and compile this procedure. definetly it will work.

if you have any other question, write me on my e-mail address.
0
 
Daniel StanleyDatabase engineerCommented:
sadia_rubbani,  that is not a correct solution, you should study his/her question further and think about what you are advising him/her to do.   mine and markgeer's solutions are accurate, period. why would he/she want to grant delete or update privs on a dba_% view?  his/her problem is simple, his/her user has the dba role and can see the view from regular select statements ... but role priviledges are not valid inside a pl/sql program unit.  once he/she grants explicit select privs on that particular sys owned view his/her program will work.

regards,
daniels
0
 
Daniel StanleyDatabase engineerCommented:
i hate it when i provide a solution and don't get a reward.

:-(
0
 
Mark GeerlingsDatabase AdministratorCommented:
To drs66:

Sorry, that happens to me sometimes too.  In this case you did correctly identify the problem and provide a very brief solution.  Apparently though, daddy007 appreciated the explanation that I added.
0
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.

All Courses

From novice to tech pro — start learning today.