Shell script using loop

Shell Programming and Scripting
21

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

Input file "name":

tbla
tblc

Input file "temp":

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.

22

Hi AIGLES
Thanks for your response.
I had made a little change in input too.I am posting here both input and output.

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);
ALTER TABLE tblc ADD col1 CHAR(3) NULL;
ALTER TABLE tblb ADD col1 CHAR(3) NULL;
ALTER TABLE tbla ADD col1 CHAR(3) NULL;
ALTER TABLE tbld ADD col1 CHAR(3) NULL;
ALTER TABLE tbla ADD col1 CHAR(3) NULL;

Desired Output:

TABLES
TABLE_NAME	COLUMN_NAME	DATATYPE	

tbla		cola1		VARCHAR2(11)	NOT NULL
		cola2		VARCHAR2(12)	NOT NULL
tblc		colc1  		VARCHAR2(31)	NOT NULL
       		colc2  		VARCHAR2(32)	NOT NULL
       		colc3  		VARCHAR2(32)	NOT NULL
tblc		col1		CHAR(3) 	NULL
tbla		col1 		CHAR(3)		NULL
tbla 		col1		CHAR(3)		NULL

NO IMPACT
TABLE_NAME

tbla		CONSTRAINT 
tblb
tblb		CONSTRAINT
tblc		CONSTRAINT
tbld		CONSTRAINT
23

Try and adapt this new version of my script :

awk '
BEGIN {
  fmtI   = "%-12s %-12s %-16s %s\n";
  fmtNoI = "%-12s %s\n";

  print  "TABLES"
  printf fmtI, "TABLE_NAME", "COLUMN_NAME", "DATATYPE",  "";
}

#
# Input file #1
#

NR==FNR {
   tbl[$1]=0;
   next;
}

#
# Input file #2
#

/ALTER TABLE/ {
   tbl_name = $3;
   if (tbl_name in tbl) {
      getcols = 1
   } else {
      getcols = 0;
      tblNoI[tbl_name] = "";
   }
}

getcols != 1 {
   next;
}

/ADD CONSTRAINT / {
   sub(/.*ADD CONSTRAINT[[:space:]]+/, "");
   tblNoI[tbl_name] = tblNoI[tbl_name] "CONSTRAINT " $1;
   next;
}

/ADD /,/;[[:space:]]*$/ {
   sub(/.*ADD[[:space:]]+\(?[[:space:]]*/, "");
   sub(/[,;]*$/, "", $2); sub (/))/, ")", $2);
   sub(/[,;)]*$/, "", $3);
   name = (tbl_name == prv_tbl_name ?  "" :  tbl_name);
   prv_tbl_name = tbl_name;
   null = ($3 ? $3 : "NOT NULL")
   printf fmtI, name, $1, $2, null;
}

END {
   print "\nNO IMPACT";
   printf fmtNoI, "TABLE_NAME", ""
   for (t in tblNoI) printf fmtNoI, t, tblNoI[t];
   print "";
}
' alisha.nam alisha2.tmp

Input file "name" (alisha.nam):

tbla
tblc

Input file "temp" (alisha2.tmp):

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);
ALTER TABLE tblc ADD col1 CHAR(3) NULL;
ALTER TABLE tblb ADD col1 CHAR(3) NULL;
ALTER TABLE tbla ADD col1 CHAR(3) NULL;
ALTER TABLE tbld ADD col1 CHAR(3) NULL;
ALTER TABLE tbla ADD col1 CHAR(3) NULL;

output:

TABLES
TABLE_NAME   COLUMN_NAME  DATATYPE
tbla         cola1        VARCHAR2(11)     NOT NULL
             cola2        VARCHAR2(12)     NOT NULL
tblc         colc1        VARCHAR2(31)     NOT NULL
             colc2        VARCHAR2(32)     NOT NULL
             colc3        VARCHAR2(32)     NOT NULL
             col1         CHAR(3)          NULL
tbla         col1         CHAR(3)          NULL
             col1         CHAR(3)          NULL

NO IMPACT
TABLE_NAME
tbld
tbla         CONSTRAINT PK_tbla
tblb
tblc         CONSTRAINT PK_tblc

Jean-Pierre.

1 Like
24

Thanks a lot .Itz working.You are real genius.I have leart a lot from you.
If you don't mind will you please send me the discription of the code.