Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell script using loop

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script using loop
# 22  
Old 07-19-2010
Hi AIGLES
Thanks for your response.
I had made a little change in input too.I am posting here both input and output.



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);
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:

Code:
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

Last edited by alisha; 07-19-2010 at 12:47 PM..
# 23  
Old 07-20-2010
Try and adapt this new version of my script :
Code:
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):
Code:
tbla
tblc

Input file "temp" (alisha2.tmp):
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);
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:
Code:
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.
This User Gave Thanks to aigles For This Post:
alisha
# 24  
Old 07-20-2010
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script use two variable in for loop

I want to create a shell script to add a user and modify its comment field mentioned in a file. 1. File value:- username comment field value xyz123 xyztesting abc123 abctesting def123 deftesting 2. i am using below loop to create user... (2 Replies)
Discussion started by: Anil
2 Replies

2. Shell Programming and Scripting

Speed up the loop in shell script

Hi I have written a shell script which will test 300 to 500 IPs to find which are pinging and which are not pinging. the script which give output as 10.x.x.x is pining 10.x.x.x. is not pining - - - 10.x.x.x is pining like above. But, this script is taking... (6 Replies)
Discussion started by: kumar85shiv
6 Replies

3. Shell Programming and Scripting

Loop in shell script

Hi Friends, I have a file. the content is as below: file1: /A/B/C/abc.txt 2013-07-28 13:50:00,2013-07-31 01:00:00,5,710 /A/B/C/xyz.txt 2011-09-21 18:30:00,2013-07-30 06:15:00,15,65135 2009-11-09 18:00:00,2011-09-02 09:00:00,5,12345 2013-07-28 13:50:00,2013-07-31 01:00:00,5,710 ... (2 Replies)
Discussion started by: vsachan
2 Replies

4. Shell Programming and Scripting

Help with the For loop shell script

Hi, I have multiple files in a directory. Each file will have a header.I have to check if any of the files has 0 rows other than the header then I have to delete the files. Here “ Empty file” in my case means a file has header information but no data. I have to delete such files. If the file... (2 Replies)
Discussion started by: ganesnar
2 Replies

5. Shell Programming and Scripting

Avoiding For Loop in Shell Script

I am looking to a solution to the following problem. I have a very large file that looks something like this: Each group of three numbers on each line are three probabilities that sum to one. I want to output the maximum for each group of three. So desired output would be: or... (6 Replies)
Discussion started by: hydrabane
6 Replies

6. Shell Programming and Scripting

Help with IF statement with loop Shell Script

Hello I am very new to shell and I bought some books and trying to learn it. I started trying to write a script that will take a number and count it down to 1 with commas in between. This number can only be one argument. If lower than one or higher than one argument it sends an error message. ... (4 Replies)
Discussion started by: zero3ree
4 Replies

7. Shell Programming and Scripting

while loop problem in c shell script

Hi all, i write a script c shell set i = 1 while ( $i <= $#array ) echo "$array" @ i++ end i want to set it to i = i +2 in that statement . Can anybody help me? ---------- Post updated at 02:46 PM ---------- Previous update was at 02:35 PM ---------- anybody not how to solve it??? (2 Replies)
Discussion started by: proghack
2 Replies

8. Shell Programming and Scripting

Loop in shell script

Dear experts, i am quite new to shell script please any one can help me in this regard i would like write a script which takes input in the form >./Test.sh a,10,b,20,c,30... in this way i can give input in any number which is not constant in the end through loop i want to... (3 Replies)
Discussion started by: vin_pll
3 Replies

9. Shell Programming and Scripting

Help with loop in a shell script

I just want to write a little script, that reads the lines from a file, echos somthing in a new tmp.file and then do some commands whith the tmp.files. while read -r line do echo "TEST=" > tmp.$$ echo "$line" >> tmp.$$ any_command < tmp.$$ done < $INPUTFILE But I think I have to... (2 Replies)
Discussion started by: elifchen
2 Replies

10. Shell Programming and Scripting

If then else loop in Shell script

Hi Following is the code . When I give input as Bangalore,its dospalying Welcome to Hitech City. But say , if I select Delhi or US, its not displaying the corresponding message. Its still says Welcome to Hitech City. Seems that it not entering in the elif part. Please suggest. #!... (4 Replies)
Discussion started by: pankajkrmishra
4 Replies
Login or Register to Ask a Question