RE: DBMS_METADATA to extract triggers

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: 2006-01-03 21:59:35
Message-id: 7ED53A68952D3B4C9540B4EFA5C76E36014326B1@CWYMSX04.Corp.Acxiom.net

Don,

Do each of your defined trigger's DDLs end with a forward slash? That's what's needed to create a trigger, since it's a PL/SQL block.

Do you have a sample or 2 of triggers that didn't get created?

What Oracle version?

It's been working fine for me, although my setup is a bit different. I extract each object into a separate file via dbms_metadata. Here's the settings I use:

SET ECHO off FEEDBACK off HEADING off LINESIZE 2047 LONGCHUNKSIZE 2047 SET LONG 200000 PAGESIZE 0 SCAN off
SET SQLBLANKLINES on TAB off TRIMSPOOL on COLUMN stmt WORD_WRAPPED
ALTER SESSION SET CURSOR_SHARING=FORCE;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Don Morse
> Sent: Friday, December 30, 2005 6:32 PM
> To: oracle-l_at_freelists.org
> Subject: DBMS_METADATA to extract triggers
>
> List,
>
> Has anyone used this utility to extract triggers from
> a schema, then run the generated DDL script in another
> database? I have extracted 76 triggers from the source
> database, about 4,700 lines of code. When I run the
> generated DDL into the destination database, only 2
> triggers are cataloged. The generated file contains
> all the required semicolons (except for the alter
> trigger enable lines, which I had to insert manually).
> I have these SQL*Plus commands at the start of the
> dmbs_metadata script:
> set long 99999
> set lines 120 space 0 heading off feedb off
>
> Did I miss something?
>
> Thanks!!
>
> Don
>
>
>
>
> __________________________________
> Yahoo! for Good - Make a difference this year.
> http://brand.yahoo.com/cybergivingweek2005/
> --
> http://www.freelists.org/webpage/oracle-l

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 03 2006 - 21:59:35 CST