Initialising SQL*Plus variables with NEW_VALUE

Whatever new and innovative tooling Oracle comes up with, it’s almost inevitable that, sooner or later, you will need to do something inventive in SQL*Plus.

This usually happens when you are confronted with an aged version of the RDBMS which has somehow managed to avoid upgrade or replacement, often for decades.

I mean, let’s face it, what is the oldest Oracle version you’ve been confronted with in the last year ?
For me it’s 9i ( released over 20 years ago). I wouldn’t be completely surprised if you’ve come across versions that are even older.

Fittingly enough it’s FA Cup Final weekend.
Being of an age where I grew up with the mythology of The Matthews Final, The White Horse and the Wembley Hodoo, I have a well of nostalgia to use as source material for the examples which follow.

Example Table and Data

This year is the 150th anniversary of the first FA Cup Final. The two finalists are Liverpool and Chelsea.
As you’d expect, both have a long history in this competition and have appeared in numerous finals.
In fact, let’s create a table and populate it with the details of those Finals :

Say we need to produce a report listing all of the Cup Finals in which a given team participated.
The name of the team is entered as a paramter at runtime.
Oh, and the report needs to be saved to a file which includes the name of the team (i.e. the value of the runtime parameter).

Column NEW_VALUE

SQL*Plus is hugely versatile and the column formatting command NEW_VALUE can be repurposed to achieve the desired effect :

Let’s step through this script.

column team new_value v_team noprint
select initcap('&1') as team from dual;

The first line “declares” the variable v_team, which will be populated from the value of the team column in the next query.

We then run a SQL statement to select the value of the paramter passed to the script at runtime.
Note that this could be any SQL query. Here however, I want to canonicalize the parameter so I don’t need to do any fiddly character comparison later in the script.

Not only can we now use the variable in SQL*Plus commands…

spool 'cup_finals_by_team_&v_team..txt'

NOTE – the double “.” is because the first “.” acts as a delimiter for the variable value. You only need to worry about this in the spool command if the variable value comes directly before the file extension delimiter.

…we can also use it in the SQL of the main report query :

select 
    year, 
    case winners when '&v_team' then 'Won' else 'Lost' end as outcome,
    case winners when '&v_team' then null else winners end
    ||
    case runners_up when '&v_team' then null else runners_up end as opponents,
    case winners 
        when '&v_team' then winners_score||'-'||ru_score
        else ru_score||'-'||winners_score
    end as "final score"    
from fa_cup_finals
where winners = '&v_team' or runners_up = '&v_team'
order by year
/

As expected, running the report…

…generates an appropriately named file…

ls -l *.txt
-rw-rw-r-- 1 mike mike 2248 May 15 20:43 cup_finals_by_team_Chelsea.txt

…which will allow us to relive former glories…

cat cup_finals_by_team_Chelsea.txt

…or if you prefer…

cat cup_finals_by_team_Liverpool.txt

References

The relevant Oracle documentation will depend on the version of your Oracle client rather than that of the RDBMS.
Also, this note by Rene Nyffenegger is extremely helpful.