how to generate ddl for different objects in different files

[kytemaniac]

HI,

how to generate ddl for different objects in different files with the additional requirements:

the constraints is to be generated in different files
package specifications and package body must be different files


here db version 11.2.0.2

here's what I have done

Code: Select all

--set long 9999
set long 20000

set heading off
set echo off
set feedback off

spool D:\william\excel\project\alpha_bank\abkdb3\dbms_metadata\get_tables_output_false_segment_attributes_false_constraints.log

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);    

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

spool off



part of output

Code: Select all

  CREATE TABLE "SUPERB"."ACTIVATED_FILTER"                                   
   (   "AFF_ID" NUMBER(8,0),                                                       
   "FADD_ID" NUMBER(8,0),                                                         
   "CF_ID" NUMBER(8,0),                                                           
   "ACTIVATE_STATUS" NUMBER,                                                     
   "LAST_UPDATE_BY" NUMBER(8,0),                                                 
   "LAST_UPDATE_DATE" DATE,                                                       
    CONSTRAINT "FK_ACTFILTER_CF_ID" FOREIGN KEY ("CF_ID")                         
     REFERENCES "SUPERB"."CARD_FILTER" ("CF_ID") ENABLE,                       
    CONSTRAINT "FK_ACTFILTER_FAD_ID" FOREIGN KEY ("FAD_ID")                       
     REFERENCES "SUPERB"."FILTER_ACTIVATION_DETAIL" ("FAD_ID") ON DELETE CASCAD
E ENABLE                                                                       

   )                                                                           




now if you notice this I have already disable the constraints , still output is showing constraints, I don't understand why?

as for the constraints part I have done a impdp with sqlfile option

Code: Select all

SCHEMAS=SUPERB
CONTENT=METADATA_ONLY
DUMPFILE=EXP_SG_PROD_SUPERB_05FEB13.DMP
LOGFILE=impdp_EXP_SG_PROD_SUPERB_METADATA_SQLFILE_20130220_1013.log
SQLFILE=create_ddl_20130220.sql
DIRECTORY=BKDB1_DUMP




partial output for constraints in SQLFILE=create_ddl_20130220.sql , is as follow

Code: Select all

ALTER TABLE "SUPERB"."ACTIVATED_FILTER" ADD CONSTRAINT "FK_ACTFILTER_FAD_ID" FOREIGN KEY ("FAD_ID")
     REFERENCES "SUPERB"."FILTER_ACTIVATION_DETAIL" ("FAD_ID") ON DELETE CASCADE ENABLE;


now how do I do the following:

place each constraints in a different files,
how do remove the double quotes
how do remove the schema name?

thanks a lot for the assistance rendered?

thanks

[Tim...]

Hi.

Regarding the constraints, you need to explicitly stop REF_CONSTRAINTS.

Code: Select all

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);   

Regarding the splitting of things into different files, you can't really do thins with SPOOL. You will probably have to write a PL/SQL procedure to write the information our to files using UTL_FILE.

Cheers

Tim...

[kytemaniac]

Code: Select all


EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);    
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');



output

Code: Select all

CREATE TABLE "SUPERB"."ME_BUCKET"                                         
   (   "ME_BUCKET_IID" NUMBER(10,0),                                             
   "STATUS" CHAR(1 CHAR),                                                         
   "BUCKET_UPD_CNT" NUMBER(10,0) DEFAULT 0,                                       
    CONSTRAINT "PK_MD_BUCKET" PRIMARY KEY ("ME_BUCKET_IID") ENABLE               
   ) ORGANIZATION INDEX NOCOMPRESS  OVERFLOW


if you notice the output, it does generate the primary key, I don't understand why. from http://docs.oracle.com/cd/E11882_01/app ... m#BGBJBFGE, I probably already explore all the options already.

can some one tell me how to not to display the primary key constraint? thanks

[Tim...]

Hi.

If you read the page you linked to you would see the notes for CONSTRAINT say, "Does not include: primary key constraint for IOT".

Think about this for a second. How could you possibly have an index organized table IOT without a primary key. The table data is part of the index keys. The whole table is the primary key.

If you did your query against a regular table it would work as you expect.

Cheers

Tim...