Help required to parse Oracle imp show=y output to DDL Commands


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help required to parse Oracle imp show=y output to DDL Commands
# 1  
Old 08-26-2008
Help required to parse Oracle imp show=y output to DDL Commands

Hi,

I generated an Oracle schema DDL script file using the show=y option of the Oracle import utility but the file that it generates needs a little more formating before we can run this as simple DDL comands to generate the schema at Target using the script file.Here is the simplified output of the file generated for one of the tables which i am trying to parse to a set of CREATE TABLE and ALTER TABLE commands

Output Generated
------------------

"CREATE TABLE "ACCOUNT" ("ACCT_ID" CHAR(4) NOT NULL DISABLE, "ACCT_NM" VARCH"
"AR2(30) NOT NULL DISABLE, "ACCT_LONG_NM" VARCHAR2(50), "PROD_CD" VARCHAR2(8"
"), "MARKET_CD" VARCHAR2(6), "VEHICLE_CD" VARCHAR2(6), "PRIM_BMARK_ID" VARCH"
"AR2(12), "WMC_COMPANY_CD" VARCHAR2(4), "FAM_ID" CHAR(5) NOT NULL DISABLE, ""
"PERF_INCEP_DT" DATE, "OPEN_DT" DATE, "CLOSE_DT" DATE, "MODEL_FLG" CHAR(1) N"
"OT NULL DISABLE, "INDEX_ID" VARCHAR2(12), "ACCT_TYP" VARCHAR2(9), "ACCT_SER"
"VICER_CD" CHAR(1), "CNTRY_CD" CHAR(2), "AMORT_TYP" VARCHAR2(12), "TAX_ID" V"
"ARCHAR2(12), "CONTRACT_DT" DATE, "FUND_TYPE" VARCHAR2(6), "ACCT_SYSTEM" CHA"
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
"T" DATE, "CASH_EQVLNT_DEFN" VARCHAR2(30), "EXEMPT_FROM_SEC_RULE" VARCHAR2(8"
"), "QUALIFIED_PURCHASER_CD" CHAR(1), "LAST_BILL_DT" DATE, "ANTICIP_LAST_TRA"
"DE_TS" DATE, "RETAIN_ASSETS_FLG" CHAR(1), "TERM_NOTIFY_DT" DATE, "CARE_MAIN"
"T_FLG" CHAR(1), "EST_CLOSE_DT" DATE, "COMPLIANCE_INSTANCE_CD" VARCHAR2(12),"
" "INVESTMENT_MGMT_FEE_CD" VARCHAR2(8)) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "
""WMC_DATA" LOGGING NOCOMPRESS"
" ALTER TABLE "ACCOUNT" MODIFY ("MODEL_FLG" DEFAULT 'N' )"
" ALTER TABLE "ACCOUNT" MODIFY ("INS_TS" DEFAULT SYSDATE)"
" ALTER TABLE "ACCOUNT" MODIFY ("UPD_TS" DEFAULT SYSDATE)"
" ALTER TABLE "ACCOUNT" MODIFY ("BOOK_CLOSE_DAY" DEFAULT 3)"
" ALTER TABLE "ACCOUNT" MODIFY ("UPD_LOGIN" DEFAULT USER)"
" ALTER TABLE "ACCOUNT" MODIFY ("ASSET_ALLOC_DISCR_FLG" DEFAULT 'N' "
.
.
.
.
.
.
.
.
.
.
.
.
.
" ALTER TABLE "ACCOUNT" MODIFY ("AUTO_CASH_RECON_FLG" DEFAULT 'N' "
" )"
" ALTER TABLE "ACCOUNT" MODIFY ("NASD_2790_RULE" DEFAULT 'NR')"
. . skipping table "ACCOUNT"

Desired Output
--------------

CREATE TABLE ACCOUNT (ACCT_ID CHAR(4) NOT NULL DISABLE, ACCT_NM VARCH
AR2(30) NOT NULL DISABLE, ACCT_LONG_NM VARCHAR2(50), PROD_CD VARCHAR2(8
), MARKET_CD VARCHAR2(6), VEHICLE_CD VARCHAR2(6), PRIM_BMARK_ID VARCH
AR2(12), WMC_COMPANY_CD VARCHAR2(4), FAM_ID CHAR(5) NOT NULL DISABLE,
PERF_INCEP_DT DATE, OPEN_DT DATE, CLOSE_DT DATE, MODEL_FLG CHAR(1) N
OT NULL DISABLE, INDEX_ID VARCHAR2(12), ACCT_TYP VARCHAR2(9), ACCT_SER
VICER_CD CHAR(1), CNTRY_CD CHAR(2), AMORT_TYP VARCHAR2(12), TAX_ID V
ARCHAR2(12), CONTRACT_DT DATE, FUND_TYPE VARCHAR2(6), ACCT_SYSTEM CHA
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
T DATE, CASH_EQVLNT_DEFN VARCHAR2(30), EXEMPT_FROM_SEC_RULE VARCHAR2(8
), QUALIFIED_PURCHASER_CD CHAR(1), LAST_BILL_DT DATE, ANTICIP_LAST_TRA
DE_TS DATE, RETAIN_ASSETS_FLG CHAR(1), TERM_NOTIFY_DT DATE, CARE_MAIN
T_FLG CHAR(1), EST_CLOSE_DT DATE, COMPLIANCE_INSTANCE_CD VARCHAR2(12),
INVESTMENT_MGMT_FEE_CD VARCHAR2(8)) PCTFREE 10 PCTUSED 40 INITRANS 1 MA
XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
WMC_DATA LOGGING NOCOMPRESS
/
ALTER TABLE ACCOUNT MODIFY (MODEL_FLG DEFAULT 'N' )
/
ALTER TABLE ACCOUNT MODIFY (INS_TS DEFAULT SYSDATE)
/
ALTER TABLE ACCOUNT MODIFY (UPD_TS DEFAULT SYSDATE)
/
ALTER TABLE ACCOUNT MODIFY (BOOK_CLOSE_DAY DEFAULT 3)
/
ALTER TABLE ACCOUNT MODIFY (UPD_LOGIN DEFAULT USER)
/
ALTER TABLE ACCOUNT MODIFY (ASSET_ALLOC_DISCR_FLG DEFAULT 'N'
.
.
.
.
.
.
.
.
.
.
.
.
.
ALTER TABLE ACCOUNT MODIFY (AUTO_CASH_RECON_FLG DEFAULT 'N'
)
/
ALTER TABLE ACCOUNT MODIFY (NASD_2790_RULE DEFAULT 'NR')
/

Requirements
------------

We need to remove the " from each line enclosing the line as in the output file
Introduce 2 new lines between start and end of each command and provide a / to be able to run as a command in sql

Every Create Table command start with "CREATE TABLE.. and ends with LOGGING NOCOMPRESS"

Every ALTER TABLE Command starts with " ALTER TABLE... and ends with )"

Every New Create Table starts After . . skipping table line

Basically my outfile has a collection of these commands so i need a script that can run in a loop and parse all these statements and put in an outfile

Quick response is appreciated...

Thanks and Kind Regards,
Rajan.S

Last edited by rajan_san; 08-26-2008 at 09:46 AM.. Reason: Missed one requirement
# 2  
Old 08-26-2008
As a starting point :
Code:
awk '
/^".*"$/ {
   $0 = substr($0, 2, length-2);
}

/^ *CREATE TABLE/ {
   sql = $0;
   while ($0 !~ /LOGGING NOCOMPRESS$/) {
      if (! getline) break;
      $0 = substr($0, 2, length-2);
      sql = sql "\n" $0;
   }
   print sql "\n/";
   next;
}

/^ *ALTER TABLE/ {
   sql = $0;
   while (substr($0,length,1) != ")") {
      if (! getline) break;
      $0 = substr($0, 2, length-2);
      sql = sql "\n" $0;
   }
   print sql "\n/";
   next;
}

' inputfile

Inputfile:
Code:
"CREATE TABLE "ACCOUNT" ("ACCT_ID" CHAR(4) NOT NULL DISABLE, "ACCT_NM" VARCH"
"AR2(30) NOT NULL DISABLE, "ACCT_LONG_NM" VARCHAR2(50), "PROD_CD" VARCHAR2(8"
"), "MARKET_CD" VARCHAR2(6), "VEHICLE_CD" VARCHAR2(6), "PRIM_BMARK_ID" VARCH"
"AR2(12), "WMC_COMPANY_CD" VARCHAR2(4), "FAM_ID" CHAR(5) NOT NULL DISABLE, ""
"PERF_INCEP_DT" DATE, "OPEN_DT" DATE, "CLOSE_DT" DATE, "MODEL_FLG" CHAR(1) N"
"OT NULL DISABLE, "INDEX_ID" VARCHAR2(12), "ACCT_TYP" VARCHAR2(9), "ACCT_SER"
"VICER_CD" CHAR(1), "CNTRY_CD" CHAR(2), "AMORT_TYP" VARCHAR2(12), "TAX_ID" V"
"ARCHAR2(12), "CONTRACT_DT" DATE, "FUND_TYPE" VARCHAR2(6), "ACCT_SYSTEM" CHA"
"R(5)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "WMC_DATA" LOGGING NOCOMPRESS"
" ALTER TABLE "ACCOUNT" MODIFY ("MODEL_FLG" DEFAULT 'N' )"
" ALTER TABLE "ACCOUNT" MODIFY ("INS_TS" DEFAULT SYSDATE)"
" ALTER TABLE "ACCOUNT" MODIFY ("UPD_TS" DEFAULT SYSDATE)"
" ALTER TABLE "ACCOUNT" MODIFY ("BOOK_CLOSE_DAY" DEFAULT 3)"
" ALTER TABLE "ACCOUNT" MODIFY ("UPD_LOGIN" DEFAULT USER)"
" ALTER TABLE "ACCOUNT" MODIFY ("ASSET_ALLOC_DISCR_FLG" DEFAULT 'N')"
" ALTER TABLE "ACCOUNT" MODIFY ("NASD_2790_RULE" DEFAULT 'NR' "
")"
. . skipping table "ACCOUNT"

Output:
Code:
CREATE TABLE "ACCOUNT" ("ACCT_ID" CHAR(4) NOT NULL DISABLE, "ACCT_NM" VARCH
AR2(30) NOT NULL DISABLE, "ACCT_LONG_NM" VARCHAR2(50), "PROD_CD" VARCHAR2(8
), "MARKET_CD" VARCHAR2(6), "VEHICLE_CD" VARCHAR2(6), "PRIM_BMARK_ID" VARCH
AR2(12), "WMC_COMPANY_CD" VARCHAR2(4), "FAM_ID" CHAR(5) NOT NULL DISABLE, "
PERF_INCEP_DT" DATE, "OPEN_DT" DATE, "CLOSE_DT" DATE, "MODEL_FLG" CHAR(1) N
OT NULL DISABLE, "INDEX_ID" VARCHAR2(12), "ACCT_TYP" VARCHAR2(9), "ACCT_SER
VICER_CD" CHAR(1), "CNTRY_CD" CHAR(2), "AMORT_TYP" VARCHAR2(12), "TAX_ID" V
ARCHAR2(12), "CONTRACT_DT" DATE, "FUND_TYPE" VARCHAR2(6), "ACCT_SYSTEM" CHA
R(5)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 F
REELISTS 1 FREELIST GROUPS 1) TABLESPACE "WMC_DATA" LOGGING NOCOMPRESS
/
 ALTER TABLE "ACCOUNT" MODIFY ("MODEL_FLG" DEFAULT 'N' )
/
 ALTER TABLE "ACCOUNT" MODIFY ("INS_TS" DEFAULT SYSDATE)
/
 ALTER TABLE "ACCOUNT" MODIFY ("UPD_TS" DEFAULT SYSDATE)
/
 ALTER TABLE "ACCOUNT" MODIFY ("BOOK_CLOSE_DAY" DEFAULT 3)
/
 ALTER TABLE "ACCOUNT" MODIFY ("UPD_LOGIN" DEFAULT USER)
/
 ALTER TABLE "ACCOUNT" MODIFY ("ASSET_ALLOC_DISCR_FLG" DEFAULT 'N')
/
 ALTER TABLE "ACCOUNT" MODIFY ("NASD_2790_RULE" DEFAULT 'NR'
)
/

Are you sure that you can split sql commands anywhere ?

Jean-Pierre.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help required in UNIX commands

I have 40000 records in a file where i need to change the 7th field date format from 05142016 to 20160514 I have given field below. any help would be highly appreciated. 364512|9999999|9999999|210553|195495477|195257095|05142016|10009|36313 ---------- Post updated at 05:02 AM... (2 Replies)
Discussion started by: arun888
2 Replies

2. Shell Programming and Scripting

Parse through ~21,000 Database DDL statements -- Fastest way to perform search, replace and insert

Hello All: We are looking to search through 2000 files with around 21,000 statements where we have to search, replace and insert a pattern based on the following: 1) Parse through the file and check for CREATE MULTISET TABLE or CREATE SET TABLE statements.....and they always end with ON... (5 Replies)
Discussion started by: madhunk
5 Replies

3. Shell Programming and Scripting

script to mail monitoring output if required or redirect output to log file

Below script perfectly works, giving below mail output. BUT, I want to make the script mail only if there are any D-Defined/T-Transition/B-Broken State WPARs and also to copy the output generated during monitoring to a temporary log file, which gets cleaned up every week. Need suggestions. ... (4 Replies)
Discussion started by: aix_admin_007
4 Replies

4. Shell Programming and Scripting

Named Pipe & Oracle imp

Hi, I have a little knowledge about mkfifo, first-in-first-out, a special file, a named pipe and it involves inter-process communication, as multiple processes can write data into a single file. Here, I would like to know how this is helpful in executing the below Oracle 'imp' command... (1 Reply)
Discussion started by: Dev_Dev
1 Replies

5. Shell Programming and Scripting

extract DDL - output every match to separate file

Hi, i want to extract the 'CREATE INDEX' or 'CREATE UNIQUE INDEX' statements from a ddl file and output each match to a separate file. i was looking around the net but couldnīt find anything. a possible sed-script could be: sed -n '/CREATE*INDEX*/,/COMMIT/p' filename.ddlbut i couldnīt find out... (11 Replies)
Discussion started by: CactusMoon
11 Replies

6. UNIX for Dummies Questions & Answers

Help required in disabling commands.

I want to disable following commands in my linux distribution (Thanks to Linux hardening guide) # which rcp /usr/kerberos/bin/rcp # which rlogin /usr/kerberos/bin/rlogin # which rsh /usr/kerberos/bin/rsh When checked they were all part of krb5-workstation-1.6.1-25.el5 rpm. # rpm -qf... (2 Replies)
Discussion started by: pinga123
2 Replies

7. Solaris

Libraries required by commands

In solaris 10 how to I know what libraries are required by a particular command? please advise (1 Reply)
Discussion started by: Tirmazi
1 Replies

8. Solaris

Can history commands show what time command executed

On Solaris 8 and 10 is there a way history command can show what time a particular command was executed. Pls reply. Thanks (2 Replies)
Discussion started by: Tirmazi
2 Replies

9. UNIX for Dummies Questions & Answers

Help required for these commands

Hi, I was going through some environment scripts where I require to make some changes. There are a couple of commands I cant understand at all. 1:- SIDS=${*:-} 2:- for element in detail.func common1.func status.func stop_start.func mc.global log.func under the for loop $element has been... (1 Reply)
Discussion started by: ayanbiswas
1 Replies

10. UNIX for Dummies Questions & Answers

Oracle like syntax required

m kinda new to unix. i have been trying to write a script where i am trying to switch between users. but the problem is that the syntax like USERNAME/PASSWORD (like oracle SCOT/TIGER) is not working. if i write su USERNAME then the script goes to the command prompt and asks for user to enter... (0 Replies)
Discussion started by: ShellBoy
0 Replies
Login or Register to Ask a Question