How to call Oracle function with multiple arguments from shell script?
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.
Code:
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
Code:
test.sh: line 79: syntax error near unexpected token `('
test.sh: line 79: ` var RetVal VARCHAR2(4000)'
Please help.
Moderator's Comments:
Please wrap all code, files, input & output/errors in CODE tags.
It makes them far easier to read and preserves multiple spaces for indenting or fixed-width data.
Last edited by rbatte1; 08-26-2016 at 01:13 PM..
Reason: Added CODE tags for output
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.
Code:
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;
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.
Code:
#!/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:
Code:
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)'
Moderator's Comments:
Please wrap all code, files, input & output/errors in CODE tags.
It makes them far easier to read and preserves multiple spaces for indenting or fixed-width data.
Last edited by rbatte1; 08-30-2016 at 04:43 AM..
Reason: Added CODE tags
I have a script that uses 2 arguments. I want to call the function part within this script using these same arguments. Below is what I came up with below script so far, any guidance would be helpful. Thank you!
cat backup.sh
#!/bin/bash
function usage {
echo "USAGE: $(basename $0)... (6 Replies)
I am converting shell script to Perl. In shell I have a code
parse_prog_args()
{
if
then
while
do
case $1 in
-P* | -p* )
export PROCESS_DATE=$2
export MM=`echo $2 | cut -c5-6`
export DD=`echo $2 | cut -c7-8`
export YY=`echo $2 | cut -c3-4`
export... (4 Replies)
I need to call a function within a code with $database and $ service as the arguments How do I proceed ? and how would a function be defined and these two arguments would be used inside the function?
calc_pref_avail $database $service
Best regards,
Vishal (7 Replies)
Here is the following code :
1.
# gcc -c test firstprog.c
the above command will generate a executable file called "test " in which ever directory it is run.
Assuming It will also return a value.
2. In the below SCRIPT . test is a file generated by compiling a c program... (3 Replies)
Hi everyone!
I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB).
I have to create a shell script to execute that procedure for each txt... (5 Replies)
Hi,
I have a function in shell script
fun1{ echo "No.of arguments are..."}
this function will be called in same script by passing arguments
fun 1 2 3
I want to check the no. of arguments passed to fun1 function in the same functionbefore validation.
can any one suggest me. (2 Replies)
i have a function written in one shell script and i want to call that function in another shell script and use the value returned by that script.
can any one suggest me how can i do that?
regards,
Rajesh.P (4 Replies)
Hi,
I have two shell variables $t1 and $t2 which I need to pass to a function in a shell script. The function will do some computation with those two variables and echo the resultant. But I do not know how to pass teh arguments.
The function written is
f1()
{......
........
}
What should... (3 Replies)