I have created the following package and although it compiles without issue and runs I do not get the expected results. I have one trigger in my test schema (ABS) but it will not disable it. In fact I do not get the ALTER TRIGGER statement that I try to output. I do get the "select * from all triggers that I have output in my dbms_output.put_line(sql_stmt) statement. Thus I believe it is not going into my cursor loop. Once I get in my loop I will decide whether I need to run EXECUTE IMMEDIATE or dbms_utility. Any suggestions as how I can get this to work?
CREATE or REPLACE PACKAGE PKG_MIGRATE AS
PROCEDURE DISABLE_OBJ(SCHEMA_IN IN VARCHAR2);
PROCEDURE DISABLE_TRIG(p_schema_in IN VARCHAR2);
END PKG_MIGRATE;
CREATE or REPLACE PACKAGE BODY PKG_MIGRATE AS
PROCEDURE DISABLE_OBJ(SCHEMA_IN IN VARCHAR2)
IS
BEGIN
DISABLE_TRIG(SCHEMA_IN);
END DISABLE_TRIG;
PROCEDURE DISABLE_TRIG(p_schema_in IN VARCHAR2)
IS
TYPE CurTyp IS REF CURSOR;
cv CurTyp;
rec all_triggers%rowtype;
sql_stmt VERCHAR2(4000);
BEGIN
sql_stmt:='select * from all_triggers where owner = ''' || p_schema_in || '''';
dbms_output.put_line(sql_stmt);
OPEN cv for sql_stmt;
LOOP
FETCH cv into rec;
EXIT WHEN cv%NOTFOUND;
dbms_output.put_line ('ALTER TRIGGER ' || rec.owner || '.' ||rec.trigger_name || ' DISABLE');
-- dbms_utility.exec_ddl_statement('ALTER TRIGGER ' || rec.owner || '.' || rec.trigger_name || 'DISABLE');
-- EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.owner || '.' || rec.trigger_name || ' DISABLE';
END LOOP;
END DISABLE_TRIG;
END PKG_MIGRATE;
When I run it
set serveroutput on;
BEGIN
pkg_migrate.disable_obj('ABS');
END;
The results are:
anonymous block completed
select * from all_triggers where owner = 'ABS'
Thank you for any assistance.