Stupid Oracle Tricks #1: SQL substitution variables

A common behaviour of a program, and therefore of my SQL script to replicate the program, is:
1) Retrieve some value from reference data
2) Use it repeatedly in SQL statements

If this reference data differs by environment or by day, this adds a manual step to the SQL script.

Here’s a neat trick to save the output of the reference data query and use it later:

column useful_value_alias new_value useful_value

select as useful_value_alias from where

select ‘Here is the useful value: ‘ || &useful_value from dual;

This sets up a column alias that stores the value for the column in a substitution variable named “useful_value”. To substitute the value in later SQL, we stick an ampersand in front of the variable name.

It beats hardcoding the value multiple times later in the SQL script, and it also beats using a nested select or join on the reference table over and over.

For lots more details on substitution variables, reference this blog post at OracleFAQ.

(To set the value explicitly, DEF variable_name=value)