Hi All,
Using Solaris box
bash-3.00$ echo $BASH_VERSION
3.00.16(1)-release
I have a real bummer of a bug, basically Im running a bash script that executes a bash function "dbase_sql". The bash function accepts a parameter in the form of an Oracle update statement eg
dbase_sql "update dte\_batch_details
set row\_count='$row_count'
, checksum\_value='$checksum_value'
, load\_attempts=NVL\(load_attempts,0\)\+1
, pre\_stage\_errors='$pre\_stage_errors'
, message='$sql\_err_msg'
where object\_name='$source\_tabin_name'"
The function itself is as below
dbase_sql()
{
echo "dbase_sql() 1.1 "
local sql="$1"
echo "sql="$sql
echo "showed ECHO!"
sqlplus -s $DBUSER/$DBPASS@$DBNAME<<EOF
set serveroutput on size 1000000
set verify off
set feedback off
VARIABLE vi_err NUMBER
declare
begin
${sql};
commit;
:vi_err:=0;
exception
when others
then
:vi_err:=1;
end;
/
EXIT:vi_err
EOF
}
The perplexing thing is that I cannot get the call to the function to work in my main shell script, so I created a simple script that calls the function and that does seem to work! For some reason, the bug is that the echo does not show the full statement in the function and hence it passes the malformed statement to the oracle call which then fails.
Error from the shell is as follows:
dbase_sql() 1.1
' , load_attempts=NVL(load_attempts,0)+1 , pre_stage_errors='Y' , message='/#SQL Loader Bad File[/export/home/ORACLE/product/10.2.0/TORPEDO/LOGS/SQLLDR_bad_TOS_TABIN218.bad]' where object_name='TOS_TABIN218'
showed ECHO!
Thursday, 25 June 2009 14:06:43 BST #Application Error - Abort: Call to dbase_func.dbase_sql()
The working version shows:
dbase_sql() 1.1
sql=update dte_batch_details set row_count='1' , checksum_value='1' , load_attempts=NVL(load_attempts,0)+1 , pre_stage_errors='Y' , message='None/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]/#SQL Loader Bad File[]' where object_name='TOS_TABIN218'
showed ECHO!
Any ideas?!
Kind Regards
Satnam