Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Generate script for PK,FK,UK and check constraint

muttleychessNov 14 2018 — edited Nov 14 2018

Hi

I am trying to generate separate scripts for each constraint of a table, for PK, FK, UK and check, when there is obviously

I tried to use dbms_metadata.get_ddl  , but to generate data like  PCTFREE,PCTUSED ,INITRANS ,MAXTRANS  etc

and I need to generate separate scripts like

BEGIN

    ALTER TABLE EXF_DEDUZ_NOP_PRE_CALC_MERC ADD CONSTRAINT ....

END;

So I tried to generate scripts this way

select

   USU_CONS.constraint_name,

   USU_COLUMNS.table_name,

   USU_COLUMNS.column_name,

   USU_COLUMNS.position,

   USU_CONS.status,

    USU_CONS.constraint_type

,listagg(USU_COLUMNS.column_name,',') within group (order by USU_COLUMNS.position)

       OVER (PARTITION by USU_CONS.constraint_name ) CHAVE

from  USER_CONSTRAINTS USU_CONS, USER_CONS_COLUMNS USU_COLUMNS

where USU_CONS.table_name = 'MYTABLE'

   and USU_CONS.constraint_name = USU_COLUMNS.constraint_name

   and USU_CONS.owner = USU_COLUMNS.owner

--   and USU_CONS.owner = 'MYONWER'

order by     USU_CONS.constraint_type, USU_COLUMNS.position,  USU_COLUMNS.table_name

  

I tried to use listagg because there are some tables that PK  (in some cases fk too)  are concatenated, ie two or more columns ,but I'm not able to group on a line only when this occurs

using

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0    Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

This post has been answered by mathguy on Nov 14 2018
Jump to Answer
Comments
Post Details
Added on Nov 14 2018
5 comments
2,205 views