-1

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.

Naidu5
  • 17
  • 2
  • 8
  • 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 Aug 17 '20 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 Aug 17 '20 at 16:29
  • Looks like duplicate of many other questions about passing CMD arguments into SQLPlus. Like https://stackoverflow.com/questions/18620893/how-do-i-pass-arguments-to-a-pl-sql-script-on-command-line-with-sqlplus/18621153 and https://stackoverflow.com/questions/40989668/how-to-pass-parameters-to-sql-scripts-via-command-line – Dornaut Aug 17 '20 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 Aug 18 '20 at 04: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 Aug 18 '20 at 06:15
  • In the question you said you're getting ORA-01722; in a comment above you said bind variable not defined. The code you showed would prompt for substitution variables Please edit the question to show your actual code, parameter values and the error you get with that combination. With the current code you just need to change to positional substitution variables `'&1'` and '`&2'`. (Though you still shouldn't rely on implicit conversion of the date strings...) – Alex Poole Aug 18 '20 at 07:01
  • @AlexPoole, thanks for the help, I was directly passing shell script arguments with above method and defined varaibles again in the script and passing those will work. – Naidu5 Aug 18 '20 at 14:11

1 Answers1

0
....
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.

Naidu5
  • 17
  • 2
  • 8