Today I’ll show a trick how we can use branching execution of SQLPlus scripts in SQL*Plus.

Although I previously showed the conditional execution of scripts and it really can be used for branching, but today I’ll show how to do it without splitting the script into several smaller scripts. In contrast to the conditional script execution, I’ll use a different method.

It is very simple, as usual – if you want to execute only one part of script, you can just comment out all unnecessary. So depending on the conditions, we can execute a script which will start a comment.

Suppose we need to create a script, which, depending on the input parameter will be required to execute a specific query.

See how this can be done:

1. “test.sql”:

def param = &1 @if ¶m=1 select 1 from dual; /* end_if */ @if ¶m=2 select 2 from dual; /* end_if */ @if ¶m=3 select 3 from dual; /* end_if */

2. “if.sql”:

col do_next new_val do_next noprint; select case when &1 then 'inc/null' else 'inc/comment_on' end as do_next from dual; @&do_next

3. “inc/comment_on.sql” contains only 2 chars:

/*

4. “inc/null.sql” is the same as in the previous examples – just empty file.

Ok, lets test it:

SQL> @test 1 1 ---------- 1 SQL> @test 2 2 ---------- 2 SQL> @test 3 3 ---------- 3

As you see, we got what we wanted. Please note that we have to close the multiline comments in the right places only(/* end_if */). So we cannot use in these parts another “*/”. But you can use it in another child scripts.

Same way we can make an analogue of switch/case:

“test2.sql”:

@switch &1 @when 1 then select 1 from dual; /* end when */ @when 2 then select 2 from dual; /* end when */ @when 3 then select 3 from dual; /* end when */ /* end switch */

switch.sql def switch_param=&1 [collapse]

when.sql col do_next new_val do_next noprint; select case when &1 = &switch_param then 'inc/null' else 'inc/comment_on' end as do_next from dual; @&do_next [collapse]

SQL> @test2 2 2 ---------- 2 SQL> @test2 3 3 ---------- 3

Example: