?
Solved

Using a variable from pl/sql block into shell script

Posted on 2004-08-25
6
Medium Priority
?
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 21

Accepted Solution

by:
tfewster earned 2000 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

When you do backups in the Solaris Operating System, the file system must be inactive. Otherwise, the output may be inconsistent. A file system is inactive when it's unmounted or it's write-locked by the operating system. Although the fssnap utility…
Let's say you need to move the data of a file system from one partition to another. This generally involves dismounting the file system, backing it up to tapes, and restoring it to a new partition. You may also copy the file system from one place to…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

762 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