Solved

Stored Procedure fails to compile

Posted on 2006-11-17
8
901 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
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
Independent Software Vendors: 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!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 80
SQL Workhours Count beetween Workhours 3 39
Excess Redo 3 32
What are the limitations of input parameters in oracle ? 5 63
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

740 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