Solved

Need help modifying ksh script

Posted on 2013-06-14
27
419 Views
Last Modified: 2013-06-23
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
Comment
Question by:YZlat
  • 13
  • 9
  • 3
  • +1
27 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 300 total points
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
Thanks! I will test it out
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 300 total points
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 300 total points
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
I guess I underestimated the dashes.

The last code you posted returns only the last line of data
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
I think we can't continue without a real life example of your output file.
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 35

Author Comment

by:YZlat
Comment Utility
btw, coudl you tell me what does this piece do, that immediately follows awk command?

!/^$/
0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 300 total points
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
I can't continue this way. Do you really want me to guess what your data might look like?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 5

Expert Comment

by:magento
Comment Utility
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
 
LVL 35

Author Comment

by:YZlat
Comment Utility
the data in the column TITLE could be anything, it cahnges, the only thing consistent is the column name, which is TITLE
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 35

Author Closing Comment

by:YZlat
Comment Utility
This is what worked:


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

Expert Comment

by:woolmilkporc
Comment Utility
This leaves the dashes in the report.

Thx for the points.
0
 
LVL 35

Author Comment

by:YZlat
Comment Utility
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
 
LVL 5

Expert Comment

by:magento
Comment Utility
Have you tried my comment - 39262012 ?

Its not working as u expected?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now