Hi everyone, I have a situation where I would like to dynamically disable a
FK constraint. The constraint name is unknown but i can retrieve it with
this statement:
select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME IN(
'TABLE_A', 'TABLE_B') and r_constraint_name in( SELECT constraint_name FROM
USER_CONSTRAINTS WHERE TABLE_NAME IN( 'TABLE_A', 'TABLE_B'));

I would like to create a procedure that will disable the result of this
query.
I would generally run this in sqlplus and spool the output to read:
ALTER TABLE TABLE_B DISABLE CONSTRAINT SYS_C00145750;
Then run the spool.
But I would like to create a procedure to do this with 3 arguements being
TABLE_A, TABLE_B and ENABLE or DISABLE.
This ia how far I got before running into a brick wall:

CREATE OR REPLACE PROCEDURE dyn_plsql (my_table1 IN VARCHAR2,my_table2 IN
varchar2, my_action IN VARCHAR2)
IS
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
const varchar2(200);
my_str varchar2(4000);
CURSOR my_constraint IS
select constraint_name FROM USER_CONSTRAINTS WHERE TABLE_NAME IN(
my_table1, my_table2) and r_constraint_name in( SELECT constraint_name FROM
USER_CONSTRAINTS WHERE TABLE_NAME IN( my_table1, my_table2));

BEGIN
open my_constraint;
fetch my_constraint into const;
my_str := 'ALTER TABLE '||my_table2 ||' '||my_action||' constraint '
||const|| ';';
DBMS_SQL.PARSE (cur, 'BEGIN '||my_str||' END;' ,DBMS_SQL.NATIVE);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in ' || my_str);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
close my_constraint;
END;
/

Any help appreciated. Thanks

Mike

Oracle LazyDBA home page