# Shell script using loop

**URL:** <https://community.unix.com/t/shell-script-using-loop/269144>\
**Category:** Shell Programming and Scripting\
**Created:** [July 8, 2010, 12:50pm UTC](https://community.unix.com/t/shell-script-using-loop/269144 "2010-07-08T12:50:34Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 8, 2010, 12:50pm UTC](https://community.unix.com/t/shell-script-using-loop/269144/1 "2010-07-08T12:50:34Z")

</div>

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.

```nohighlight
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:

```nohighlight
TABLE_NAME COLUMN_NAME DATATYPE

```

i have used the code

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 8, 2010, 3:42pm UTC](https://community.unix.com/t/shell-script-using-loop/269144/2 "2010-07-08T15:42:23Z")

</div>

Something like that ?

```nohighlight
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):

```nohighlight
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):

```nohighlight
tbla
tblc

```

Output:

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

```

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 3:21am UTC](https://community.unix.com/t/shell-script-using-loop/269144/3 "2010-07-09T03:21:56Z")

</div>

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

```nohighlight
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:

```nohighlight
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

```nohighlight
getcols = (tbl_name in tbl);

```

is troubling.  
can you explain me this line.

---

<div class="post-metadata">

**Author:** ![clx](https://community.unix.com/letter_avatar/clx/32/6_22734026db12803ebb3e059622ac3534.png) [@clx](https://community.unix.com/u/clx)\
**Post date:** [July 9, 2010, 3:40am UTC](https://community.unix.com/t/shell-script-using-loop/269144/4 "2010-07-09T03:40:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gaithrit](https://community.unix.com/letter_avatar/gaithrit/32/6_22734026db12803ebb3e059622ac3534.png) [@gaithrit](https://community.unix.com/u/gaithrit)\
**Post date:** [July 9, 2010, 4:48am UTC](https://community.unix.com/t/shell-script-using-loop/269144/5 "2010-07-09T04:48:43Z")

</div>

I think the problem is due to the below line:

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

```

This line should be:

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

```

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

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 7:07am UTC](https://community.unix.com/t/shell-script-using-loop/269144/6 "2010-07-09T07:07:17Z")

</div>

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

```nohighlight
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

```nohighlight
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.

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 9, 2010, 7:59am UTC](https://community.unix.com/t/shell-script-using-loop/269144/7 "2010-07-09T07:59:26Z")

</div>

Try :

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

```

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 8:09am UTC](https://community.unix.com/t/shell-script-using-loop/269144/8 "2010-07-09T08:09:24Z")

</div>

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

Please help me with this.

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 9, 2010, 8:29am UTC](https://community.unix.com/t/shell-script-using-loop/269144/9 "2010-07-09T08:29:25Z")

</div>

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

A commented and revised version of the script :

```nohighlight
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):

```nohighlight
tbla
tblc

```

"temp" file (alisha.tmp) :

```nohighlight
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:

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

```

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 8:39am UTC](https://community.unix.com/t/shell-script-using-loop/269144/10 "2010-07-09T08:39:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 9, 2010, 8:46am UTC](https://community.unix.com/t/shell-script-using-loop/269144/11 "2010-07-09T08:46:19Z")

</div>

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

```nohighlight
/usr/xpg4/bin/awk '

#
# Input file #2
#

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

```

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 8:46am UTC](https://community.unix.com/t/shell-script-using-loop/269144/12 "2010-07-09T08:46:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 9, 2010, 8:53am UTC](https://community.unix.com/t/shell-script-using-loop/269144/13 "2010-07-09T08:53:27Z")

</div>

Try to replace

```nohighlight
   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

```nohighlight
   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.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 9, 2010, 9:00am UTC](https://community.unix.com/t/shell-script-using-loop/269144/14 "2010-07-09T09:00:44Z")

</div>

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.

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

```

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [July 9, 2010, 9:19am UTC](https://community.unix.com/t/shell-script-using-loop/269144/15 "2010-07-09T09:19:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 9, 2010, 9:44am UTC](https://community.unix.com/t/shell-script-using-loop/269144/16 "2010-07-09T09:44:09Z")

</div>

> [@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.
> 
> ```plaintext
> /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 :

```plaintext
   next ; # Proceed next record
}

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

```

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 12, 2010, 2:14am UTC](https://community.unix.com/t/shell-script-using-loop/269144/17 "2010-07-12T02:14:05Z")

</div>

Thanks alot AIGLES.its working now.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 16, 2010, 3:03am UTC](https://community.unix.com/t/shell-script-using-loop/269144/18 "2010-07-16T03:03:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![aigles](https://community.unix.com/user_avatar/community.unix.com/aigles/32/118_2.png) [@aigles](https://community.unix.com/u/aigles)\
**Post date:** [July 16, 2010, 1:53pm UTC](https://community.unix.com/t/shell-script-using-loop/269144/19 "2010-07-16T13:53:12Z")

</div>

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

Jean-Pierre.

---

<div class="post-metadata">

**Author:** ![alisha](https://community.unix.com/letter_avatar/alisha/32/6_22734026db12803ebb3e059622ac3534.png) [@alisha](https://community.unix.com/u/alisha)\
**Post date:** [July 19, 2010, 1:15am UTC](https://community.unix.com/t/shell-script-using-loop/269144/20 "2010-07-19T01:15:00Z")

</div>

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

[Next page](https://community.unix.com/t/shell-script-using-loop/269144.md?page=2)