How do I use views in a stored procedure?

[Vaughan Mc Carthy]

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

[Thomas Kyte]

A copy of this was sent to "Vaughan Mc Carthy" <vaug...@medscheme.co.za>
(if that email address didn't require changing)

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
tk...@us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

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.

[Vaughan Mc Carthy]

Thanks
I eventually got around the problem, by granting select on the base tables
to system, as sys. I then created a view on these base tables as system and
called it another name. I then used these views in my stored procedure.
Vaughan

[Jonathan Gennick]

On Wed, 12 Aug 1998 11:36:32 +0200, "Vaughan Mc Carthy"
<vaug...@medscheme.co.za> 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?

I think you would need to logon as SYS, and explicitly grant
privs on the view to SYSTEM, i.e.

grant select on sys.dba_segments to system.

Roles don't apply to queries written within a stored
procedure.

Jonathan

[EXE]

> 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?

Have your DBA grant you SELECT ANY TABLE and give it to your user account, not
to one of your roles.

Regards,

Daniel A. Morgan