SQLPlus conditional execution with variable from query

Question

I have a batch file which has many steps in it that will get executed one by one.

However, to be able to make it more flexible, I want to include a condition check in SQLPlus.

Something like, to get the conditional variables' value from a query first and store is in say v_variable. Then use it for some checks like

IF v_variable = 'Y' THEN
--DO SOME DDL
ELSE
--DO OTHER DDL
END IF

I have to repeat this block in many places in the batch file and I can't do it through a PL/SQL somehow.

I am trying to use this COLUMN command in SQLPlus but somehow not getting the variable value to get saved.

COLUMN VARIABLE1 NEW_VALUE V_VARIABLE1

SELECT PARAM_VAL AS VARIABLE1 FROM TABLE_T WHERE PARAM_TYPE =  'XYZ'; 
-- This query will only throw one record.

DEFINE V_VARIABLE1

Is that absolutely wrong? What do we do to see if the V_VARIABLE1 is getting the value coming from the query?

And even after I get it right I am clueless on the IF-ELSE part. Can anybody help here? I am interested in solution that works for SQLPlus.

Answer 1

sql*plus doesn't support flow control natively, so anything you can do here would fall somewhere between "workaround" and "hack". Some of possible options are:

1. Use PL/SQL block and run your DDL as dynamic SQL via execute immediate or dbms_utility.execute_ddl_statement. Full access to PL/SQL features, so most flexible in terms of flow control and statement building, however harder to manage if you're deploying something large.

2. Write a script file per if/else branch, get its name with something like column/query trick you provided in your post, run it with something like @&scriptname.

3. Use substitution variables that, when used properly, will comment out some parts of your script. You can use Tanel Poder's snapper utility script as example; notice &_IF_ substitution variables there.

4. You can embed child script into parent script's pl/sql block. Like this:

--

21:21:23 SQL> ho cat run.sql
begin
case '&1.' when 'A' then
@script_a
when 'B' then
@script_b
else
null;
end case;
end;
/

21:21:26 SQL> ho cat script_a.sql
dbms_output.put_line('this is a');

21:21:30 SQL> ho cat script_b.sql
dbms_output.put_line('this is b');

21:21:34 SQL> @run
Enter value for 1: A
this is a

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
21:21:37 SQL> @run B
this is b

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03