Oracle - SQL to determine MAX of table in another (varible) schema

We have multiple schemas for users and a MAIN schema. Each schema has the same named tables.

We would like to place all PL/SQL code in pacakges in the MAIN schema and make the routines as generic as possible - just pass the schema to the MAIN procedure within the MAIN package and have it work on the table in the passed schema.

Inside this specific Procedure, we have declared a Variable, tmpPriority.
We also have declared and populated another Variable tmpHIER_ID
Each Schema has a Table called HX_HEIRACHY which, for this puprose has two fields, a PRIORITY field and a HIER_ID field. Each HIER_ID can have many PRIORITies ( 1 to x)

We wish to pass the schema into this procedure and select the MAX(HX_HEIRACHY.PRIORITY) into the variable tmpPiority for a given HIER_ID

We thought we would use an EXECUTE IMMEDIATE, but I'm not married to that idea - I'm open to other solutions (except placing the procedure in each Schema).

To that end, we have the following statement:
EXECUTE IMMEDIATE 'SELECT MAX(PRIORITY) INTO '||tmpPRIORITY||' FROM '||tmpSCHEMA||'.HX_HIERARCHY WHERE HIER_ID = '||tmpHIER_ID;

This errors.
Anyone have an idea on a way around this ?
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
declare your package to be authid current_user

then inside the procedure change the active schema

execute immediate 'alter session set current_schema='||tmpSCHEMA;

all objects will be referenced as if they were prefixed with the appropriate schema
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GNOVAKAuthor Commented:
And if, in the same procedure, I wish to access a table in the MAIN schema, I can just use the "MAIN.<some table>"  ?
0
sdstuberCommented:
something like this...


SQL> create or replace procedure test_schemas(tmpSCHEMA in varchar2) authid current_user
  2  is
  3      v_old_schema varchar2(30) := sys_context('userenv','current_schema');
  4  begin
  5      execute immediate 'alter session set current_schema='||tmpSCHEMA;
  6
  7      for x in (select * from test_table) loop
  8          dbms_output.put_line(x.n);
  9      end loop;
 10
 11       execute immediate 'alter session set current_schema='||v_old_schema;
 12  end;
 13  /

Procedure created.

SQL> create table test1.test_table as select 1 n from dual connect by level <= 10;

Table created.

SQL> create table test2.test_table as select 2 n from dual connect by level <= 10;

Table created.

SQL> create table test3.test_table as select 3 n from dual connect by level <= 10;

Table created.

SQL> set serveroutput on
SQL> exec test_schemas('TEST1')
1
1
1
1
1
1
1
1
1
1

PL/SQL procedure successfully completed.

SQL> exec test_schemas('TEST2')
2
2
2
2
2
2
2
2
2
2

PL/SQL procedure successfully completed.

SQL> exec test_schemas('TEST3')
3
3
3
3
3
3
3
3
3
3

PL/SQL procedure successfully completed.

Open in new window



I made the assumption that "test_table" existed in the main schema, if not, you'll need to create one, or a dummy view to give the procedure a base structure to compile against.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
you could simply pass in MAIN as the schema to your procedure
0
GNOVAKAuthor Commented:
PS - you wouldnt happen to know how to declare the package to be authid current_user using TOAD perchance?
0
sdstuberCommented:
extract the code to editor,  add "authid current_user" and then hit F9 to recompile it
0
GNOVAKAuthor Commented:
I tried the following:
 PROCEDURE MyProcedure(tmpSCHEMA IN VARCHAR2) IS
   v_old_schema varchar2(30) := sys_context('userenv','current_schema');
   begin
        execute immediate 'alter session set current_schema='||tmpSCHEMA;
 
        for x in (select * from hx_hierarchy) loop
            dbms_output.put_line(x.PRIORITY);
        end loop;
 
        execute immediate 'alter session set current_schema='||v_old_schema;
   end;

I've never used the for x in (select *...) before, and it seems to be choking on X
0
sdstuberCommented:
does the error occur while compiling or while executing?

what is the error?
0
GNOVAKAuthor Commented:
Found it - my own SOFM - Stupidity Of The Moment
It works great and I get it now..
Thanks so much!!!!
0
GNOVAKAuthor Commented:
Super help - Thanks!
0
sdstuberCommented:
also note, declaring the package authid current_user will affect the other routines in that package.

You may want to pull that one procedure out into a standalone procedure, or put it into a different package where you'd want everything to be authid current_user
0
GNOVAKAuthor Commented:
yes - we have a package called something like SCHEMA_ROUTINES

BTW, if multiple users utilize that procedure, Oracle keeps track and allows it to manipulate each user's schema, correct?

in other words, no caveats with multiple users..?
0
sdstuberCommented:
if you mean multiple users simultaneously using it, that's fine. the alter session and authid are for each users's session.

So they won't interfere with each other.

This would apply even if the same user logged in more than once, that user would have distinct sessions and package state would NOT be shared between them.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.