Oracle SQL if statement based on parameter passed in via .sh script

Question

I have a shell script which calls some SQL like so

sqlplus system/$password@$instance @./oracle/mysqlfile.sql $var1 $var2 $var3

Then in mysqlfile.sql, I define properties like this:

DEFINE var1=&1
DEFINE var2=&3
DEFINE var3=&3

Later in the file, I call another SQL script:

// i wish to wrap this in a if statement - pseudo-code
if(var3="true") do the following
@./oracle/myOthersqlfile.sql &&varA &&varB

I am not sure how to implement this though, any suggestions appreciated

score 1 · Answer 1 · answered Aug 04 '20 at 17:12

You could (ab)use substitution variables:

set termout off
column var3_path new_value var3_path
select case
  when '&var3' = 'true' then './oracle/myOthersqlfile.sql &&varA &&varB'
  else '/dev/null'
  end as var3_path
from dual;
set termout on

@&var3_path

The query between the set termout commands - which just hide the output of the query - uses a case expression to pick either your real file path or a dummy file; I've used /dev/null, but you could have a 'no-op' file of your own that does nothing if that's clearer. The query gives the result of that the alias var3_path. The new_value line before it turns that into a substitution variable. The @ then expands that variable.

So if var3 is 'true' then that runs:

@./oracle/myOthersqlfile.sql &&varA &&varB

(or, actually, with the varA and varB variables already replaced with their actual values) and if it is false it runs:

@/dev/null

which does nothing, silently.

You can set verify on around that code to see when and where substitution is happening.

good trick ;) I never used like this, I always apply the logic outsitde of sqlplus, but your example is quite good. Upvote is the less I can do — Roberto Hernandez, Aug 05 '20 at 11:49

score 0 · Answer 2 · answered Aug 04 '20 at 16:16

You can't implement procedural logic into sqlplus. You have these options :

Implement the IF-THEN-ELSE logic inside the shell script that is running the sqlplus.
Use PL/SQL, but then your SQL Script should be called as a process inside an anonymous block, not like an external script.

In your case the easiest way is to change your shell script.

#/bin/bash
#
# load environment Oracle variables
sqlplus system/$password@$instance @./oracle/mysqlfile.sql $var1 $var2 $var3
# if then
if [ $var3 == "true" ]
then 
   sqlplus system/$password@$instance @./oracle/myOthersqlfile.sql 
fi

You should realise that sqlplus is just a CLI ( Command Line Interface ). So you can't apply procedural logic to it.

I have no idea what you do in those sql scripts ( running DMLs, creating files, etc ), but the best approach would be to convert them to PL/SQL, then you can apply whatever logic you need to.

Oracle SQL if statement based on parameter passed in via .sh script

2 Answers2