Solved

Using a variable from pl/sql block into shell script

Posted on 2004-08-25
6
464 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 20

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 20

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 20

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AIX  NFS  question 1 138
how to check for shares on aix 1 80
MarkLogic 1 79
How to clone solaris 10 machine 33 131
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…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
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…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

778 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