Re: v$parameter

Super-Duper-

Access to this sys-owned view was probably granted via a role. Roles are suspended during stored procedure execution under the current releases. To fix this, log in as sys and explicitly grant select on this view to the procedure owner.

It's a weird, wonderful world Oracle lives in, but it really does make sense. It's a security thing to prevent insidious security holes in complex systems.

Roger

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

Super Kev wrote:
>
> Just a quickie....
>
> why is it that the following in SQL*Plus is fine..
>
> select value
> from v$parameter
> where name = 'utl_file_dir';
>
> whereas the following procedure produces the following error...
>
> create or replace procedure test_v$param as
>
> l_value varchar2(250);
>
> begin
>
> select value into l_value
> from v$parameter
> where name = 'utl_file_dir';
>
> end;
> /
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 4/1 PL/SQL: SQL Statement ignored
> 5/6 PLS-00201: identifier 'SYS.V_$PARAMETER' must be declared
>
> both are created by the same user.
>
> cheers in advance.
Received on Sat Dec 19 1998 - 13:04:20 CST