Hi Tom,

Thanks for this great site!

I wrote a stored procedure which dynamically disables constraints:


CREATE OR REPLACE PROCEDURE COMMON.PRC_DSBL_PART_CNSTR_TEST("PAYOR_IN" IN VARCHAR2) IS
DSBLCTR_sql1 VARCHAR2(100) := 0 ;
SCHEMA_OWNER VARCHAR2(10);

BEGIN
SELECT USER INTO SCHEMA_OWNER FROM DUAL ;


DSBLCTR_sql1 := 'ALTER Table ' || SCHEMA_OWNER || '.' || PAYOR_IN || '_CLAIM_CURPART DISABLE CONSTRAINT ' || PAYOR_IN || '_CLAIM_CURPART_PK' ;

dbms_output.put_line('SCHEMA '||SCHEMA_OWNER);
dbms_output.put_line('DSBL1 '||DSBLCTR_sql1);


EXECUTE IMMEDIATE DSBLCTR_sql1 ;

END PRC_DSBL_PART_CNSTR_TEST ;

This procedure is designed to be used by multiple schemas that have similar table structures. The user provides the payer and everything else is automatic for the schema.

When I run this procedure in a different schema I get the following
error:

SQL> show user;
USER is "BCBSTGT1"
SQL> execute common.prc_dsbl_part_cnstr_test('BCBS');
BEGIN common.prc_dsbl_part_cnstr_test('BCBS'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "COMMON.PRC_DSBL_PART_CNSTR_TEST", line 24
ORA-06512: at line 1

However, when I run the command directly I do not get the error:
SQL> SHOW USER;
USER is "COMMON"
SQL> ALTER TABLE BCBSTGT1.BCBS_CLAIM_CURPART DISABLE CONSTRAINT BCBS_CLAIM_CURPA
RT_PK ;

Table altered.

Any idea why this is happening? Thanks in advance.