Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » cannot select from dba_role_privs in FUNCTION - WHY?
cannot select from dba_role_privs in FUNCTION - WHY? [message #393384] Sun, 22 March 2009 07:46 Go to next message
roithi
Messages: 8
Registered: March 2006
Junior Member
Hi guys

When i do the following statement in my editor it works,

select count(*) from dba_role_privs;

but when i put this select statement into my function i get an compilation error "table or view not found".. how can that be?

code of the function:
create or replace function get_student
(p_schema in varchar2, p_tab in varchar2) return varchar2 as
e_ret varchar2(2000);
counter number;
begin
  select count(*) into counter from dba_role_privs;
end;
/

6/24 PL/SQL: ORA-00942: Tabelle oder View nicht vorhanden

the user has the role DBA,



Re: cannot select from dba_role_privs in FUNCTION - WHY? [message #393386 is a reply to message #393384] Sun, 22 March 2009 08:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Check your privs again.
You need to grant
select any dictionary

to the dba user.
Also please post how you are calling the function.

I was not sure about your processing logic/in parameters.
So just removed it.
  1  create or replace function get_student
  2  return number as
  3  counter number;
  4  begin
  5    select count(*) into counter from dba_role_privs;
  6    return counter;
  7*   end;
scott@chum > /

Function created.

scott@chum > select get_student from dual;

GET_STUDENT
-----------
	151

scott@chum > select count(*) from dba_role_privs;

  COUNT(*)
----------
       151

[Updated on: Sun, 22 March 2009 08:16]

Report message to a moderator

Re: cannot select from dba_role_privs in FUNCTION - WHY? [message #393389 is a reply to message #393386] Sun, 22 March 2009 08:55 Go to previous message
roithi
Messages: 8
Registered: March 2006
Junior Member
Quote:
select any dictionary


Thank you, that was the missing link!

Now it works!
Previous Topic: How to change Grid line color of "horizontal 3D Flash chart"
Next Topic: upgrade oracle 10g express edition
Goto Forum:
  


Current Time: Wed Dec 07 18:37:02 CST 2016

Total time taken to generate the page: 0.04992 seconds