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;
Anyone have an idea on a way around this ?