Shell Programming and Scripting

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

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.