Shell script using loop

alisha · July 8, 2010, 12:50pm

Hi everyone,

I have n number of data in my file "temp" in following order.In each line table_name and
column_name are different.input data is in same format each query in three lines.

ALTER TABLE table_name
ADD  ( column_name1         VARCHAR2(10),
       column_name2  VARCHAR2(70)  );

ALTER TABLE table_name
ADD  ( column_name1         VARCHAR2(10),
       column_name2  VARCHAR2(70)  );

ALTER TABLE table_name
ADD  ( column_name1         VARCHAR2(10),
       column_name2  VARCHAR2(70)  );

I need to pick the table name from each line one by one and check that
this name exists in my another file "name" or not.
If the table name exists in the file "name" then i need to pick the column1_name,
column2_name..... and their data type which are after ADD.
In this way need to do above action to each data in file and stor them in a file
in the following format:

TABLE_NAME   COLUMN_NAME  DATATYPE

i have used the code

while read folder
folder=`awk '/ALTER/ { print $3; } ' data.txt`
do
  cd $folder
  echo "in" > test
  cd ..
  i=$[$i+1]
done < data.txt

but for this i m getting only one table_name with error.

can anybody help me with the script.I want my script in SH
Please help me with above asap.

aigles · July 8, 2010, 3:42pm

Something like that ?

awk '
NR==FNR { tbl[$1]++ }
/ALTER TABLE/ {
   tbl_name = $3;
   getcols = (tbl_name in tbl);
   next;
}
! getcols { next }
/^ADD/,/;[[:space:]]*$/ {
   f = ($1 ~ /^ADD/ ? 3 : 1);
   printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);
}

' alisha.nam alisha.tmp

"temp" file (alisha.tmp):

awk '
NR==FNR { tbl[$1]++ }
/ALTER TABLE/ {
   tbl_name = $3;
   getcols = (tbl_name in tbl);
   next;
}
! getcols { next }
/^ADD/,/;[[:space:]]*$/ {
   f = ($1 ~ /^ADD/ ? 3 : 1);
   printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);
}

' alisha.nam alisha.tmp

"name" file (alisha.nam):

tbla
tblc

Output:

tbla         cola1        VARCHAR2(11),
tbla         cola2        VARCHAR2(12)
tblc         colc1        VARCHAR2(31),
tblc         colc2        VARCHAR2(32)
tblc         colc3        VARCHAR2(33)

Jean-Pierre.

alisha · July 9, 2010, 3:21am

hi Aigles,
thanks for your reply but i am getting errot while executing your code.please help me with that.

awk '
NR==FNR { tbl[$1]++ }
/ALTER TABLE/ {
   tbl_name = $3;
   getcols = (tbl_name in tbl);
   next;
}
! getcols { next }
/^ADD/,/;[[:space:]]*$/ {
   f = ($1 ~ /^ADD/ ? 3 : 1);
   printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);
}
 
' table.name data.txt

output:

awk: syntax error near line 5
awk: illegal statement near line 5
awk: syntax error near line 8
awk: bailing out near line 8

---------- Post updated at 02:21 AM ---------- Previous update was at 01:47 AM ----------

hi
i have searched for the problem.I hink

code

getcols = (tbl_name in tbl);

is troubling.
can you explain me this line.

clx · July 9, 2010, 3:40am

use nawk or /usr/xpg4/bin/awk.

gaithrit · July 9, 2010, 4:48am

I think the problem is due to the below line:

 
printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);

This line should be:

 
printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);

I tried this and it is working. Hope this helps.

alisha · July 9, 2010, 7:07am

hi its not working with nawk

---------- Post updated at 04:11 AM ---------- Previous update was at 04:01 AM ----------

hi Gathrit

I have tried it but still the same problem

awk '
NR==FNR { tbl[$1]++ }
/ALTER TABLE/ {
   tbl_name = $3;
   getcols = (tbl_name in tbl);
   next;
}
! getcols { next }
/^ADD/,/;[[:space:]]*$/ {
   f = ($1 ~ /^ADD/ ? 3 : 1);
   printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1);
}


' table.name data.txt

o/p

awk: syntax error near line 5
awk: illegal statement near line 5
awk: syntax error near line 8
awk: bailing out near line 8

i think the problem is in following codes.

1) getcols = (tbl_name in tbl);
2)! getcols { next }

can anyone help me with this.

---------- Post updated at 06:07 AM ---------- Previous update was at 04:11 AM ----------

hi Aigles,
Will you please give me the description of the code that you had sent me.

aigles · July 9, 2010, 7:59am

Try :

   getcols = (tbl_name in tbl ? 1 : 0);

Jean-Pierre.

alisha · July 9, 2010, 8:09am

Hi Aigles,
Thanks for the reply.
still giving the same error.

Please help me with this.

aigles · July 9, 2010, 8:29am

Have you tried to use /usr/xpg4/bin/awk or gawk ?

A commented and revised version of the script :

awk '

#
# Input file #2
#

NR==FNR {                               # Select records from input file #1
   tbl[$1]++;                           #   Memorize table in in array tbl
   next;                                #   Proceed next record
}

#
# Input file #2
#

/ALTER TABLE/ {                         # Select ALTER TABLE records
   tbl_name = $3;                       #   Table name
   getcols = (tbl_name in tbl ? 1 : 0); #   Set getcols flag if table
                                        #   + found in array tbl
   next;                                #   Proceed next record
}

! getcols {                             # Select record if getcols flag not
                                        # + set to 1
   next ;                               #   Proceed next record
}

/^ADD/,/;[[:space:]]*$/ {               # Select ADD records (from ADD to
                                        # + record endig with ";"
   f = ($1 ~ /^ADD/ ? 3 : 1);           #   IF ADD record, column name is field 3
                                        #   + otherwise field 1;
                                        #   + column format is field f+1
   sub(/).*/, ")", $(f+1));             #   Remove chars after ) ending column def
   printf "%-12s %-12s %s\n", tbl_name, $f, $(f+1); #  Output table name,
                                                    #  + column name and format
}

' alisha.nam alisha.tmp

"name" file (alisha.nam):

tbla
tblc

"temp" file (alisha.tmp) :

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

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

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

Output:

tbla         cola1        VARCHAR2(11)
tbla         cola2        VARCHAR2(12)
tblc         colc1        VARCHAR2(31)
tblc         colc2        VARCHAR2(32)
tblc         colc3        VARCHAR2(32)

Jean-Pierre.

alisha · July 9, 2010, 8:39am

No i have not tried use /usr/xpg4/bin/awk or gawk till now.
i don't no how to use these applications.

aigles · July 9, 2010, 8:46am

In the first statement, replace awk with nawk, gawk or /usr/xpg4/bin/awk :

/usr/xpg4/bin/awk '

#
# Input file #2
#

NR==FNR {                               # Select records from input file #1

Jean-Pierre.

alisha · July 9, 2010, 8:46am

hi Aigles,
sorry for troubling you.
but still the code is bailing out near line containing "getcols".

aigles · July 9, 2010, 8:53am

Try to replace

   getcols = (tbl_name in tbl ? 1 : 0); #   Set getcols flag if table
                                        #   + found in array tbl
   next;                                #   Proceed next record
}

! getcols {                             # Select record if getcols flag not
                                        # + set to 1

with

   if (tbl_name in tbl)                 #   if table found in array tbl
        getcols = 1                     #        set hetcols flag to 1
   else getcols = 0;                    #   else set getcols flag to 0
   next;                                #   Proceed next record
}

getcols != 1 {                          # Select record if getcols flag not
                                        # + set to 1

Jean-Pierre.

alisha · July 9, 2010, 9:00am

hi
i have used /usr/xpg4/bin/awk and nawk
in both cases getting error.

---------- Post updated at 08:00 AM ---------- Previous update was at 07:53 AM ----------

hi i have used your code starting with /usr/xpg4/bin/awk its giving me syntax error near lines.

/usr/xpg4/bin/awk: file "[command line]": line 16: syntax error  Context is:
>>>
>>>
>>>     next ;
>>>     /^ADD/, <<<

durden_tyler · July 9, 2010, 9:19am

$
$
$ # 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

aigles · July 9, 2010, 9:44am

alisha:

hi
i have used /usr/xpg4/bin/awk and nawk
in both cases getting error.

---------- Post updated at 08:00 AM ---------- Previous update was at 07:53 AM ----------

hi i have used your code starting with /usr/xpg4/bin/awk its giving me syntax error near lines.
/usr/xpg4/bin/awk: file "[command line]": line 16: syntax error  Context is:
>>>
>>>
>>>     next ;
>>>     /^ADD/, <<<

Seems thatr there is a missing line in your script :

   next ;                               #   Proceed next record
}

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

Jean-Pierre.

alisha · July 12, 2010, 2:14am

Thanks alot AIGLES.its working now.

alisha · July 16, 2010, 3:03am

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.

aigles · July 16, 2010, 1:53pm

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

Jean-Pierre.

alisha · July 19, 2010, 1:15am

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(/)./, ")", $(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