Example of modifying triggers to use new schemas in code

triggerchange.sql

set linesize 10000
set pagesize 0
set long 2000000000
set longchunksize 1000000
set echo on
set termout on
set serveroutput on size 1000000
set trimspool on

spool test.log

set define off

drop table test1.tab1;
drop table test1.tab2;

create table test1.tab1
(
   col1 number, 
   col2 number
); 

create table test1.tab2
(
   col1 number, 
   col2 number
); 

create or replace trigger test1.trg1 
before insert or update on test1.tab1
for each row
begin 
  :new.col2 := :new.col1*2; 
end; 
/

create or replace trigger test1.trg2 
before insert or update on test1.tab2
for each row
begin 
  :new.col2 := :new.col1*2; 
end; 
/ 

drop table clobout;

create table clobout (doc clob);

declare
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('TRIGGER');

-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','TEST1');

-- Request that the schema name be modified.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','TEST1','TEST2');

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true);

-- Fetch the triggers.

LOOP
  doc := DBMS_METADATA.FETCH_CLOB(h);
  EXIT WHEN (doc is null);
  insert into clobout values (doc);
  commit;
END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/ 

-- update schema name in triggers

update clobout set doc=replace(doc,'test1.','test2.');

commit;

select doc from clobout;

spool off