Shell Programming and Scripting

# cat a
ALTER TABLE abc ADD empid char
# awk  '{if($1=="ALTER" && $4="ADD"){print "var"++i"="$3"\n" "var"++i"="$5"\n" "var"++i"="$6}}' a
var1=abc
var2=empid
var3=char
#

awk  '{if( $1=="ALTER" && $4="ADD" )  print $3, $5, $6 }' file | read table column type

awk  '{ if( $1=="ALTER" && $4="ADD" )  print $3, $5, $6 }' file >/tmp/x.$$
read table column type </tmp/.x$$ 
rm /tmp/x.$$

var1=$(awk -F "[<>]" '{print $2}' file);var2=$(awk -F "[<>]" '{print $4}' file);var3=$(awk -F "[<>]" '{print $6}' file)

awk  '$1=="ALTER" && $4="ADD" { print $3, $5, $6 }' infile |  
{ 
read table column type 
echo $table
echo $column
echo etcetera...
}

read table column type < <( awk  '$1=="ALTER" && $4="ADD" { print $3, $5, $6 }' infile )

#!/bin/ksh

while read line
do
	echo $line | awk  '{
		if($1=="ALTER" && $2=="TABLE"){
			if($4=="ADD"){
				sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s and column_type = %s", $3, $5, $6);
				print sql_statement
			}
			if($4=="MODIFY"){
				sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s and column_type = %s", $3, $5, $6);
				print sql_statement
			}
			if($4=="RENAME" && $5=="TO" ){
				sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s", $6);
				print sql_statement
			}
			if($4=="RENAME" && $5=="COLUMN"){
				sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s", $3, $8);
				print sql_statement
			}
                        if($4=="DROP" && $5=="COLUMN"){
				sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s", $3, $6);
				print sql_statement
			}
		}
	}' | read sql
	echo $sql
done < file

1. ALTER TABLE emp ADD status VARCHAR2(20);
2. ALTER TABLE dept MODIFY dept_name varchar2(20) NOT NULL;
3. ALTER TABLE temp RENAME TO temp01;
4. ALTER TABLE dept RENAME COLUMN status TO status_new;
5. ALTER TABLE emp DROP COLUMN status;

6. ALTER TABLE customers ADD (contact_name	varchar2(50),last_contacted date );
7. ALTER TABLE customers MODIFY ( customer_name varchar2(50) not null, state varchar2(2));