SQL & PL/SQL

Hello ,

There are 2 schemas in our DB

1. Common schema - say xx_com
2. Custom Schema - say xx_cust - with a table: xx_cust_tbl

A procedure/package is created in ‘xx_com’, to insert into a table ‘xx_cust_tbl’ in ‘xx_cust’ schema.

Synonym for xx_com.procedure is created in xx_cust schema.

Now if I call the procedure from xx_cust as beow, it works.

begin
xxfsm_test_schema_100('test','test11','test11');
end;

But the problem is we want to call this from common schema 'xx_com' without prefixing the xx_cust to the proecdure name.(no changes can be made to the existing code)

I tried giving invoker rights to the procedure (authid current_user) and included alter session set current_schema ='xx_Cust'; before the call to xxfsm_test_schema_100()

All statements are dynamic as well. Still INSERT into xx_Cust_tbl fails and immediately the control goes back to common schema and hence even the exception handling within the procedure doesn't work.

Is there way we can do this without prefixing 'xx_cust' to the custom table ‘xx_cust_tbl’ and calling the procedure from xx_Common itself?

CREATE OR REPLACE PACKAGE xxfsm_test_schema AUTHID current_user AS
PROCEDURE xxfsm_test_schema_100 (
p_schema VARCHAR2,
val1 VARCHAR2,
val2 VARCHAR2
);

END xxfsm_test_schema;

CREATE OR REPLACE PACKAGE BODY xxfsm_test_schema AS

PROCEDURE xxfsm_test_schema_100 (
p_schema VARCHAR2,
val1 VARCHAR2,
val2 VARCHAR2
) AS
v_bob VARCHAR2(20) := NULL;
xx_sql VARCHAR2(2000) := NULL;
BEGIN

       EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT\_SCHEMA = ' || p\_schema;--XXCUST1';  

       EXECUTE IMMEDIATE 'INSERT INTO xxcust\_test\_tbl100(col1,col2) VALUES('  
                         || ''''  
                         || val1  
                         || ''''  
                         || ','  
                         || ''''  
                         || val2  
                         || ''''  
                         || ')';

       COMMIT;  
   EXCEPTION  
       WHEN OTHERS THEN  
           SELECT  
               substr(sys.standard.sqlerrm, 1, 1999)  
           INTO xx\_sql  
           FROM  
               dual;

           INSERT INTO debug\_table ( col1 ) VALUES ( xx\_sql );

           COMMIT;  
   END;

END xxfsm_test_schema;

begin
xxfsm_test_schema.xxfsm_test_schema_100('xxcust1','table_test','table_test');
end;

Gives error ORA-06512: at "TFSM.XXFSM_TEST_SCHEMA", line 26
ORA-00942: table or view does not exist

Thanks ,

Rima