access to v$ dictionary views from the packaged procedure
411850Oct 31 2005 — edited Oct 31 2005Hi,
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