CREATE OR REPLACE PACKAGE BODY ddl_extract_pkg is

    FUNCTION scrub_raw_ddl (p_status_c IN varchar2,
                            p_cons_type_c IN varchar2,
                            p_ddl_c IN varchar2)
    return varchar2
    is
        v_new_ddl_c varchar2(1000);
    BEGIN
        v_new_ddl_c := p_ddl_c;
        v_new_ddl_c := replace(v_new_ddl_c, chr(10), null);
        v_new_ddl_c := trim(v_new_ddl_c);
        if ( p_cons_type_c in ('C', 'R') ) then
            if ( ( p_status_c = 'ENABLED' ) ) then
                if ( instr(v_new_ddl_c, ' NOVALIDATE') = 0 ) then
                    v_new_ddl_c := v_new_ddl_c||' NOVALIDATE';
                end if;
            end if;
        end if;
        v_new_ddl_c := v_new_ddl_c||';';

        return(v_new_ddl_c);
    END scrub_raw_ddl;

    FUNCTION get_constraint_ddl return sg_constraint_ddl_array pipelined is
        v_mdc_i integer;
        v_raw_sql_c varchar2(1000);
        v_out_record sg_constraint_ddl_rec;

        cursor v_extract_order_cur is
            select 1 as a_cons_order,
            'C' as a_cons_type,
            'CONSTRAINT' as a_cons_group
            from dual
            union all
            select 2, 'P', 'CONSTRAINT'
            from dual
            union all
            select 3, 'U', 'CONSTRAINT'
            from dual
            union all
            select 4, 'R', 'REF_CONSTRAINT'
            from dual
            order by 1;

        cursor v_constraints_cur (p_type_c IN varchar2) is
            select owner, table_name,
            constraint_name, constraint_type,
            status, validated
            from user_constraints
            where table_name = 'SEND_MAT_PLAN_M'
            and constraint_type = p_type_c
            and generated <> 'GENERATED NAME';
    BEGIN
        --Configure the dbms_metadata package.
        v_mdc_i := dbms_metadata.session_transform;
        dbms_metadata.set_transform_param(v_mdc_i, 'PRETTY', false);
        dbms_metadata.set_transform_param(v_mdc_i, 'SEGMENT_ATTRIBUTES', false);
        dbms_metadata.set_transform_param(v_mdc_i, 'STORAGE', false);
        dbms_metadata.set_transform_param(v_mdc_i, 'TABLESPACE', false);
        dbms_metadata.set_transform_param(v_mdc_i, 'CONSTRAINTS_AS_ALTER', true);
        dbms_metadata.set_transform_param(v_mdc_i, 'CONSTRAINTS', true);
        dbms_metadata.set_transform_param(v_mdc_i, 'REF_CONSTRAINTS', true);
        dbms_metadata.set_transform_param(v_mdc_i, 'SQLTERMINATOR', false);
        --Open the cursor that controls the extraction order...
        for extract_order_rec in v_extract_order_cur loop
        --Open the cursor to access the constraints of the
        --current type (PK, UK, etc).
            for constraints_rec in v_constraints_cur(extract_order_rec.a_cons_type) loop
                --Initialize the next pipeline record to be returned.
                v_out_record.ddl_name := constraints_rec.constraint_name;
                v_out_record.ddl_text := null;
                --Get the raw DDL for the current constraint.
                v_raw_sql_c := dbms_metadata.get_ddl(extract_order_rec.a_cons_group,
                constraints_rec.constraint_name,
                constraints_rec.owner);
                --Scrub the raw DDL.
                --The cleaned DDL will be placed into the record
                --being returned to the pipeline.
                v_out_record.ddl_text := scrub_raw_ddl(constraints_rec.status,
                extract_order_rec.a_cons_type,
                v_raw_sql_c);
                --Return the constructed command to the pipeline.
                pipe row(v_out_record);
            end loop;
        end loop;
        return;
    END get_constraint_ddl;
END ddl_extract_pkg;

select x.ddl_name,
x.ddl_text
from table(ddl_extract_pkg.get_constraint_ddl) x
order by 1;