Oracle Database Discussions

We are very pleased with DMBS_METADATA for punching DDLs in general, We use the following to create executable scripts for recreating any object in our databases.

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE', 'OBJECT_NAME', 'SAINADM' ) from dual;

In most types of object, the DDL produced can be executed without errors, providing that the original target object was well founded. However, we have found that in the case of a triggers, the DDL produced does not function for the following reason:

EXAMPLE
-- This set of instructions is produced by a metascript too complicated to show here

SPOOL TRIGGER_NAME.trg
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'TRIGGER_NAME', 'SCHEMA_NAME' ) txt
FROM DUAL;
SPOOL OFF

END OF EXAMPLE
-

This will produce the following output, spooled to the file TRIGGER_NAME.trg

OUTPUT
-- we have anonymised our object names
-- the syntax is what I would like you to focus on

CREATE OR REPLACE TRIGGER "SCHEMA_NAME"."TRIGGER_NAME"
BEFORE INSERT on SCHEMA_NAME.TABLE_NAME FOR EACH ROW

BEGIN
select SCHEMA_NAME.SEQUENCE_NAME.nextval
into :new.colname
from dual;
END;

ALTER TRIGGER SCHEMA_NAME"."TRIGGER_NAME" ENABLE

_END OF OUTPUT_

Note what has happened.
1. The trigger DDL has been produced
2. So has a the ALTER TRIGGER ... ENABLE
3. BUT => between the trigger DDL and the alter trigger statement the is no slash (of course, because naturally DBMS_METADATA does not produce such characters). BUT this is problematic, because the combination of the Create trigger statement and the alter trigger statement, without a slash in between means that the spool file is atomically illegal. Because in real life we need a slash between the two staements.

Why is this a problem for us?
- Because we are about to introduce the automation of object changes between our "Development" "Integration" "Quality Assurance" and "Production" databases based on the execution of files produced by DBMS_METADATA.GET_DDL. For every other type of object, the file produced is well founded and executable DDL that will create the object. But in the case of triggers, the existence of the alter trigger statement renders the foregoing create trigger statement unexecutable.

The use of DMBS_METADATA is more or less vanilla. That is to say, there appears to be no scope for instructing DBMS_METADATA to abstain form including the alter trigger statement

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'TRIGGER_NAME', 'SCHEMA_NAME' ) txt

Here are my questions
1. How can we punch the DDL for triggers without bringing the alter trigger statement
2. Alternatively, how can we automate the insertion of the slash character in between the CREATE TRIGGER and the ALTER TRIGGER in the original metascript that creates the example spool file.


Thanks for your attention. Every response will be welcomed.

Edited by: user10248070 on Mar 2, 2010 3:54 AM