Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do I use views in a stored procedure?
A copy of this was sent to "Vaughan Mc Carthy" <vaughanm_at_medscheme.co.za>
(if that email address didn't require changing)
On Wed, 12 Aug 1998 11:36:32 +0200, you wrote:
>I want to create a procedure as system, with a select from dba_segments,
>which is a view. Whenever I try to compile this procedure, I get the
>following error: PLS-00201: identifier 'SYS.DBA_SEGMENTS' must be declared
>How do I get around this problem?
>
>Vaughan Mc Carthy
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in dbms_sql via a role. Grant the privelege (select on dba_segments) directly to the owner of the procedure and it'll work.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Aug 12 1998 - 10:33:49 CDT