7 thoughts on “How to Get Referential Constraints Using DBMS_METADATA

  1. Dean H.

    Thanks for the informative post! The technique of separating the referential integrity constraints from the table DDL was very helpful.

    Reply
  2. Adam Martin

    This was helpful, but I’ve got a question.

    I’m spooling this output to a file, and I seem to get 2 of every ‘R’ constraint.
    Here is my script, hopefully you can help me figure out what I’m doing wrong.

    set long 200000 pages 0 lines 10000
    set head off
    set echo off
    set pagesize 0
    set verify off
    set feedback off
    set linesize 32767
    set trimspool on
    COLUMN DDL Format a2000

    spool schema_ref_constraints.sql
    begin
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’PRETTY’,true);
    dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,true);
    end;
    /
    SELECT dbms_metadata.get_dependent_ddl(‘REF_CONSTRAINT’, table_name) DDL
    FROM user_tables t
    WHERE EXISTS (SELECT 1
    FROM user_constraints
    WHERE table_name = t.table_name
    AND constraint_type = ‘R’);
    /
    begin
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, ‘DEFAULT’);
    end;
    /
    SPOOL OFF

    Reply
    1. Przemysław Kruglej Post author

      The slash after the SELECT is the reason – it tells SQL*Plus to re-execute the last command which, in your case, is the SELECT statement, and you end up with the referential constraints being spooled twice.

      Reply

Leave a Reply

Your email address will not be published.