Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Help required to parse Oracle imp show=y output to DDL Commands
Top Forums Shell Programming and Scripting Help required to parse Oracle imp show=y output to DDL Commands Post 302229176 by aigles on Tuesday 26th of August 2008 10:38:52 AM
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.
 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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
All times are GMT -4. The time now is 01:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy