Home » SQL & PL/SQL » SQL & PL/SQL » stored procedure with curson quering data dictonary view
stored procedure with curson quering data dictonary view [message #224802] Thu, 15 March 2007 09:28 Go to next message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
Hi,

I don't get it....I do not have a lot of pl/sql development experiance please help:

this (anonymous block) works:
declare
cursor c1 is
select file_name from dba_data_files;
rec varchar2(255);

begin
open c1;
loop
fetch c1 into rec;
EXIT WHEN c1%NOTFOUND;
dbms_output.PUT_LINE(rec);
end loop;
close c1;
end;


and this doesn't:
create or replace
procedure test2 is


cursor c1 is
select file_name from dba_data_files;
rec varchar2(255);

begin
open c1;
loop
fetch c1 into rec;
EXIT WHEN c1%NOTFOUND;
dbms_output.PUT_LINE(rec);
end loop;
close c1;
end test2;


it returns me an error ora-00942 table or view doesn't exist.
I have select_catalog_role.....the anonymous block works.
When I use a table in my own schema it works fine.

Can anybody tell me what I'm missing?

regards,
Martin
Re: stored procedure with curson quering data dictonary view [message #224807 is a reply to message #224802] Thu, 15 March 2007 09:47 Go to previous messageGo to next message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
found it......roles do not apply for stored procedures, i granted the user to have select on the data dictionary view, now eveything works fine.
Re: stored procedure with curson quering data dictonary view [message #224808 is a reply to message #224802] Thu, 15 March 2007 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
My car doesn't work.
Tell me how to make it go.
A clueless post deserves a contentless response.
Re: stored procedure with curson quering data dictonary view [message #224809 is a reply to message #224802] Thu, 15 March 2007 09:49 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This may be the problem.
If not, try giving select any dictionary privilege to the user.

Edit: I was late. Laughing

By
Vamsi

[Updated on: Thu, 15 March 2007 09:50]

Report message to a moderator

Re: stored procedure with curson quering data dictonary view [message #224817 is a reply to message #224809] Thu, 15 March 2007 10:41 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Well I have stepped into role limits a few times...
"Role Limlitation.
In general, a user cannot acquire a DML privilege needed to perform a DDLoperation via role.The user must be explicitly granted the necessary object privilege.A user creating a view on another user's table, cannot receive the privilege to select from the table through a role."
Re: stored procedure with curson quering data dictonary view [message #224885 is a reply to message #224808] Thu, 15 March 2007 17:48 Go to previous message
meggens
Messages: 57
Registered: February 2007
Location: Netherlands
Member
anacedent wrote on Thu, 15 March 2007 15:47
My car doesn't work.
Tell me how to make it go.
A clueless post deserves a contentless response.


I think I was clear enough by showing the first example is an anonymous pl/sql block and the second one a stored procedure.

anyway thanks for your respons.
Previous Topic: PLS-00905 -- Why am I getting this
Next Topic: Rename Constraint
Goto Forum:
  


Current Time: Mon Dec 05 02:30:23 CST 2016

Total time taken to generate the page: 0.08035 seconds