Solved

Stored Procedure fails to compile

Posted on 2006-11-17
8
927 Views
Last Modified: 2008-01-09
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;
/
0
Comment
Question by:daddy007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17970624
role privs are void in pl/sql.

as sys user grant select priviledge on dba_tables and dba_tab_columns

regards,
daniels
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17971526
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
 
LVL 32

Expert Comment

by:awking00
ID: 17971637
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 100 total points
ID: 17972101
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
 

Expert Comment

by:sadia_rubbani
ID: 17977207
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
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17977252
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
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 17982873
i hate it when i provide a solution and don't get a reward.

:-(
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17983053
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

630 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