Using a variable from pl/sql block into shell script


  I have a shell script like this.


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

lc_dump_count = wc -l financials.csv

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

if (lc_data_count = lc_dump_count)
mailx -s "Counts are good `date`" < $LOGFILE
mailx -s "Counts are not good `date`" < $LOGFILE

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.

Who is Participating?
tfewsterConnect With a Mentor Commented:
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}'
lc_dump_count=`cat financials.csv | wc-l`

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]
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 " ' " )
Duh - (*slaps tfewster*)

if [ $lc_data_count -eq $lc_dump_count ]

I guess you can delete the other question ;-)
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

sumanth_oraAuthor Commented:

    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;

   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.....
sumanth_oraAuthor Commented:
And this works fine too:


mailx -s "Hi" < $LOGFILE

    this gives me a valid email.
sumanth_oraAuthor Commented:
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....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.