Solved

Using a variable from pl/sql block into shell script

Posted on 2004-08-25
6
466 Views
Last Modified: 2012-05-05
Hi,

  I have a shell script like this.

$LOGFILE=logfile.log


awk -F"," 'BEGIN{cnt = 0; OFS=","}
IF FILENAME == "fin.csv"{
++cnt
test_field[cnt] = $1
field[cnt] = $1","$2","$3","
next
}END{
for (a=1;a<=cnt;++a){
if ( test_field[a] == "LOCATION" )    ####   DROP HEADERS IF ANY
   continue
 print field[a] > "financials.csv"
'}} fin.csv


lc_dump_count = wc -l financials.csv

1>>$LOGFILE sqlplus -s crebatch/crebatch@credvl <<SQLRUN
begin
       lc_data_count number;
       select count(1) into lc_data_count from XYZ;
end;
SQLRUN

if (lc_data_count = lc_dump_count)
then
mailx -s "Counts are good `date`" abc@yahoo.com < $LOGFILE
else
mailx -s "Counts are not good `date`" abc@yahoo.com < $LOGFILE
fi



1)  I want to send the count of table XYZ into a variable called 'lc_data_count' and compare it with the variable called 'lc_dump_count'. Of course this code isn't working because lc_data_count is a variable with the pl/sql block. What will work then?

2)    Will this code work?

    lc_dump_count = wc -l financials.csv

3)    Any suggestions on how to get the whole line into a variable instead of doing
  field[cnt] = $1","$2","$3","

    I have like atleast 25 values.

0
Comment
Question by:sumanth_ora
  • 3
  • 3
6 Comments
 
LVL 21

Accepted Solution

by:
tfewster earned 500 total points
ID: 11896686
2)  wc will return the filename as well as the line count; To get around that:
lc_dump_count=`wc -l financials.csv |awk '{print $1}'
or
lc_dump_count=`cat financials.csv | wc-l`


3)
awk syntax to build up a string:

field[cnt] = $1
for (x=2; x<=NF; x++) {print x; print $x; field[cnt]=field[cnt]","$x}
print field[cnt]
0
 
LVL 21

Expert Comment

by:tfewster
ID: 11897305
Thank you, but you could have left the question open until your main point about capturing output from SQL was answered and then split the points...I've not touched SQL for years, but is it possible to send the output of the "select count(1)"  to a flat file, which you could then read with e.g.:
lc_dump_count=`cat mycountfile`

Or grep the results of the count out of the LOGFILE?


I just noticed a couple of minor things:
LOGFILE=logfile.log   (Not "$LOGFILE")

> if (lc_data_count = lc_dump_count)
I think this should be:
if [ lc_data_count -eq lc_dump_count ]

'}} fin.csv
should be
}}' fin.csv                    (position of " ' " )
0
 
LVL 21

Expert Comment

by:tfewster
ID: 11897324
Duh - (*slaps tfewster*)

if [ $lc_data_count -eq $lc_dump_count ]


I guess you can delete the other question ;-)
0
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!

 

Author Comment

by:sumanth_ora
ID: 11903519
Hi,

    I got my answer for that first question before you answered for it. This is how I did it.

lc_data_count=`sqlplus -s crebatch/crebatch@credvl <<SQLRUN
                 set head off
                 select count(1) from fin_data;
                 exit;
                 SQLRUN`


   This worked perfectly fine.  

As for the variable equality check, I used:

if (($lc_data_count==$lc_dump_count))


    This worked fine too.


     And I don't know why, but
'}} fin.csv

  worked.   But I will try with
}}' fin.csv

  and see.....
0
 

Author Comment

by:sumanth_ora
ID: 11905561
And this works fine too:

LOGFILE=fin_master.log

mailx -s "Hi" abc@yahoo.com < $LOGFILE

    this gives me a valid email.
0
 

Author Comment

by:sumanth_ora
ID: 11906897
And one more thing....

   This 'awk' script you gave is taking longer to parse when compared to what I had. I am guessing this is because of the extra FOR loop in the script.

   Just to let you know....
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
pauing printer deamon in AIX 10 66
KSH What's the best way to pass multiple variables (some may be blank) to a function? 5 62
ftpcommand 2 74
How to clone solaris 10 machine 33 173
Hello fellow BSD lovers, I've created a patch process for patching openjdk6 for BSD (FreeBSD specifically), although I tried to keep all BSD versions in mind when creating my patch. Welcome to OpenJDK6 on BSD First let me start with a little …
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

733 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