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.2Information 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. |