Solved

Using a variable from pl/sql block into shell script

Posted on 2004-08-25
6
462 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CPU monthly average? 12 90
llcommand 6 76
phantom space used up on RHEL?  (du shows no space used) 1 57
unix in java example 9 40
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…
Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
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…
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.

707 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

16 Experts available now in Live!

Get 1:1 Help Now