How to call Oracle function with multiple arguments from shell script?

Shell Programming and Scripting
1

Dear All,
I want to know how can i call oracle function from shell script code . My oracle function have around 5 input parameters and one return value.

for name in *.csv;
do
echo "connecting to DB and start processing '$name' file at "
 echo "csv file name=$x"
 sqlplus -s scoot/tiger <!
 #select $x from dual;
 var RetVal  VARCHAR2(4000)
 exec scott.LOAD_CSV($var_table_name,$var_directory,'$x',$var_ignore_header,$var_delimiter,:RetVal)
 select :RetVal from dual;
!
echo "File '$x' load completed on "
done

I'm getting below error

test.sh: line 79: syntax error near unexpected token `('
test.sh: line 79: ` var RetVal  VARCHAR2(4000)'

Please help.

2

There are lots of errors - $x should be $name or $name should be $x - everywhere in the script

scoot/tiger should be scott/tiger
All of the red words have issues, were missing or were wrong like using ' around a variable instead of double quotes.

for name in *.csv;
do
echo "connecting to DB and start processing '$name' file at "
 x="$name"
 echo "csv file name=$x"
 sqlplus -s scoot/tiger <<!
 #select $x from dual;
DECLARE
 RetVal  VARCHAR2(4000);  -- lose var
BEGIN
 exec scott.LOAD_CSV($var_table_name,$var_directory,'$x',$var_ignore_header,$var_delimiter,:RetVal)
 select :RetVal from dual;  -- I cannot understand what this does see comment about DBMS_OUTPUT
END
/
!
   echo "File $x load completed on "
done

Plus, there just be other lines of code you did not show since there are not 79 lines in the code.
I would guess the actual error the shell found first started somewhere else.

DBMS_OUTPUT is what you should use to debug PL/SQL code - what you are writing, why I enclosed part of the script with - not select something from dual;

DECLARE
....
BEGIN
...
END
/
3

Hi jim mcnamara,
Thank you for pointing out the problem in my code. I removed all unnecessary lines as you suggested. Still I'm getting same error. I'm posting my entire shell script code below.

#!/bin/bash
### ##########################################################################################################################################
###   Below Code is setting environment varaible path.
### ##########################################################################################################################################
ORACLE_BASE=/data/oracle/app
ORACLE_HOME=/data/oracle/app/product/db/11.2.0
ORACLE_SID=gxcdb
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH

now=$(date)

echo  "Process Started at- '$now' "
date

### ##########################################################################################################################################
###   Below select statment will fetch  Source and archive directory path.
### ##########################################################################################################################################

path_varaible=$( 
                echo "set feed off
                set pages 0
                select response_data_dir_path,archive_dir_path
                from xw_config 
                where name ='RESP';
                exit
                "  | sqlplus -s SCOTT/TIGER
               )


if [ -z "$path_varaible" ]; then
    echo "No rows returned from database"
    exit 0
else
    echo "path_varaible value available"
fi

### ##########################################################################################################################################
###   Assigning values to varaible from above select statement.
### ##########################################################################################################################################

data_dir=`echo $path_varaible | awk '{print $1}'`
archive_dir=`echo $path_varaible | awk '{print $2}'`

echo "data_dir is $data_dir"
echo "archive_dir is $archive_dir"

cd $data_dir

echo "************** varaible values ***************"
var_table_name='XW_RESPONSE_DATA'
var_directory='XW_SP_XML_DIR'
var_ignore_header=1
var_delimiter=','
var_optional_enclosed='"'


echo "var_table_name : $var_table_name"
echo "var_directory : $var_directory"
echo "var_ignore_header : $var_ignore_header"
echo "var_delimiter : $var_delimiter"
echo "var_optional_enclosed : $var_optional_enclosed"

for filename in *.csv;
do
 echo "connecting to DB and start processing '$filename' file "
 echo "csv file name=$filename"
 sqlplus -s SCOTT/TIGER <!
 DECLARE
 RetVal  NUMBER;
 exec SCOTT.LOAD_CSV('$var_table_name','$var_directory','$filename',$var_ignore_header,'$var_delimiter','$var_optional_enclosed',:RetVal)
 END
 /!
echo "File '$filename' load completed"
done

Still I'm getting below error message:

test.sh: line 81: syntax error near unexpected token `('
test.sh: line 81: ` execute SCOTT.LOAD_CSV('$var_table_name','$var_directory','$filename',$var_ignore_header,'$var_delimiter','$var_optional_enclosed',:RetVal)'