UNIX for Advanced & Expert Users

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

Is there any difference between the echo syntax in your longer and the working shorter script? Basically you have problems with single and/or double quotation marks it seems. Maybe try playing around by escaping them with a backslash until you get the desired output.
Another way might be putting the statement to a plain file and then hand it over as a parameter to your script to have less hassle.
Also use CODE-tags when posting code, data or logs in future please.