Solved

Stored Procedure fails to compile

Posted on 2006-11-17
8
858 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 100 total points
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:sadia_rubbani
Comment Utility
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
Comment Utility
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
Comment Utility
i hate it when i provide a solution and don't get a reward.

:-(
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 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

10 Experts available now in Live!

Get 1:1 Help Now