Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

access to v$ dictionary views from the packaged procedure

411850Oct 31 2005 — edited Oct 31 2005
Hi,

I am having the problem trying to access v$parameter view from the packaged procedure within PL/SQL. I am using Oracle 9.2.0.7

I have SELECT_CATALOG_ROLE assigned to the user, and when I am running this query directly from Sql Plus it works fine.

declare
v_val varchar2(30);
begin
select value into v_val from v$parameter where name like 'instance_name%';
end;
/

Is also working fine, but

create or replace package test
is
function get_instance_name return VARCHAR2;
end;
/
show errors;

create or replace package body test
is
function get_instance_name return VARCHAR2
is
v_val VARCHAR2(30);
begin
select value into v_val
from v$parameter
where name like 'instance_name%';

return v_val;
end;
end;
/

Doesn't want to compile, saying that I have insufficiant privileges or table or view is not found!

How can I fix this?
Thanks in advice
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 28 2005
Added on Oct 31 2005
3 comments
422 views