My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.