I'm having some issues with a script I'm writing.
It's probably most chaotic, I'm no ksh guru, but the idea is to extract an ID with that query, spool it into a file, and read that file making the ID a variable. This has to be done for every row extracted by function extractData
Problem is, it doesn't spool. At all. I'm in the dark here because it has always worked for me, but not this time. It's the red/bold part of the script.
What am I doing wrong?
Thank you in advance!
#!/bin/ksh
set -x
###########
#FUNCTIONS#
###########
function extractData
{
perl -ne 'BEGIN { $/="\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*" } chomp; print if /error_field/i' $recent_log | \
awk '(match($1,/^cccardcode|^date/)) {printf "%s;",$3};(match($1,/^file_amount/)) {printf "%s\n",$3}' | tr -d " " | tr -d "\015" | \
awk -F";" '{
printf "%s;", $1
printf "%s;", $2
}
$3 ~ /[0-9]+\.00+/ {
printf "%.0f\n", $3
}
$3 ~ /[0-9]+\.[1-9]0+/ {
printf "%.1f\n", $3
}
$3 ~ /[0-9]+\.[0-9][1-9]0+/ {
printf "%.2f\n", $3
}'
}
function doCSVSQL
{
sqlplus -s user/pass@SID >>report_dettaglio_$(date +"%Y%m%d").csv << EOF
@do_csv.sql
exit;
EOF
}
##########
#PRECHECK#
##########
cd /LOGPATH
recent_log=$(ls PIHCCARD*.LOG | tail -1)
go=$(cat $recent_log | grep error_field | wc -l)
if [ $go -eq 0 ]
then
echo "Nessuno scarto trovato nei LOG. Esco."
break
else
######
#MAIN#
######
rm -f tmp_data.txt
extractData > tmp_data.txt
cat tmp_data.txt | while read line
do
cdc="'$(echo $line|awk -F";" '{print $1}')'"
emiss="'$(echo $line|awk -F";" '{print $2}')'"
importo="'$(echo $line|awk -F";" '{print $3}')'"
cdc_cut=$(echo $cdc | cut -c14-17)
sqlplus -s user/pass@SID >result.txt << EOF
set pagesize 32000
set linesize 500
set heading off
set feedback off
select /*+ PARALLEL(customer 2) PARALLEL(orderhdr 2) PARALLEL(tickler_records 2) USE_HASH(customer) */
c.customer_id
from customer c,orderhdr o,tickler_records t
where c.customer_id=o.customer_id
and o.customer_id=t.customer_id
and o.ohinvamt=$importo
and o.ohentdate=to_date($emiss,'DDMMYY')
and (t.long_description like '%Carta di credito%$cdc_cut%'
or long_description like '%Cambio Metodo di Pagamento%$cdc_cut%'
or long_description like '%Carta di Credito%$cdc_cut%');
exit;
EOF
cat result.txt | while read customer_id
do doCSVSQL
rm -f result.txt
done
done
Yes, the "spool" would be the result.txt, sorry for the mislead
I remove it because of the second function doCSVSQL. Since it's another SQL file it doesn't go well with multiple records, so I delete and create it again to only have 1 record inside it.
And yes, I know it's brutal I tried making a variable like this:
customer_id=$(-query)
But I got a "Line too long" error.
Again, I'm sorry if this is a mess, but thanks
I'm gonna try and remove the delete part and the second function right now and see how it works.
Can you say in simple terms exactly what you expect to have at the end? There's stuff in the code you've posted that doesn't tie in anywhere (such as the SQL in @do_csv.sql, for example).
Step1, extract an ID and put it in the result file.
Step2, read that file and use that record to run the .sql, then delete the result file.
Step3, repeat until while cycle ends.
At the end, you'd have a report_dettaglio_date that contains every result of the .sql run. I didn't post the content of the SQL file because I thought it was not important.
There's probably some easier way to do this, but I'm still practicing.
---------- Post updated at 10:36 AM ---------- Previous update was at 09:49 AM ----------
I removed the second cycle and left with this, but still no spool. So there was no accident delete of the file.
What bothers me is that I can't even do it in some other way. I really need to spool that output in a file.
Thanks again for helping me out guys, you're the best
That's a useless use of cat award. You don't need cat's help to read a file, and you don't need to run awk three times per line to extract three fields separated by ;
Why do you need it in the result file if you already have the ID? Wouldn't you rather have sqlplus' output?
Try redirecting the entire loop once instead of 1000 times for 1000 lines:
#!/bin/ksh
...
while IFS=";" read CDC EMISS IMPORTTO OTHER
do
cdc_cut="${CDC:13:4}"
sqlplus <<EOF
...
EOF
done < inputfile > outputfile
You might even be able to do
while IFS=";" read CDC EMISS IMPORTTO OTHER
do
cdc_cut="${CDC:13:4}"
cat <<EOF
...
EOF
done < inputfile | sqlplus > outputfile
to put more than one sql query into one run of sqlplus.
I've been fidgeting around that syntax a bit, and it looks like it's spooling now. I don't know how, I just altered a few things and got rid of the "Stupid use of cat" but apparently that was enough for him.
If you were using your variables outside the while-loop, cat would have prevented them from being set, because the while-loop would be run in a subshell.
