Conditionally calling a script in a SQL*Plus control script

For those of us who haven’t quite gotten around to incorporating Liquibase in our database code release pipeline, we sometimes find ourselves in a situation where it would be extremely useful to be able to apply some branching logic in a SQL*Plus control script.
In order to save myself the pain of trying to figure out exactly how you can do this without writing lots of dynamic code, I’ve decided to write it down.

NOTE – the scripting techniques outlined here work the same way in both SQLCL and SQL*Plus.

What follows are examples of SQL*Plus scripts which implement branching to :

• choose which of two scripts to run
• choose whether or not to run a script

The difference between these two use cases is fairly subtle, but worth exploring…

Choosing between two scripts

Here are two very simple scripts. The first is called weekday.sql :

prompt Another working day :-(

…and the second is called weekend.sql

prompt Yay, it's the weekend :-)

I want to write a control script which calls one – and only one – of these scripts based on what day it is today.

We can establish the day easily enough with :

select to_char(sysdate, 'DY') from dual;

In order to use this information to influence the behaviour of the control script at runtime, we’ll need to assign it to a variable that we can reference. In SQL*PLUS, we can do this with NEW_VALUE :

The first line of the script…

column weekday new_value v_script noprint

…takes the value of the column “weekday” from result of the subsequent query and assigns it to the variable v_script.

I’ve saved this script as choose_message.sql.

When I run it I get :

As you can see, only the message from weekend.sql is shown. The weekday.sql script is not executed.

Choosing whether or not to run a script

This is a little different as, with this technique, the script will always call a file at the end.
Therefore, I’ve got a placeholder script which will get executed if we decide we don’t want to run the actaul target script. The new script is placeholder.sql and is predictably simple :

prompt You have chosen to skip weekday.sql

This time, the control script is called run_weekday_yn.sql and accepts a parameter :

This will run weekday.sql…

…but only if we tell it to…

Of course, you can use a positional notation for the parameter to allow it to be passed unprompted at runtime. This is run_it_yn.sql :