Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Need help modifying ksh script

I have the following script to read database names from a file in a loop, login to the sqlplus, run a select statement against the database and write the results to a file.


grep "^[a-zA-Z]" $FILE1 | while read LINE
do
 OUTFILE=/u01/test/logs/output_`date +"%Y%m%d%H%M"`.log
sqlplus -s "/ as sysdba" <<! >> $OUTFILE
        COLUMN field1 format 999999.90
        COLUMN field2 format a20
        COLUMN field3 format 999999.90
       
                set lines 132
SELECT field1, field2, field3 FROM TABLE1;

                 exit;

!

done

The output in my output file looks like this:

field1      field2      field3
1      test      34
2      test2      56


field1      field2      field3
5      test4      6778
6      test5      332


field1      field2      field3
8      test5      346
9      test6      123


Is there any way I could modify my script to have only one header line with column names and data from all the databases unser it?

Something like:

field1      field2      field3
1      test      34
2      test2      56
5      test4      6778
6      test5      332
8      test5      346
9      test6      123
0
YZlat
Asked:
YZlat
  • 13
  • 9
  • 3
  • +1
4 Solutions
 
slightwv (䄆 Netminder) Commented:
The only ways I can thing of is manually generate the header outside of the sqlplus calls and turn off the sqlplus headers but this would be pretty difficult to get everything lined up.

The other way I can think of is have the do loop generate a series of UNION ALL select statements and use that to call sqlplus once using the generated select.
0
 
woolmilkporcCommented:
You could apply some postprocessing to $OUTFILE:

awk '!/^$/ {if ( $0~"^field1" && NR==1) print $0; else if ($0~"^field1" && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean
0
 
YZlatAuthor Commented:
Thanks! I will test it out
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
YZlatAuthor Commented:
woolmilkporc, in example here I used field1, field2 as column titles but in reality each one consists of several words. For example "this is fiest column" "this is second". How do I need to modify your code to accomodate this change?
0
 
woolmilkporcCommented:
You need to specify a string which starts in the first column and is of sufficient length to make it unique.
We can place the definiton of this string outside of the awk statement to makeit it better readable/customizeable.

The code for the example in your last post could look like this:

awk -v S="^This is first column" '!/^$/ {if ($0~S && NR==1) print; else if ($0~S && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window

All you have to customize is the unique string value in the -v S="^unique string" part.

If the string in question does not start in column 1 omit the caret "^":

... -v S="unique string" ...
0
 
YZlatAuthor Commented:
I have changed my sql, so now I need to make sure the second column is unique and the column name is TITLE. I tried the code below but it didn't chaneg anything in my fiel at all. What am I doing wrong?

awk '!/^$/ {if ( $1~"^TITLE" && NR==1) print $1; else if ($1~"^TITLE" && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window

0
 
woolmilkporcCommented:
If it's column 2, why do you use "$1"? That's the first column! "$0" is the whole input line!

Please note that awk's columns are by default space delimited, so "TITLE" must be the second word in the header line to make the code work with $2.
Best post an example!

And you don't want to print only "$1" (the first column), do you?
So use "print $0" (or just "print", because $0 (the whole line) is the default).

The following code will work regardless of where in the header line the word TITLE would appear - but it must not appear in a data row!

awk -v S="TITLE" '!/^$/ {if ($0~S && NR==1) print; else if ($0~S && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window

0
 
YZlatAuthor Commented:
so $0 is the whole input line, then the first column is $1 and second column is $2?

I have tried:
 
awk '!/^$/ {if ( $0~"^TITLE" && NR==1) print $0; else if ($0~"^TITLE" && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window


and that returned

field1  TITLE  field3
------- -------- --------
1      test      34
field1  TITLE  field3
------- -------- --------
2      test2      56
field1  TITLE  field3
------- -------- --------
5      test4      6778
field1  TITLE  field3
------- -------- --------
6      test5      332
field1  TITLE  field3
------- -------- --------
8      test5      346
field1  TITLE  field3
------- -------- --------
9      test6      123


Then I tried
 
awk '!/^$/ {if ( $2~"^TITLE" && NR==1) print $2; else if ($2~"^TITLE" && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window


and that returned

---- -------- --------
1      test      34
---- -------- --------
2      test2      56
---- -------- --------
5      test4      6778
---- -------- --------
6      test5      332
---- -------- --------
8      test5      346
---- -------- --------
9      test6      123


Neither is the desired result.

I also tried
 
awk '!/^$/ {if ( $2~"^TITLE" && NR==1) print $0; else if ($2~"^TITLE" && NR>1) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window


But that returned the same as the second result set here
0
 
woolmilkporcCommented:
Well, those dashes were not part of the initial question.

Remember what you posted?
The output in my output file looks like this:

field1      field2      field3
1      test      34
2      test2      56

field1      field2      field3
5      test4      6778
6      test5      332
Anyway, here you go:
 
awk '!/^$/ {if ( ($2~"^TITLE" && NR==1 ) || ( $2~"^--" && NR==2) ) print ; else if ( ($2~"^TITLE" && NR>1 ) || ( $2~"^--" && NR>2) ) next; else print}' $OUTFILE > ${OUTFILE}.clean 

Open in new window

>> so $0 is the whole input line, then the first column is $1 and second column is $2? <<

Correct.
0
 
YZlatAuthor Commented:
I guess I underestimated the dashes.

The last code you posted returns only the last line of data
0
 
woolmilkporcCommented:
I think we can't continue without a real life example of your output file.
0
 
YZlatAuthor Commented:
by the way, when I replaced $2 with $0 I got all the lines but without dashes in between and with column names over every line
0
 
YZlatAuthor Commented:
Here are the output file contents:

SERVER               TITLE   FIRST VALUE SECOND VALUE  BACKUP SECOND VALUE
-------------------- --------- -------------------- ------------------ --------------------
test          TITLE1                    11.165              9.212                1.952      


SERVER               TITLE   FIRST VALUE SECOND VALUE  BACKUP SECOND VALUE
-------------------- --------- -------------------- ------------------ --------------------
test2          TITLE2                  964.578            884.578               79.997      


SERVER               TITLE   FIRST VALUE SECOND VALUE  BACKUP SECOND VALUE
-------------------- --------- -------------------- ------------------ --------------------  
test3          TITLE3                  171.753            168.253                3.499
0
 
YZlatAuthor Commented:
btw, coudl you tell me what does this piece do, that immediately follows awk command?

!/^$/
0
 
woolmilkporcCommented:
Last question first: It filters out empty lines.

And I told you that we need a string to uniquely identify the header, which means that of course it may not appear in the report detail data (which it does in your case)!

Why don't we use the first field "SERVER" which appears only in the header?

Alternatively we could test for "TITLE" without any trailing character (1,2,3 in your case) but please understand that I'm a bit wary of your samples! Is it really "TITLE" in the header and always "TITLEx" in the report?
If so, it's simple:
awk '!/^$/ {if ( ($2~"^TITLE$" && NR==1 ) || ( $2~"^--" && NR==2) ) print ; else if ( ($2~"^TITLE$" && NR>1 ) || ( $2~"^--" && NR>2) ) next; else print}' $OUTPUTFILE > ${OUTPUTFILE}.clean

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Personally, I would look at what I suggested in the first post:
Manually add the headers to the output file and turn them off in the sqlplus output.

Then all you need to worry about is blank lines and that is easy.

I realize your actual ksh script has changed from what you posted in the original question but I'll use it as an example:

OUTFILE=/u01/test/logs/output_`date +"%Y%m%d%H%M"`.log

echo "SERVER               TITLE     FIRST VALUE          SECOND VALUE       BACKUP SECOND VALUE" >>$OUTFILE
echo "-------------------- --------- -------------------- ------------------ --------------------" >>$OUTFILE

grep "^[a-zA-Z]" $FILE1 | while read LINE
do
sqlplus -s "/ as sysdba" <<! >> $OUTFILE
        COLUMN field1 format 999999.90
        COLUMN field2 format a20
        COLUMN field3 format 999999.90
       
                set lines 132
		set pages 0
		set feedback off
SELECT field1, field2, field3 FROM TABLE1;

                 exit;

!

done

# remove blank lines
grep -v "^$" $OUTFILE >> $OUTFILE

Open in new window

0
 
YZlatAuthor Commented:
No, the titles are not really title1, title2, and title3, I just don't want to post the actual titles. So using TITLE to uniquely identify the row would be fine
0
 
woolmilkporcCommented:
I can't continue this way. Do you really want me to guess what your data might look like?
0
 
slightwv (䄆 Netminder) Commented:
>>No, the titles are not really title1, title2, and title3

To add to what woolmilkporc posted:
If you post sample data, you get sample scripts.  With sample scripts, it is then up to you to learn what the script is doing then make the necessary changes on your own to get it working.

If you don't know how to make the necessary changes, we cannot provide actual scripts without actual data.
0
 
magentoCommented:
Why cant you modify your output file ?

What ever your header has , it will take care.

try this

grep "^[a-zA-Z]" $FILE1 | while read LINE
do
 OUTFILE=/u01/test/logs/output_`date +"%Y%m%d%H%M"`.log
sqlplus -s "/ as sysdba" <<! >> $OUTFILE
        COLUMN field1 format 999999.90
        COLUMN field2 format a20
        COLUMN field3 format 999999.90
       
                set lines 132
SELECT field1, field2, field3 FROM TABLE1;

                 exit;

!

done
header=`head -1 $OUTPUT`
temp=/u01/test/logs/temp.log
sed -e '/--------/d' -e '/$header/d' -e '1i "<$header>"' $OUTFILE >> $temp
mv $temp $OUTPUT

Open in new window


BR,

Magento
0
 
YZlatAuthor Commented:
the data in the column TITLE could be anything, it cahnges, the only thing consistent is the column name, which is TITLE
0
 
YZlatAuthor Commented:
Finally got it to work!

Here is what worked:
awk '!/^$/ {if  ($2~"^TITLE" && NR>2 ) next; else print}' $OUTFILE > ${OUTFILE}.clean

Open in new window


Thanks to all those trying to help!
0
 
YZlatAuthor Commented:
This is what worked:


awk '!/^$/ {if  ($2~"^TITLE" && NR>2 ) next; else print}' $OUTFILE > ${OUTFILE}.clean
0
 
woolmilkporcCommented:
This leaves the dashes in the report.

Thx for the points.
0
 
YZlatAuthor Commented:
Yes, but I have no issue with the dashes, all i wanted is to only have column names listed once. I know how to remove the dashes now, but I prefer to keep them in
0
 
magentoCommented:
Have you tried my comment - 39262012 ?

Its not working as u expected?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now