Skip to Main Content
  • Questions
  • executing stored procedures after context change

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: July 22, 2016 - 5:20 pm UTC

Last updated: July 24, 2016 - 9:02 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

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

and Connor said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, July 22, 2016 - 9:07 pm UTC

Excellent! That's really helpful.
Connor McDonald
July 24, 2016 - 9:02 pm UTC

Glad we could help

Don Simpson, July 25, 2016 - 7:11 pm UTC

That's really helpful, thanx!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library