Shell Programming and Scripting

Code:

$
$
$ # display the content of the file "name"
$ cat name
tbla
tblc
$
$ # display the content of the file "temp"
$ cat temp
ALTER TABLE tbla
ADD  ( cola1         VARCHAR2(10),
       cola2  VARCHAR2(11)  );

ALTER TABLE tblb
ADD  ( colb1         VARCHAR2(12),
       colb2  VARCHAR2(13)  );

ALTER TABLE tblc
ADD  ( colc1         VARCHAR2(14),
       colc2  VARCHAR2(15)  );
$
$
$ # now run the Perl one-liner
$ perl -ne 'chomp; $fmt = "%-10s  %-10s  %s\n";
>           if ($ARGV eq "name") {$x{$_}++}
>           elsif (/^ALTER TABLE (.*)/ and defined $x{$1}) {$in=1; $tbl=$1}
>           elsif ($in and /^ADD\s*\(\s*(\w+)\s*(.*?),/) {printf($fmt,$tbl,$1,$2)}
>           elsif ($in and /^\s*(\w+)\s*(.*?)\s*\);/) {printf($fmt,$tbl,$1,$2)}
>           elsif (/^\s*$/) {$in=0}
>          ' name temp
tbla        cola1       VARCHAR2(10)
tbla        cola2       VARCHAR2(11)
tblc        colc1       VARCHAR2(14)
tblc        colc2       VARCHAR2(15)
$
$
$

tyler_durden

Seems thatr there is a missing line in your script :

Code:

   next ;                               #   Proceed next record
}

/^ADD/,/;[[:space:]]*$/ {               # Select ADD records (from ADD to

Jean-Pierre.

Thanks alot AIGLES.its working now.

Hi aigles

Thanks for the help.I need one more favor.I have modified the code according to my need but now need to add one more condition.while we are searching for line having "add"can we do somthing to first search for line containig "add constraint" and print constraint name in the output of table and column names.Then search for line that contains only "ADD" not the " ADD CONSTRAINT" and print the output.

Please post an example of the input file containing ADD CONSTRAINT.

Jean-Pierre.

I need to do all the modification in previous code.

Here firstly i need to check whether
the table exits or not.If exists then searching for line having "ADD constraint" and
need to print the constraint name.
Now if add constraintnot does not exist then need to check for "ADD ( column name" as
we had previously done.Input for "ADD CONSTRAINT" is given below:-


ALTER TABLE PREMIUM_MSGS_SDB ADD CONSTRAINT PK_PREMIUM_MSGS_SDB PRIMARY KEY (MSG_TYPE,COUNTRY_CODE,LEG_VEH);(CODE/)

One more thing in the coding part we are working with a defined format of "alter table"
that is like below:

ALTER TABLE tbla
ADD ( cola1 VARCHAR2(11),
cola2 VARCHAR2(12) );(CODE/)

I want to add some command in my code so that the script will work on all formats.
Is it possible?
script which i needto modify:

#!/bin/sh
#read.sh
sed -n '/ALTER/,/;/p' new.txt > data.txt
sed '/;/ a\
' data.txt > data.ex
/usr/xpg4/bin/awk '
NR==FNR {
tbl[$1]++;
next;
}
/ALTER TABLE/ {
tbl_name = $3;
if (tbl_name in tbl)
getcols = 1
else getcols = 0;
next;
}

getcols != 1 {
printf "%-12s\t %-12s\t %-12s\t %s\t\t %s\n" , tbl_name , "N/A" , "N/A" , "N/A" , "NIL";
next ;
}
/^ADD/,/;[[:space:]]*$/ {
f = ($1 ~ /^ADD/ ? 3 : 1);
sub(/).*/, &quot", $(f+1));
printf "%-12s\t %-12s\t %-12s\t %s\t %s\n" , tbl_name, $f, $(f+1) , "NOT NULL" ,"Y";

}

' table data.ex > result
echo "
1i
"`grep "DDCF No." new.txt`\n"

TABLE_NAME\t\t COLUMN_NAME\t DATA_TYPE\t VALUE\t\t STATUS\n
.
wq
" | ex -s result
uniq result > result1
cat result1(CODE/)

---------- Post updated at 12:15 AM ---------- Previous update was at 12:02 AM ----------

Sorry to all because my post is not in good format but i am helpless i am trying to edit it but its not working and also not wrapingthe codes

I am not sure to understand the required output.
Can you post the output for the 2 following sample data files:

Input file "name":

Code:

tbla
tblc

Input file "temp":

Code:

ALTER TABLE tbla
ADD  ( cola1  VARCHAR2(11),
       cola2  VARCHAR2(12)  );

ALTER TABLE tbla  ADD CONSTRAINT PK_tbla PRIMARY KEY (cola1);

ALTER TABLE tblb
ADD  ( colb1  VARCHAR2(21),
       colb2  VARCHAR2(22)  );

ALTER TABLE tblb ADD CONSTRAINT PK_tblb PRIMARY KEY (colb1,colb2);
ALTER TABLE tblc ADD CONSTRAINT PK_tblc PRIMARY KEY (colc0);

ALTER TABLE tblc
ADD  ( colc1  VARCHAR2(31),
       colc2  VARCHAR2(32),
       colc3  VARCHAR2(32)  );

ALTER TABLE tbld ADD CONSTRAINT PK_tbld PRIMARY KEY (cold0,cold2,cold3);

Jean-Pierre.