The DBMS_METADATA package is very cool. I remember the days of either hand-crafting DDL statements based on queries to the data dictionary, or many a DBA will be familiar with running “imp show=y” or “imp indexfile=…” in order to then laboriously extract the DDL required from the import log file. DBMS_METADATA removed all of those annoyances to give us a simple API to get the true and complete DDL for a database object.
But when extracting DDL from the database using the DBMS_METADATA package, you need to be aware of some subtleties especially if you plan on executing that DDL in the database.
Consider this example – I have a few tables and I want to extract the referential integrity constraints for one of them. Being a good cautious developer 
I’ll just output the DDL first before attempt to do any execution of the statements:
SQL> create table tab1(id number, name varchar2(100),
2 constraint pk_tab1_id primary key(id));
Table created.
SQL> create table tab2(id number, name varchar2(100),
2 constraint pk_tab2_id primary key(id));
Table created.
SQL> create table tab3(id number, name varchar2(100), int_id number,
2 constraint pk_tab3_id primary key(id),
3 constraint fk_tab1_id foreign key(int_id) references tab1(id),
4 constraint fk_tab2_id foreign key(int_id) references tab2(id));
Table created.
SQL>
SQL> set serverout on
SQL> begin
2 for i in (
3 select t.table_name,
4 dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name) ddl
5 from user_tables t
6 where table_name = 'TAB3'
7 )
8 loop
9 dbms_output.put_line(i.ddl);
10 --execute immediate i.ddl;
11 end loop;
12 end;
13 /
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.
That all looks fine – I have my two ALTER statements ready to go. So now I’ll comment back in the ‘execute immediate’ command and all should be fine.
SQL> set serverout on
SQL> begin
2 for i in (
3 select t.table_name,
4 dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name) ddl
5 from user_tables t
6 where table_name = 'TAB3'
7 )
8 loop
9 dbms_output.put_line(i.ddl);
10 execute immediate i.ddl;
11 end loop;
12 end;
13 /
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
begin
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 10
ORA-06512: at line 10
The seems an odd result. Since in this simple example I’m just running the commands straight back into the same database, I might have expected a “Constraint already exists”, or “Object name exists” style of error, but this is different. This error is telling that the statement is invalid – which obviously should not be the case if it came straight out of DBMS_METADATA. But a simple amendment to my anonymous block will reveal the answer. I will output a line of dashes each time I cycle through the cursor loop
SQL> set serverout on
SQL> begin
2 for i in (
3 select t.table_name,
4 dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name) ddl
5 from user_tables t
6 where table_name = 'TAB3'
7 )
8 loop
9 dbms_output.put_line('========================================');
10 dbms_output.put_line(i.ddl);
11 --execute immediate i.ddl;
12 end loop;
13 end;
14 /
========================================
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.
And therein lies the issue. I got two ALTER commands back on screen, but in reality they both came back from a single row fetched from the cursor. The ALTER commands were separated by a carriage return, but if I try to execute that single row, then the statement is invalid because it is an attempt to run two ALTER commands in a single statement. The output looks like two rows from the cursor but it was not.
That is a problem if I want to store that DDL in a table or a file, because I can’t use it as it currently stands, and I don’t want to have to write some scripts to parse that DDL to add semi-colons or split it into multiple commands, because one of the motivations for DBMS_METADATA in the first place was to avoid all that irritation.
There is an easy fix to this. Rather than getting the dependent DDL for a table, we can get the “direct” DDL for the constraints themselves. In this way, you’ll get a row from the cursor for each constraint, and hence one DDL statement per constraint as well.
SQL> set serverout on
SQL> begin
2 for i in (
3 select t.table_name,
4 dbms_metadata.get_ddl('REF_CONSTRAINT', constraint_name) ddl
5 from user_constraints t
6 where table_name = 'TAB3'
7 and constraint_type = 'R'
8 )
9 loop
10 dbms_output.put_line('========================================');
11 dbms_output.put_line(i.ddl);
12 --execute immediate i.ddl;
13 end loop;
14 end;
15 /
========================================
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB1" ("ID") ENABLE
========================================
ALTER TABLE "MCDONAC"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY ("INT_ID") REFERENCES "MCDONAC"."TAB2" ("ID") ENABLE
PL/SQL procedure successfully completed.
