How to pass a variable to sqlplus in a bash script

Question

I have a script like below. My goal is write to a log file terminating with the current date and time as filename and spool to the file:

#!/bin/bash

year=`date +%Y`
month=`date +%m`
day=`date +%d`
prefixe=$month$day$year
logfile="/home/oracle/logs/exec_proc_daily_20_"$prefixe.log

sqlplus / "as sysdba" <<EOF
spool on
spool $logfile
execute Proc_RFC_MAJ_MV_ITIN;
execute Proc_RFC_MAJ_MV_REFGEO;
commit;
quit
EOF

When I execute the script, the spool $logfile gives an error. No log is created. But it works when I use something like spool exec_proc_daily_2o.log. Why is the variable $logfile not replace.

Replacing sqlplus / "as sysdba" with cat and running your script suggests that the variable replacement is working as expected... — Stygies
– Stygies, Commented Oct 24, 2016 at 15:55

Beege · Accepted Answer · 2016-11-16 21:25:53Z

3

[Edited with working example] Pass a variable to sqlplus:

demo.sh

export myvar1="hello"
export myvar2=123
sqlplus "/ as sysdba" @demo.sql $myvar1 $myvar2
echo "---shell is complete---"

demo.sql

select 'first variable is &1 and second is &2'
from dual;
exit

edited Nov 16, 2016 at 21:25

answered Oct 26, 2016 at 14:53

Beege

6654 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fokwa Best Over a year ago

Hi @Beege. Thanks for your input. I've tried your approach but it doesn't work. I get the message of incorrect usage of sqlplus command with suggestions on how to use it.

Lav Patel Over a year ago

works in linux env, I have made two changes as I don't like to refer $1 and $2 in my SQL script. ``` define myvar1='&1' define myvar2='&2' select 'first variable is &&myvar1 and second is &&myvar2' from dual; exit ```

Fokwa Best · Accepted Answer · 2016-10-25 04:39:18Z

2

After doing alot of digging, I found the solution to the problem. On the line sqlplus / "as sysdba" <<EOF, I passed the variable like this sqlplus / as sysdba <<EOF >$logfile. So the complete code should be:

#!/bin/bash

year=`date +%Y`
month=`date +%m`
day=`date +%d`
prefixe=$month$day$year
logfile="/home/oracle/logs/exec_proc_daily_20_"$prefixe.log

sqlplus / "as sysdba" <<EOF >$logfile
spool on
spool $logfile
execute Proc_RFC_MAJ_MV_ITIN;
execute Proc_RFC_MAJ_MV_REFGEO;
commit;
quit
EOF

answered Oct 25, 2016 at 4:39

Fokwa Best

3,5357 gold badges39 silver badges57 bronze badges

1 Comment

Beege Over a year ago

Your sqlplus command is redirecting the output of sqlplus to the $logfile.

Lav Patel · Accepted Answer · 2019-08-04 18:54:52Z

1

@Beege answer works in Linux env. I have made few changes as I don't I like to refer to &1 and &2 in SQL script everywhere.

myvar1="hello"
myvar2=123
sqlplus username/password@SID @oracle_variabling_passing.sql $myvar1 $myvar2
echo "---shell is complete---"

oracle_variabling_passing.sql:

define first_var=&1
define second_var=&2
select 'first variable is &&first_var and second is &&second_var'
from dual;
exit

answered Aug 4, 2019 at 18:54

Lav Patel

1,0471 gold badge8 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to pass a variable to sqlplus in a bash script

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related