Needs to pass variable values to sqlplus sql file from shell script

Question

I am using the Properties file to get the DB connection values and below is shell script file.

....
read -p "Please enter start date and end date " para1 para2
source database.prop
for ((i=1;i<=$Dbcount;i++ )); do
sqlplus -S ${user1}/${Password}@${conn} <<EOF &
spool sqlcsvdb_"$i".csv
@squery.sql $para1 $para2
exit;
EOF
done
....

squery.sql file.

....
SET PAGESIZE 50000
SET COLSEP "|"
SET LINESIZE 20000
SET headsep ON
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
set verify off 
SELECT id|| '|'||date|| '|'|| appid from table where date between '&para1' and '&para2';
exit;
EOF
....

When I execute shell it is not passing the variable values and getting ERROR at line 1:ORA-01722: invalid number error message. Please help me on how to I resolve this and usage of bind variables.

Where are $para1 and $para2 being set, and have you checked they are correct before the SQL*Plus call? If they represent dates what format are they in? (You should probably be quoting them in the call, as @$para1", but not likely to be the issue.) As you've changed the SQL for posting, are you sure your real version has the single quotes around the substitution variables - and those should be in to_date() calls, unless the parameter format allows you use date literals? — Alex Poole
– Alex Poole, Commented Aug 17, 2020 at 15:21
Actually you aren't defining &para1 either, do you actually have &1 in your real script? It's hard to tell what you're really doing from this. Setting verify on might shed more light on what's going wrong though. If not then you'll need to give a more realistic example. Changing names and data is fine as long as you do it consistently and legally... — Alex Poole
– Alex Poole, Commented Aug 17, 2020 at 16:29
Looks like duplicate of many other questions about passing CMD arguments into SQLPlus. Like stackoverflow.com/questions/18620893/… and stackoverflow.com/questions/40989668/… — Dornaut
– Dornaut, Commented Aug 17, 2020 at 17:05
@Dornaut, I checked the above links but I am using the same way but still I get bind varaible not defined error message. — Naidu5
– Naidu5, Commented Aug 18, 2020 at 4:43
@AlexPoole, The date params are passing dynamically and date validation is same as Database date format which is working fine in my script, read -p "Please enter start date and end date " para1 para2 , passing date params as same in DB date format. — Naidu5
– Naidu5, Commented Aug 18, 2020 at 6:15

Naidu5 · Accepted Answer · 2020-08-18 14:09:49Z

....
read -p "Please enter start date and end date " para1 para2
Date1=$para1
Date2=$para2
source database.prop
for ((i=1;i<=$Dbcount;i++ )); do
sqlplus -S ${user1}/${Password}@${conn} <<EOF &
spool sqlcsvdb_"$i".csv
@squery.sql $Date1 $Date2
exit;
EOF
done
....
....
SET PAGESIZE 50000
SET COLSEP "|"
SET LINESIZE 20000
SET headsep ON
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
set verify off 
SELECT id|| '|'||date|| '|'|| appid from table where date between '&1' and '&';
exit;
EOF
....

Defining variables again in the shell script file then passing those variables to sql file works, until it is varaibles are defined within the shell script, it does not work.

Collectives™ on Stack Overflow

Needs to pass variable values to sqlplus sql file from shell script

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related