Hi,
I have a file that I would like to translate using sed.
I can do some basic sed commands, but Im afraid this level is beyond me.
I have this file -
...alter...
..
...65536...
...
...65536...
...
...alter...
...
...65536...
etc
What I would like to do is replace the first occurrence of the 65536 after each line that has 'alter' in it (there may be serveral lines with 65536 between 'alter' lines) .
Any help would be much appreciated thanks.
Bob
If the input is always like the one you posted:
alter
one record
pattern to replace
% cat file
...alter...
..
...65536...
...
...65536...
...
...alter...
...
...65536...
% sed '/alter/{N;N;s/65536/whatever/;}' file
...alter...
..
...whatever...
...
...65536...
...
...alter...
...
...whatever...
If not (more records between the word alter and the pattern)
with Awk:
awk 'f&&/65536/{sub(/65536/,"whatever");f=0}/alter/{f=1}1' filename
awk 'f&&sub(/65536/,"whatever")&&f=0;/alter/{f=1}1' filename
Use nawk or /usr/xpg4/bin/awk on Solaris.
Thanks radoulov.
There may be any number of records between the
alter and 65536, so your awk code is on the mark.
I'll check it out first thing tomorrow (and yes, I'm on Solaris)
Do the two lines of code do the same?
Great work. I really appreciate the help.
The awk script works great thanks, but I've discovered that there may be instances where there is no line of '65536' between the 'alter' lines.
Is there any way to skip to the next 'alter', if a 65536 line does not exist?
Thanks
What do you mean by skip: you want to _ remove _ the records from alter to alter if there is not 65536 in between?
Hi,
This one should be ok for you.
code:
nawk '
{
if(f==1 && $0 ~ /65536/)
{
sub(/65536/,"aaa")
f=0
}
if ($0 ~ /alter/ )
f=1
print
}' filename
Apologies for the confusion in the original problem statement. I should have given more detail.
The file I am processing contains SQL statements (It's from an Oracle database export).
For each table these is a block of statements (each statement ends with ';') like -
create table tablename ....
....
.... 65536 ....
.... ;
alter table tablename
............ ;
alter table tablename
.... 65536 .... ;
alter table tablename
.... 65536 .... ;
alter table tablename
............ ;
<next create statement>
For each table there will only be one 'create' statement, but any number of 'alter' statements.
Each 'alter' statement (and the 'create' statement) has a variable number of lines.
The 'alter' statements may or may not (I missed this on the original problem definition) have the '65536'.
I am trying to change the '65536' to a variable value (using a unix variable) which comes from a parameter file. The value will be something like '524288', but only for the 'alter' statements.
So the resulting file would be like -
create table tablename ....
....
.... 65536 ....
.... ;
alter table tablename
............ ;
alter table tablename
.... 524288 .... ;
alter table tablename
.... 524288 .... ;
alter table tablename
............ ;
<next create statement>
The awk code works fine until it hits the first 'alter' with no '65536' in it, when it changes the '65536' of the next occurrence, which may be in the 'create' statement.
Your efforts are much appreciated.
Regards
Bob
Hm, check this (if it doesn't work,
post a sample from the real data):
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 
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
So:
nawk '/ALTER/{sub(/65536/,v)}1' ORS= RS=";" v=524288 filename
or:
nawk 'f&&sub(/65536/,v)||/CREATE/{f=0};/ALTER/{f=1}1' v=524288 filename
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
You're welcome!
Glad we could help 