Monday, May 26, 2014

How I Copied Triggers from one Database to Another

(This happened on Oracle 11.2.) In an earlier example, I started to look at doing a full expdp and impdp. I'm not sure why, but the Data Pump import produced a number of errors. There were well over 200 like this: 

ORA-39112: Dependent object type TRIGGER:"SRCE"."ADDRESS_BIU" skipped, base object type TABLE:"SRCE"."ADDRESS" creation failed 

This message suggested that the SRCE.ADDRESS table had not been created in the output database but, in fact, it had: 

SQL> desc srce.address
Name                       Null?    Type
-------------------------- -------- ------------------
UPDATE_COUNT               NOT NULL NUMBER
ADDRESS_ID                 NOT NULL NUMBER
SUB_PREMISES                        VARCHAR2(30)
PREMISES_NAME                       VARCHAR2(30)
PO_BOX                              VARCHAR2(30)
PREMISES_NUMBER                     VARCHAR2(10)
DEP_THOROUGHFARE                    VARCHAR2(30)
THOROUGHFARE                        VARCHAR2(30)
DOUB_DEP_LOCALITY                   VARCHAR2(30)
DEP_LOCALITY                        VARCHAR2(30)
POST_TOWN                           VARCHAR2(30)
COUNTY                              VARCHAR2(30)
POSTCODE                            VARCHAR2(8)
USES                                NUMBER
LAST_UPD_BY                         VARCHAR2(8)
LAST_UPD_TIME                       DATE
COUNTRY                             VARCHAR2(30)
BUILDING_GROUP_NAME                 VARCHAR2(30)
TCN                                 NUMBER 

However, none of the triggers had been created so my output database was over 200 triggers short, which, as you might expect, stopped it working properly.
 
I tried to extract DDL from the input database to create the triggers as follows but this failed too:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -
> (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
 
PL/SQL procedure successfully completed.
 
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM -
> (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_metadata.get_ddl
  2  ('TRIGGER','ADDRESS_BIU','SRCE') from dual
  3  /
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 13899
Session ID: 95 Serial number: 10069
 
no rows selected
 
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database
  2  /
 
OPEN_MODE
--------------------
READ WRITE
 
1 row selected.
 
SQL>
 
I wrote the following script and ran it against the input database:
 
col trigger_body newline
col slash newline
set feedback off
set pages 0
set lines 200
set long 100000
set longchunksize 200
set trimspool on
spool create_triggers.sql
select 'create or replace trigger '||
description,
trigger_body,
'/' slash
from dba_triggers
where owner = 'SRCE'
/
spool off
 
This is part of the SPOOL file:
 
create or replace trigger srce.ptr_product_pk_bi
  BEFORE INSERT ON ptr_product
  FOR EACH ROW
DECLARE
  -- local variables here
BEGIN
  IF :new.product_id IS NULL THEN
    SELECT ptr_product_id_seq.nextval
    INTO   :new.product_id
    FROM   DUAL;
  END IF;
END ptr_product;
/
 
… and when I ran it against the output database, it worked like this:
 
SQL> l
  1  create or replace trigger srce.ptr_product_pk_bi
  2    BEFORE INSERT ON ptr_product
  3    FOR EACH ROW
  4  DECLARE
  5    -- local variables here
  6  BEGIN
  7    IF :new.product_id IS NULL THEN
  8      SELECT ptr_product_id_seq.nextval
  9      INTO   :new.product_id
10      FROM   DUAL;
11    END IF;
12* END ptr_product;
SQL> /
 
Trigger created.
 
SQL>

The SQL in the SPOOL file created the other 200+ missing triggers in the same way. When I get a moment, I will try to understand what caused the problems I had with impdp and dbms_metadata.get_ddl.

The book advertised below is apparently free if you have a connected Android device:


1 comment:

t said...

This is great, Just what I was looking for. Mucho Gracias,

-t