can AWK be of any use in my case?

[Yogeeraj_1]

Hello experts,

I am working on a project where i have to design scripts that will be run at specified intervals and generate all table specs on my oracle database.

According to my plans, i will have to do the following:
1. Export my database schema (create a dump file)
2. Extract all specs from the dump file
3. Filter out the lines of code that will constitute my script.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
After Step 1, i get file: /BACKUP02/cmt7full2002-0325.dmp

Step 2:
When i extract the specs, the lines comes out as:
===============================================
K250: home/yd/admin/cmtpers>more cmtperrs.sql

REM CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT
REM "NN_ATT001_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT
REM NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) PCTFREE 5 PCTUSED 60
REM INITRANS 2 MAXTRANS 100 STORAGE(INITIAL 675840 NEXT 659456 MINEXTENTS
REM 1 MAXEXTENTS 240 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "PERS" ;
REM ... 2559 rows
REM CREATE TABLE "SCOTT"."ATT002" ("EMPCODE" VARCHAR2(8) CONSTRAINT
REM "NN_ATT002_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT002_2" NOT
REM NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) PCTFREE 10 PCTUSED
REM 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 204800 NEXT 102400
REM MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS
REM 1) TABLESPACE "PERS" ;
REM ... 1088 rows
REM CREATE TABLE "SCOTT"."ATT003" ("EMPCODE" VARCHAR2(8) CONSTRAINT
REM "NN_ATT003_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT003_2" NOT
REM NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) PCTFREE 5 PCTUSED 60
REM INITRANS 4 MAXTRANS 100 STORAGE(INITIAL 819200 NEXT 819200 MINEXTENTS
REM 1 MAXEXTENTS 240 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "PERS" ;
...
===============================================
Step 3.
I do a "grep" on the output of step2:
grep -v '^REM \.\.\.' cmtperrs.sql
to get only the important lines.


Now, for the extract below:
-----------------------------------------------
REM CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT
REM "NN_ATT001_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT
REM NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) PCTFREE 5 PCTUSED 60
REM INITRANS 2 MAXTRANS 100 STORAGE(INITIAL 675840 NEXT 659456 MINEXTENTS
REM 1 MAXEXTENTS 240 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "PERS" ;
-----------------------------------------------
I would like some help to be able to remove the following portions:
a. REM found at each start of line
b. STORAGE till end of line indicated by a ';'

so that i get only the following:
-----------------------------------------------CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT
"NN_ATT001_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT
NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2));
-----------------------------------------------

Thank you in advance for dedicating some of your precious time to help me in my project..

Best Regards
Yogeeraj

[harry d brown jr]

Yogeeraj,

Use "sed" to "strip" off the REM at the beginning of the lines:

sed "s/^REM//"

and then awk to paste the lines together until the ";" is found.

live free or die
harry

[Yogeeraj_1]

Thank you for the reply.

Could you please give me a detail example?

I am no scripting expert! ;)

thanks
yogeeraj

[Steve Steel]

Hi

In 1 line where file = name of input file

yy=$(xx=$(cat $file|sed -e 's/^REM //g');echo $xx|sed -e 's/PCT.*$//g')

echo $yy";" gives

CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT "NN_ATT001_1" NO
T NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT NULL, "RDRCOD" VARCHAR2(3), "
EMODE" VARCHAR2(2));


Steve Steel

[Robin Wakefield]

Hi Yogeeraj,

sed 's/^REM //' filename |
awk '{printf "%s",$0}/;/{print}' |
sed 's/STORAGE.*;//'

This will:

a) remove the REMs
b) join each line together up to the ";" character
c) remove STORAGE upto ";" character

I'm not sure if you wanted all the lines joined up. Anyway, if that's not what you intended, please say so.

Rgds, Robin.

[harry d brown jr]

Yogeeraj,


Try this (see attachment for indentation)

cat cmtperrs.sql | grep -v "^REM \.\.\." | sed "s/^REM //" |
awk 'BEGIN {fullstr="";}
{
instr=$0;
fullstr=fullstr instr;
}
END {
while (fullstr != "") {
SEMIcolon=index(fullstr,";");
workstr=substr(fullstr,1,SEMIcolon);
fullstr=substr(fullstr,SEMIcolon+1);
SEMIcolon=index(fullstr,";");
if (SEMIcolon < 1) {
fullstr = "";
}
workstr=substr(workstr,1,index(workstr," STORAGE("));
workstrlen=length(workstr);
foundit=0;
for(i=workstrlen; foundit == 0 ; i=i-1) {
if (substr(workstr,i,1) == ")") {
foundit=i;
}
}
workstr=substr(workstr,1,foundit);
numLparens=split(workstr,LTarray,"(");
NEWlines=0;
for(i=1; i <= numLparens; i=i+1) {
numRparens=split(LTarray[i],RTarray,")");
if (numRparens > 1) {
for(j=1; j <= numRparens; j=j+1) {
if (RTarray[j] != "") {
NEWlines=NEWlines+1;
NEWarray[NEWlines]=RTarray[j];
if (j == 1) {
NEWarray[NEWlines]=NEWarray[NEWlines] ")";
}
if (j == numRparens) {
NEWarray[NEWlines]=NEWarray[NEWlines] "(";
}
}
}
} else {
NEWlines=NEWlines+1;
NEWarray[NEWlines]=LTarray[i] "(";
}
}
NEWarray[NEWlines]=NEWarray[NEWlines] ")";
NEWstr="";
for(i=1; i <= NEWlines; i=i+1) {
NEWstr=NEWstr NEWarray[i];
}
print NEWstr;
print "\n";
}
}'


live free or die
harry

[Yogeeraj_1]

Thank you all for your replies.
A few feedbacks....>>

Mr. Steeve Steele
===================
Your solution works. Except it does it only for the first line.
see extract below:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
K250: home/yd/admin/cmtpers>./steeve.sh
CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT "NN_ATT001_1" NO
T NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT NULL, "RDRCOD" VARCHAR2(3), "
EMODE" VARCHAR2(2)) ;
K250: home/yd/admin/cmtpers>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Mr. Robin Wakefield
====================
Good solution too. Except
a. there is no ';' at the end
b. '...xxx rows' at the begining
c. does not remove the "PCTFREE 15 PCTUSED 80 INITRANS 3 MAXTRANS 100" section

see extract below:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
... 234 rows CREATE TABLE "SCOTT"."TMT120" ("BROUTE" VARCHAR2(4) CONSTRAINT "N
N_TMT120_1" NOT NULL, "BSTOP" VARCHAR2(7) CONSTRAINT "NN_TMT120_2" NOT NULL, "W
DYTIME" DATE, "SATTIME" DATE, "STPSEQ" NUMBER(2, 0), "PLDESC" VARCHAR2(30) CONS
TRAINT "NN_TMT120_3" NOT NULL, "PUBFARE" NUMBER(8, 2), "STATUS" VARCHAR2(1)) PC
TFREE 15 PCTUSED 80 INITRANS 3 MAXTRANS 100
... 720 rows CREATE TABLE "SCOTT"."TMT130" ("REGION" VARCHAR2(2) CONSTRAINT "N
N_TMT130_1" NOT NULL, "REGNAM" VARCHAR2(20) CONSTRAINT "NN_TMT130_2" NOT NULL)
PCTFREE 15 PCTUSED 80 INITRANS 3 MAXTRANS 100
... 71 rows CREATE TABLE "SCOTT"."TMT140" ("BUSNO" VARCHAR2(8) CONSTRAINT "NN_
TMT140_1" NOT NULL, "SEATS" NUMBER(2, 0) CONSTRAINT "NN_TMT140_2" NOT NULL, "CA
TEGORY" VARCHAR2(1) CONSTRAINT "NN_TMT140_3" NOT NULL, "BOWNER" VARCHAR2(15)) P
CTFREE 15 PCTUSED 80 INITRANS 3 MAXTRANS 100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Mr. Harry d Brown
===================
Wow! Such a long script!
It works but..
a. no ';' at the end of the line
b. should not have '... xxx rows' at the begining.

see extract below:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

... 720 rows CREATE TABLE "SCOTT"."TMT130" ("REGION" VARCHAR2(2) CONSTRAINT "N
N_TMT130_1" NOT NULL, "REGNAM" VARCHAR2(20) CONSTRAINT "NN_TMT130_2" NOT NULL)


... 71 rows CREATE TABLE "SCOTT"."TMT140" ("BUSNO" VARCHAR2(8) CONSTRAINT "NN_
TMT140_1" NOT NULL, "SEATS" NUMBER(2, 0) CONSTRAINT "NN_TMT140_2" NOT NULL, "CA
TEGORY" VARCHAR2(1) CONSTRAINT "NN_TMT140_3" NOT NULL, "BOWNER" VARCHAR2(15))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Some more inputs from you will be most welcomed. I will also working with it. In fact, i think i missed something.. ;)

I should keep the tablespace part!!

i.e.
----------------------------------------------
REM CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT
REM "NN_ATT001_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT
REM NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) PCTFREE 5 PCTUSED 60
REM INITRANS 2 MAXTRANS 100 STORAGE(INITIAL 675840 NEXT 659456 MINEXTENTS
REM 1 MAXEXTENTS 240 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "PERS" ;
REM ... 2559 rows
----------------------------------------------

should look like:
----------------------------------------------
CREATE TABLE "SCOTT"."ATT001" ("EMPCODE" VARCHAR2(8) CONSTRAINT "NN_ATT001_1" NOT NULL, "ATDATE" DATE CONSTRAINT "NN_ATT001_2" NOT NULL, "RDRCOD" VARCHAR2(3), "EMODE" VARCHAR2(2)) TABLESPACE "PERS" ;
----------------------------------------------

Thank you again, all of you, for your replies and for a few more guidelines if possible.

Best Regards
Yogeeraj

PS. I hope you don't mind that i allocate point later.. ;)

[Robin Wakefield]

Hi Yogeeraj,

Is this any closer?

====================================
sed 's/^REM //' filename |
awk '/PCTFREE/{printf ("%s",substr($0,1,index($0,"PCTFREE")-1));S=1;next}
/;/{S=0;print;next}
{if (S==0)printf("%s",$0)}'
====================================

not sure what you mean by "xxx rows"

Rgds, Robin

[Yogeeraj_1]

Hi,

Thank you Mr. Robin.

This is getting very close!! ;)

=============================================
... 90 rows CREATE TABLE "SCOTT"."TMT110" ("BSTOP" VARCHAR2(7) CONSTRAINT "NN_
TMT110_1" NOT NULL, "PLDESC" VARCHAR2(30) CONSTRAINT "NN_TMT110_2" NOT NULL) P
CTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "PERS" ;
... 234 rows CREATE TABLE "SCOTT"."TMT120" ("BROUTE" VARCHAR2(4) CONSTRAINT "N
N_TMT120_1" NOT NULL, "BSTOP" VARCHAR2(7) CONSTRAINT "NN_TMT120_2" NOT NULL, "W
DYTIME" DATE, "SATTIME" DATE, "STPSEQ" NUMBER(2, 0), "PLDESC" VARCHAR2(30) CONS
TRAINT "NN_TMT120_3" NOT NULL, "PUBFARE" NUMBER(8, 2), "STATUS" VARCHAR2(1)) 2
40 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "PERS" ;
... 720 rows CREATE TABLE "SCOTT"."TMT130" ("REGION" VARCHAR2(2) CONSTRAINT "N
N_TMT130_1" NOT NULL, "REGNAM" VARCHAR2(20) CONSTRAINT "NN_TMT130_2" NOT NULL)
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "PERS" ;
... 71 rows CREATE TABLE "SCOTT"."TMT140" ("BUSNO" VARCHAR2(8) CONSTRAINT "NN_
TMT140_1" NOT NULL, "SEATS" NUMBER(2, 0) CONSTRAINT "NN_TMT140_2" NOT NULL, "CA
TEGORY" VARCHAR2(1) CONSTRAINT "NN_TMT140_3" NOT NULL, "BOWNER" VARCHAR2(15))
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "PERS" ;
===============================================

my ...xxx rows are:

... 90 rows
... 234 rows
... 720 rows
... 71 rows

found each time at the start of a line.

thank you very much for some more clues.

Best Regards
Yogeeraj