Hello,
I have 2 schemas, each with it´s own package and methods.
/* package in schema A */
create or replace package BIP_PKG authid current_user is
/* GLOBAL VARIABLES*/
G_SCHEMA varchar2(200 char);
end BIP_PKG;
/
/* package in schema B */
create or replace package body ng_session_pkg as
(...)
function get_report_parameter(
p_parameter_name in varchar2
)return varchar2
as
v_sql_action clob;
v_SESSION_USER constant varchar2 (30 char) := SYS_CONTEXT('USERENV','SESSION_USER');
v_parameter_value varchar2 (500 char);
begin
v_sql_action := 'declare v_parameter_value varchar2(100 char); begin select '|| v_SESSION_USER||'.bip_pkg.'||p_parameter_name||' into v_parameter_value from dual; end;' ;
EXECUTE IMMEDIATE v_sql_action into v_parameter_value;
return v_parameter_value;
exception when others then
return null;
end get_report_parameter;
(...)
end ng_session_pkg;
/
Schema B has no access to Schema A (and there are no synonyms - therefore the dynamic pl/sql) but using SYS_CONTEXT ('USERENV','SESSION_USER'); returns schema A and with that i can access functions in schema A with pl/sql, but i would like to access a global variable.
The above gives error when executing :
ORA-01007: variable not in select list
- ORA-06512: at "SCHEMAB.NG_SESSION_PKG", line 19
The ideia is to have a function in schema B that given the global variable name returns the result.
How can i get the variable value in above pl/sql code please.
Thanks
Carlos