DBMS_METADATA Excludes Semi-Colon With Multiple Constraints

(Doc ID 1436887.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

There are actually 2 issues to address.
The first is that the semi-colon is missing when multiple constraints are returned.
The second is that keywords will have a carriage return in the middle.
Both issues generate syntax errors if the commands produced were to be executed in a SQL script.

This example will illustrate both issues.

Created a new table with 3 foreign key constraints to the EMP table in the SCOTT schema.

CREATE TABLE NEW_TABLE (
   EMPNO NUMBER(4) CONSTRAINT FK_EMPNO_2 REFERENCES EMP,
   DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO_2 REFERENCES EMP,
   MGR NUMBER(4) CONSTRAINT FK_MGR_2 REFERENCES EMP);


Run the following code within the same SQL*Plus session.

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
set feedback off
set termout off
set echo off
set linesize 300
SET LONG 500
col output format 300
SET PAGESIZE 0
spool /tmp/FK_syntax.sql
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT',c.table_name,'SCOTT') ||';' output
FROM user_tables c
where table_name in
(select distinct table_name from user_constraints
where owner = 'SCOTT' and constraint_type = 'R')
order by table_name;
spool off
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');


The following the output produced. Although the semi-colon issue should be the same, the words broken could vary. (Comments have been added)

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;


ALTER TABLE "SCOTT"."NEW_TABLE" ADD CONSTRAINT "FK_DEPTNO_2" FOREIGN KEY ("DEP
TNO")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE  <--Missing semi-colon

ALTER TABLE "SCOTT"."NEW_TABLE" ADD CONSTRAINT "FK_EMPNO_2" FOREIGN KEY ("EMPN
O")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE  <--Missing semi-colon

ALTER TABLE "SCOTT"."NEW_TABLE" ADD CONSTRAINT "FK_MGR_2" FOREIGN KEY ("MGR")
REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms