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