Home » SQL & PL/SQL » SQL & PL/SQL » DDL generation (Oracle Database 19.10 Linux x86_64)
DDL generation [message #687894] Sat, 15 July 2023 05:33 Go to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
I want to generate DDL of all constraints in the schemas but I do not want text like using and enable.
Please help me

Report message to a moderator

Re: DDL generation [message #687896 is a reply to message #687894] Sat, 15 July 2023 10:00 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
smunir362 wrote on Sat, 15 July 2023 13:33
I want to generate DDL of all constraints in the schemas but I do not want text like using and enable.
Please help me
Try


SET LINES 900 PAGES 20000
SET LONG 40000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
select dbms_metadata.get_ddl ( 'CONSTRAINT',CONSTRAINT_NAME,OWNER) FROM DBA_CONSTRAINTS WHERE OWNER='YOUR_OWNER';
To filter out the ENABLE/DISABLE you can use REPLACE, for example.

If you just want the names and the references, you can query DBA_CONSTRAINTS, columns OWNER,R_OWNER,TABLE_NAME,OWNER...

Regards,
Andrey

[Updated on: Sat, 15 July 2023 10:14]

Report message to a moderator

Re: DDL generation [message #687897 is a reply to message #687894] Sat, 15 July 2023 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want to use DBMS_METADATA there is no ways to avoid USING and ENABLE/DISABLE.
The best you can have is:
SQL> begin
  2    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY',             true);
  3    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',      true);
  4    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
  5    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_CREATION',   false);
  6    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',            false);
  7    DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE',         false);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select dbms_metadata.get_ddl
  2           (decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER)
  3  FROM DBA_CONSTRAINTS
  4  WHERE OWNER = 'SCOTT' and TABLE_NAME in ('EMP','DEPT')
  5  /
DBMS_METADATA.GET_DDL(DECODE(CONSTRAINT_TYPE,'R','REF_CONSTRAINT','CONSTRAINT'),CONSTRAINT_NAME,OWNER)
----------------------------------------------------------------------------------------------------------

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE;

  ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE;

3 rows selected.
But now if you just want the core part you can use, as Andrey said, the columns in DBA_CONSTRAINTS and DBA_CONS_COLUMNS, something like (simplified example, I assumed referencing and referenced tables in the same schema and restricted the query to 3 constraint types P, U and R, I let you do the same thing for the other ones):
SQL> set recsep each recsepchar ' '
SQL> with
  2    cols as (
  3      select table_name, constraint_name,
  4             listagg('"'||column_name||'"',',') within group (order by position) cols
  5      from dba_cons_columns
  6      where owner = 'SCOTT' and table_name in ('EMP','DEPT')
  7      group by table_name, constraint_name
  8    )
  9  select 'ALTER TABLE "'||c.owner||'"."'||c.table_name||'" ADD CONSTRAINT "'||c.constraint_name||'" '||
 10         decode(c.constraint_type,
 11                'P', 'PRIMARY KEY ('||cc.cols||')',
 12                'U', 'UNIQUE  ('||cc.cols||')',
 13                'R', 'FOREIGN KEY ('||cc.cols||')
 14     REFERENCES "'||c.owner||'"."'||cc2.table_name||'" ('||cc2.cols||')')||';'
 15          res
 16  from dba_constraints c, cols cc, cols cc2
 17  where c.owner = 'SCOTT' and c.table_name in ('EMP','DEPT')
 18    and cc.constraint_name = c.constraint_name
 19    and cc2.constraint_name (+) = c.r_constraint_name
 20  /
RES
------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
   REFERENCES "SCOTT"."DEPT" ("DEPTNO");

ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO");

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO");


3 rows selected.

Report message to a moderator

Re: DDL generation [message #687898 is a reply to message #687897] Mon, 17 July 2023 09:14 Go to previous messageGo to next message
smunir362
Messages: 310
Registered: September 2007
Senior Member
Thanks.
But if help me in using regexp_replace with get_ddl .

Report message to a moderator

Re: DDL generation [message #687899 is a reply to message #687898] Mon, 17 July 2023 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do it with some REPLACE:
SQL> select replace(
  2           replace(
  3             replace(
  4               replace(
  5                 dbms_metadata.get_ddl
  6                   (decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER),
  7                 'USING INDEX '),
  8               'ENABLE'),
  9             'DISABLE'),
 10           chr(10)||'  ') res
 11  FROM DBA_CONSTRAINTS
 12  WHERE OWNER = 'SCOTT' and TABLE_NAME in ('EMP','DEPT')
 13  /
RES
------------------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ;
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ;
ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ;

3 rows selected.

Report message to a moderator

Re: DDL generation [message #687900 is a reply to message #687899] Mon, 17 July 2023 13:13 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel, this will not work if index is pre-created. For example:
drop table child purge
/
drop table parent purge
/
create table parent(
                    id number not null
                   )
/
create unique index parent_pk
  on parent(
            id
           )
/
alter table parent
  add constraint parent_pk
    primary key(
               id
              )
/
create table child(
                   parent_id number
                  )
/
alter table child
  add constraint child_fk1
    foreign key(
                parent_id
               )
    references parent
/
DBMS_METADATA will generate:

select  dbms_metadata.get_ddl(decode(constraint_type,'R','REF_CONSTRAINT','CONSTRAINT'),constraint_name,owner) ddl
  from  dba_constraints
  where owner = 'SCOTT'
    and table_name in ('PARENT','CHILD')
/

DDL
-----------------------------------------------------------------------------------

  ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
          REFERENCES "SCOTT"."PARENT" ("ID") ENABLE;


  ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL ENABLE);


  ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")
  USING INDEX "SCOTT"."PARENT_PK"  ENABLE;


SQL>
Notice index name in PARENT_PK. Using your solution will fail to remove it:

select replace(
            replace(
              replace(
                replace(
                  dbms_metadata.get_ddl
                    (decode(CONSTRAINT_TYPE, 'R','REF_CONSTRAINT', 'CONSTRAINT'), CONSTRAINT_NAME, OWNER),
                  'USING INDEX '),
                'ENABLE'),
              'DISABLE'),
            chr(10)||'  ') res
   FROM DBA_CONSTRAINTS
   WHERE OWNER = 'SCOTT' and TABLE_NAME in ('PARENT','CHILD')
/
RES
-------------------------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
          REFERENCES "SCOTT"."PARENT" ("ID") ;

ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL );
ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")"SCOTT"."PARENT_PK"  ;

SQL>
We do need regex. Something like:

select  regexp_replace(
                       dbms_metadata.get_ddl(
                                             decode(
                                                    constraint_type,
                                                    'R','REF_CONSTRAINT',
                                                        'CONSTRAINT'
                                                   ),
                                             constraint_name,
                                              owner
                                            ),
                       ' +((USING +INDEX .*)|ENABLE|DISABLE)([);])',
                       '\3'
                      ) ddl
  from  dba_constraints
  where owner = 'SCOTT'
    and table_name in ('PARENT','CHILD')
/

DDL
-----------------------------------------------------------------------------------

  ALTER TABLE "SCOTT"."CHILD" ADD CONSTRAINT "CHILD_FK1" FOREIGN KEY ("PARENT_ID")
          REFERENCES "SCOTT"."PARENT" ("ID");


  ALTER TABLE "SCOTT"."PARENT" MODIFY ("ID" NOT NULL);


  ALTER TABLE "SCOTT"."PARENT" ADD CONSTRAINT "PARENT_PK" PRIMARY KEY ("ID")
;


SQL>
SY.
P.S. Not sure why OP doesn't want to use your provided solution. It makes it much simpler.

[Updated on: Mon, 17 July 2023 13:16]

Report message to a moderator

Previous Topic: DBMS LDAP package
Next Topic: order by union query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 27 12:53:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.