To call Oracle procedure by reading parameter file in UNIX

Shell Programming and Scripting
1

hi Guys,

is there a way to pass parameter into oracle store procedure by reading date range in file and increment accordingly. Something like this

file.txt
01-JAN-2015
03-JAN-2015

sqlplus -s user/pwd@DB
execute TEST( to_date( '01-JAN-2015, 'dd.mm.yyyy' ), to_date( '03-JAN-2015',  'dd.mm.yyyy' ) ) ;
exit
2

I'm pretty sure your line wouldn't execute, as the closing single quote for the first date is missing, and the format specifier doesn't match the date constants.

Howsoever, you could try a "here document":

mapfile -t <file.txt
sqlplus -s user/pwd@DB << EOF
execute TEST( to_date( ' ${MAPFILE[0]}', 'dd-mon-yyyy' ), to_date( '${MAPFILE[1]}',  'dd-mon-yyyy' ) ) ;
EOF

This is to be taken cum grano salis, as my sql is somewhat rusty and I don't have a DB at hand to perform tests.