Hi,
I need to execute a stored procedure that exists in many schema, on demand, in any given schema. I am trying to change the current_schema then execute the procedure (GS_LOAD_FDB_PKG is the package in each of our schemas and gst_disease_dxid_update_sdlc is the sproc). The variable "new_schema" is just used to demonstrate that the context did indeed change.
declare
user_schema varchar2(32);
new_schema varchar2(32);
begin
user_schema := 'masterv71';
execute immediate 'alter session set current_schema='||user_schema;
select sys_context('userenv', 'current_schema') into new_schema from dual;
dbms_output.put_line(new_schema);
GS_LOAD_FDB_PKG.gst_disease_dxid_update_sdlc;
end;
but it results is this error:
ORA-06550: line 12, column 5:
PLS-00201: identifier 'GS_LOAD_FDB_PKG.GST_DISEASE_DXID_UPDATE_SDLC' must be declared
ORA-06550: line 12, column 5:
PL/SQL: Statement ignored
If I hard-code the schema like this:
masterv71.GS_LOAD_FDB_PKG.gst_disease_dxid_update_sdlc;
then it works, but that defeats the purpose.
How can I accomplish this?
Thanx, D
You might want to check out definers versus invokers rights procedures for a possibly simpler method here.
But for your question - we need to be able to *compile* the routine before we run it, so we are checking that "GS_LOAD_FDB_PKG.gst_disease_dxid_update_sdlc" is resolvable BEFORE we even run the anonymous block.
To get around that, the call itself also has to be dynamic:
execute immediate 'begin GS_LOAD_FDB_PKG.gst_disease_dxid_update_sdlc; end;'
but I think an invokers rights faciity might be what you are really after here.