Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Complex sed replacement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Complex sed replacement
# 8  
Old 11-26-2007
Hm, check this (if it doesn't work,
post a sample from the real data):

Code:
nawk 'f{sub(/65536/,v);f=0}/alter/{f=1}1' v=524288 filename

It seams you're trying to alter the storage of certain types of objects,
perhaps dbms_metadata get_ddl and set_transform_param + a bit of custom sql
will be more appropriate.

Or better, consider using LMT with system managed extents (autoallocate) and forget about storage parameters Smilie
Last edited by radoulov; 11-26-2007 at 08:21 AM.. Reason: modified
# 9  
Old 11-26-2007
No luck I'm afraid. The file comes from a third party development system, and I need to create a live schema from it with different storage criteria. I'd also use the syntax to change tablespace names etc.
Original File

CREATE TABLE "ADDRESS" ("ADDRESS_PK" NUMBER NOT NULL ENABLE,
"ADDRESS_TYPE_PK" NUMBER, "COUNTRY_PK" NUMBER, "CONTACT_PK" NUMBER,
"ADDRESS1" VARCHAR2(255), "ADDRESS2" VARCHAR2(255), "ADDRESS3"
VARCHAR2(255), "TOWN" VARCHAR2(255), "POSTCODE" VARCHAR2(20), "CITY"
VARCHAR2(255), "STATUS" NUMBER, "MAIN" NUMBER, "SHIP" NUMBER, "INV"
NUMBER, "UPPER_POSTCODE" VARCHAR2(20), "UPPER_ADDRESS1"
VARCHAR2(255), "CONTACT_NAME" VARCHAR2(100)) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "ADDRESS" ADD CONSTRAINT "ADDRESS_D1" PRIMARY KEY
("ADDRESS_PK") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
ALTER TABLE "ADDRESS" ADD CONSTRAINT "PK_ADDRESS2" CHECK
(ADDRESS_PK is not null) ENABLE NOVALIDATE ;
CREATE TABLE "AUDIT_TRAIL" ("AUDIT_TRAIL_PK" NUMBER,
"AUDIT_TRAIL_DATE" DATE, "USR_PK" NUMBER, "TYPE" NUMBER, "TYPE_PK"
NUMBER, "EVENT" VARCHAR2(255), "DESCRIPTION" VARCHAR2(4000)) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 10 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "AUDIT_TRAIL" ADD CONSTRAINT "PK_AUDIT_TRAIL"
PRIMARY KEY ("AUDIT_TRAIL_PK") USING INDEX PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 10 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
CREATE TABLE "CONTACT" ("CONTACT_PK" NUMBER NOT NULL ENABLE,
"LANGUAGE_PK" NUMBER, "CUSTOMER_PK" NUMBER, "FIRST_NAME"
VARCHAR2(255), "MIDDLE_NAME" VARCHAR2(255), "SURNAME" VARCHAR2(255),
"SALUTATION" VARCHAR2(20), "GENDER" VARCHAR2(20), "DATE_OF_BIRTH"
DATE, "STATUS" NUMBER, "ENQUIRYPASS" VARCHAR2(255), "CUSTOMER_STATUS"
NUMBER, "MESSAGING_TYPE_PK" NUMBER, "CUSTOMER_RANK" NUMBER,
"EXTRA_FIELD_DATE1" DATE, "EXTRA_FIELD_DATE2" DATE, "CONTACT_NUM"
VARCHAR2(20), "EMBARGOED" NUMBER, "UPPER_CONTACT_NUM" VARCHAR2(20),
"UPPER_SURNAME" VARCHAR2(255), "UPPER_FIRST_NAME" VARCHAR2(255),
"CONTACT_TYPE_PK" NUMBER, "NAME" VARCHAR2(255),
"ORGANISATION_TYPE_PK" NUMBER, "EMBARGOED_DATE" DATE,
"EMBARGOED_BY_USER" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "CONTACT" ADD CONSTRAINT "SYS_COO1678" CHECK
(CONTACT_PK IS NOT NULL) ENABLE NOVALIDATE ;
ALTER TABLE "CONTACT" ADD CONSTRAINT "PK_CONTACT2" PRIMARY
KEY ("CONTACT_PK") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
CREATE TABLE "CONTACT_NOTES" ("CONTACT_NOTES_PK" NUMBER NOT
NULL ENABLE, "CONTACT_PK" NUMBER, "DESCRIPTION" VARCHAR2(255),
"USR_PK" NUMBER, "DATE_CREATED" DATE, "NOTES" VARCHAR2(4000)) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD"
LOGGING NOCOMPRESS ;
CREATE TABLE "MESSAGING_DETAILS" ("MESSAGING_DETAILS_PK"
NUMBER NOT NULL ENABLE, "CONTACT_PK" NUMBER, "MESSAGING_TYPE_PK"
NUMBER, "DESCRIPTION" VARCHAR2(255), "AREA_CODE" VARCHAR2(50),
"COUNTRY_CODE" VARCHAR2(50), "STATUS" NUMBER) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;

Desired Output (changed lines have * at the start)

CREATE TABLE "ADDRESS" ("ADDRESS_PK" NUMBER NOT NULL ENABLE,
"ADDRESS_TYPE_PK" NUMBER, "COUNTRY_PK" NUMBER, "CONTACT_PK" NUMBER,
"ADDRESS1" VARCHAR2(255), "ADDRESS2" VARCHAR2(255), "ADDRESS3"
VARCHAR2(255), "TOWN" VARCHAR2(255), "POSTCODE" VARCHAR2(20), "CITY"
VARCHAR2(255), "STATUS" NUMBER, "MAIN" NUMBER, "SHIP" NUMBER, "INV"
NUMBER, "UPPER_POSTCODE" VARCHAR2(20), "UPPER_ADDRESS1"
VARCHAR2(255), "CONTACT_NAME" VARCHAR2(100)) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "ADDRESS" ADD CONSTRAINT "ADDRESS_D1" PRIMARY KEY
("ADDRESS_PK") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
*STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
ALTER TABLE "ADDRESS" ADD CONSTRAINT "PK_ADDRESS2" CHECK
(ADDRESS_PK is not null) ENABLE NOVALIDATE ;
CREATE TABLE "AUDIT_TRAIL" ("AUDIT_TRAIL_PK" NUMBER,
"AUDIT_TRAIL_DATE" DATE, "USR_PK" NUMBER, "TYPE" NUMBER, "TYPE_PK"
NUMBER, "EVENT" VARCHAR2(255), "DESCRIPTION" VARCHAR2(4000)) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 10 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "AUDIT_TRAIL" ADD CONSTRAINT "PK_AUDIT_TRAIL"
PRIMARY KEY ("AUDIT_TRAIL_PK") USING INDEX PCTFREE 10 INITRANS 2
*MAXTRANS 255 STORAGE(INITIAL 524288 FREELISTS 10 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
CREATE TABLE "CONTACT" ("CONTACT_PK" NUMBER NOT NULL ENABLE,
"LANGUAGE_PK" NUMBER, "CUSTOMER_PK" NUMBER, "FIRST_NAME"
VARCHAR2(255), "MIDDLE_NAME" VARCHAR2(255), "SURNAME" VARCHAR2(255),
"SALUTATION" VARCHAR2(20), "GENDER" VARCHAR2(20), "DATE_OF_BIRTH"
DATE, "STATUS" NUMBER, "ENQUIRYPASS" VARCHAR2(255), "CUSTOMER_STATUS"
NUMBER, "MESSAGING_TYPE_PK" NUMBER, "CUSTOMER_RANK" NUMBER,
"EXTRA_FIELD_DATE1" DATE, "EXTRA_FIELD_DATE2" DATE, "CONTACT_NUM"
VARCHAR2(20), "EMBARGOED" NUMBER, "UPPER_CONTACT_NUM" VARCHAR2(20),
"UPPER_SURNAME" VARCHAR2(255), "UPPER_FIRST_NAME" VARCHAR2(255),
"CONTACT_TYPE_PK" NUMBER, "NAME" VARCHAR2(255),
"ORGANISATION_TYPE_PK" NUMBER, "EMBARGOED_DATE" DATE,
"EMBARGOED_BY_USER" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;
ALTER TABLE "CONTACT" ADD CONSTRAINT "SYS_COO1678" CHECK
(CONTACT_PK IS NOT NULL) ENABLE NOVALIDATE ;
ALTER TABLE "CONTACT" ADD CONSTRAINT "PK_CONTACT2" PRIMARY
KEY ("CONTACT_PK") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
*STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "AAL01MD" LOGGING ENABLE ;
CREATE TABLE "CONTACT_NOTES" ("CONTACT_NOTES_PK" NUMBER NOT
NULL ENABLE, "CONTACT_PK" NUMBER, "DESCRIPTION" VARCHAR2(255),
"USR_PK" NUMBER, "DATE_CREATED" DATE, "NOTES" VARCHAR2(4000)) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD"
LOGGING NOCOMPRESS ;
CREATE TABLE "MESSAGING_DETAILS" ("MESSAGING_DETAILS_PK"
NUMBER NOT NULL ENABLE, "CONTACT_PK" NUMBER, "MESSAGING_TYPE_PK"
NUMBER, "DESCRIPTION" VARCHAR2(255), "AREA_CODE" VARCHAR2(50),
"COUNTRY_CODE" VARCHAR2(50), "STATUS" NUMBER) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AAL01MD" LOGGING NOCOMPRESS ;

Regards
Bob
# 10  
Old 11-26-2007
So:
Code:
nawk '/ALTER/{sub(/65536/,v)}1' ORS= RS=";" v=524288 filename

or:

Code:
nawk 'f&&sub(/65536/,v)||/CREATE/{f=0};/ALTER/{f=1}1' v=524288  filename

# 11  
Old 11-27-2007
Wow radoulov,
That's brilliant. It works perfectly.
This is going to save me so much laborious editing.
I can't thank you enough for your efforts.

Regards
Bob Smilie
# 12  
Old 11-27-2007
Quote:
Originally Posted by one_ring99
Wow radoulov,
That's brilliant. It works perfectly.
This is going to save me so much laborious editing.
I can't thank you enough for your efforts.

Regards
Bob Smilie
You're welcome!
Glad we could help Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

sed text replacement

Hello, I'm using Bash and Sed to replace text within a text file (1.txt) twice in one script. Using a for loop I'm initially replacing any 'apple' words with the variable 'word1' ("leg). I'm then using another for loop to replace any 'apple' words with the variable 'word2' ("arm"). This task is... (2 Replies)
Discussion started by: Flip-Flop
2 Replies

2. UNIX for Dummies Questions & Answers

sed replacement inplace

I need to make permanent changes in the file after find and replace using sed. for this i am using sed -i However this is not working. says sed: illegal option -- i I am working on Sun Solaris uname -a SunOS aspsun14 5.10 Generic_150400-13 sun4u sparc SUNW,SPARC-Enterprise any other work... (3 Replies)
Discussion started by: gotamp
3 Replies

3. Shell Programming and Scripting

sed replacement error

Hi ,"environment":"accent-release","build":"ac-1112_c_Alph_Nop-release_rele-1112_c_Alph_Nop-release_rele","tags":"" above is the config file, where in i want to replace value after build inside the double quotes... that value i'll be passing as parameter for example if m passing ... (1 Reply)
Discussion started by: nikhil jain
1 Replies

4. Shell Programming and Scripting

SED replacement

Hi, i have a file with lines, file.txt ------- test is fun testing is better I need to replace 'test' to 'develop' and i used, a=test b=develop sed "s,$a,$b,g" -------- but i see the word 'testing' is also replaced. Need some solution. Is there any way i could replace only 'test' ? (4 Replies)
Discussion started by: giri_luck
4 Replies

5. Shell Programming and Scripting

A complex sed statement

I have following requirement. Say, my text file contains following patterns {2010501005|XXGpvertex|9|0|17|0|{|{30100001|XXparameter_set|@@@@{{30001002|XXparameter|!prototype_path|$AB_COMPONENTS/Sort/Sort.mpc|3|2|Pf$|@{0|}} }}@0|@315000|78500|335000|99000|114000|87000|17|And the Sort|Ab... (8 Replies)
Discussion started by: Shell_Learner
8 Replies

6. Shell Programming and Scripting

Replacement with sed

I am trying to replace the line which has string "tablespace" not case senstive.... with below simple script: mysrcipt.sh sed "s/.*/TABLESPACE USERS/g" create_table > tmp mv tmp create_table Is there any better way to do it? If Search string tooooooo long it will be tough to code in... (4 Replies)
Discussion started by: ganeshd
4 Replies

7. Shell Programming and Scripting

Help with sed replacement

This seems like it should be an easy problem, but I'm a noob and I can't figure it out. I'm trying to use sed, but would be happy to use anything that does the job. I am trying to trim off a fixed number of unknown characters from 2 different : delimited fields while keeping the intervening... (4 Replies)
Discussion started by: helix_w
4 Replies

8. Shell Programming and Scripting

SED complex string replacement

sed -i 's:"ps -ef | grep $(cat $PID_FILE) | grep -v grep":"ps -C java -o pid,cmd | grep ${SERVER_NAME} | cut -d' ' -f1 | grep -v grep":g' scriptName That's what I'm attempting to do. I'm attempting to replace this: ps -ef | grep $(cat $PID_FILE) | grep -v grep with this: ps -C java -o... (5 Replies)
Discussion started by: cbo0485
5 Replies

9. Shell Programming and Scripting

Need Replacement for sed

Hi Can anyone provide me the replacement of sed with xargs perl syntax for the below sed -e :a -e '/;$/!N;s/\n//; ta' -e 's/;$//' This should be without looping has to take minimal time for search (0 Replies)
Discussion started by: dbsurf
0 Replies

10. UNIX for Dummies Questions & Answers

Replacement using sed

Hi I have the following file that i need to run a sed command on 1<tab>running 2<tab>running 3<tab>running 4<tab>running I want to be able to replace a line i.e the second one with '2<tab>failed'. As the first number is unique that can be used to search for the relevant line (using ^2 i... (5 Replies)
Discussion started by: handak9
5 Replies
Login or Register to Ask a Question