You Asked
Hello,
I have a variable in Unix shell script that contains list of comma separated tables. I want to use this variable in my pl/sql cursor. It works in a SQL statement but not when I refer it in a cursor.
Here is the example:
if [[ $v1 == TAB ]]; then
tabFile=`ls ~/tab.lst`
status=$?
if [[ $status -gt 0 ]]; then
echo "Required file ~/tab.lst is missing. Program exits" >> ${LOG_FILE}
error_status
exit 1
fi
cat ~/tab.lst |sed "s/^[ \t]*//;s/[ \t]*$//" > ~/tab
vlist=`sed -e s/^/\'/ -e s/$/\'/ ~/tab |tr '\n' ',' |sed -e s/^/\(/ |sed -e s/,$/\)/`
vCondition="b.table_name in $vlist"
fi
oraMsg=`$ORACLE_HOME/bin/sqlplus /nolog << EOF |grep -i ORA-
connect $ORACLE_USER/$ORACLE_PASS@$DB_NAME
set echo off linesize 850 heading off pagesize 1000 serveroutput on
whenever sqlerror exit sql.sqlcode;
truncate table tmphive;
declare
--The cursor below extracts table name and its related information that are required to be queried by Hive
cursor c1 is select /*TESTING VCONDITION8 */ project,job_name,location,table_name,pk_names from
(
select distinct c.project,a.job_name,b.location,b.table_name,b.pk_names, row_number() over(partition by b.location,table_name order by table_name)rk
from job_to_file_map a,
hive_table_map b,
sequence_project c,
job_to_sla_map d
where a.location = b.location
and c.sequence_name = d.sequence_name
and d.job_name = a.job_name
and b.pk_names is not null
and a.stage_level='BASE' and a.file_type_ind='OUTPUT'
and b.table_name not in ('FIN.IC_VEHICLE_USAGE_MODEL_ASSOC','MFG.IC_VEHICLE_OPTION','MFG.IC_VEHICLE_TRACE_PART','MFG.IC_VEHICLE_PART')
and $vCondition;
The value in the vCondition variable is apparently not read by the cursor statement. How to get this work?
Thanks,
and Connor said...
I'm guessing a typo or logic error somewhere, because if I do a very basic test case, it appears to work as expected.
First with a true condition, and I output a row containing an "ora-" to be picked up by your grep.
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_USER=xxxx
export ORACLE_PASS=xxxx
export DB_NAME=xxxx
export vCondition="1=1"
oraMsg=`$ORACLE_HOME/bin/sqlplus /nolog << EOF |grep -i ORA-
connect $ORACLE_USER/$ORACLE_PASS@$DB_NAME
set echo off linesize 850 heading off pagesize 1000 serveroutput on
whenever sqlerror exit sql.sqlcode;
declare
cursor c1 is select * from dual
where $vCondition;
begin
for i in c1 loop
dbms_output.put_line('ORA-'||i.dummy); <==== will return a row if vcond is true
end loop;
end;
/
exit
EOF
`
echo $oraMsg
Then I run it
[oracle@C102 ~]$ ksh demo.sh
SQL> 2 3 4 5 6 7 8 9 ORA-X
Then I changed the condition to be "1=0", and got:
[oracle@C102 ~]$ ksh demo.sh
So your concept is fine, there's just a typo or something awry in there.
As an aside, I'm not a fan of scripts like this where if it fails, all you have is the error. If something goes wrong, I want *Everything*, so I generally do this:
print
"connect blah
<all my stuff>
exit" | sqlplus /nolog 1>script.log 2>&1
failed=`grep 'ORA-' script.log`
That way, I've got all the output
Hope this helps