Unix Variables in AWK script

[mjm22]

Hi

I have a requirement to do the following within a Unix script: read an input file and for certain lines get a value from the line to sum it ( after using sed to remove the quotes from the values). Then after all lines have been read I write an audit record to an oracle database by calling a stored function. Here is the working part of the script...


grep "Event:" $TO_REF_FILE | sed s/\"//g | awk '
BEGIN{eCount=0;eValue=0;tempsql="tempSQL.sql"}
{FS=","; eValue += $6; eCount++}
END {print "set serveroutput on\n" >tempsql;
print "DECLARE\n errValue NUMBER;\nBEGIN\n" >tempsql;
printf("errValue := IPGPPP.insertAuditTrailRecords(1,0,'\''&&1'\'','\''&&2'\'','\''&&3'\'','\''&&4'\'',%d,%d);\n",eCount,eValue) >>tempsql;
print "END;\n/\n" >>tempsql;
print "exit;\n" >>tempsql}'

sqlplus $DATABASE @$tempsql $TO_REF_FILE $FILEDTM $PPPNNI $PPPPID

# remove the temporary sql script
rm $tempsql

-----------

The problem I am haing is with the tempsql variable which stores the temporary sql file. Because I declare it in the AWK part of the script it is obviously not available outwith ( so the lines starting sqlplus and rm $tempsql do not currently work).

My preferred option would be to declare TEMPSQL as a unix variable and reference this within the AWK, eg:

TEMPSQL="tempSQL.sql"

grep "Event:" $TO_REF_FILE | sed s/\"//g | awk '
BEGIN{eCount=0;eValue=0}
{FS=","; eValue += $6; eCount++}
END {print "set serveroutput on\n" >$TEMPSQL;
......etc


However I can't seem to be able to get this to work either. What do I need to do within the AWK section in order to redirect the output from the Print commands to $TEMPSQL?

Thanks in advance

Mike

 

[dickiebird]

One way to pass parameters :

grep "Event:" $TO_REF_FILE | sed s/\"//g | awk -v FILEOUT="$TEMPSQL" '
BEGIN{eCount=0;eValue=0}
{FS=","; eValue += $6; eCount++}
END {print "set serveroutput on\n" >FILEOUT;

HTH

Dickie Bird (-)))

 

[Ygor]

Another method that I use...

END {
tempsql=ENVIRON["TEMPSQL"];
print "set serveroutput on\n" >tempsql;
etc

 

[mjm22]

Thanks

Had some trouble getting the -v FILEOUT="$TEMPSQL" working, but then tried nawk instead of awk and it was fine after that.

Thanks for the help.

Mike