Passing parameters in SQL*Plus – or What's the opposite of a Chick Flick ?

Watching Iron Man 2 the other night, I was somewhat surprised by a brief appearance on screen of someone who looked remarkably like Larry Ellison.
No, he wasn’t the villain although – depending on what Oracle ends up doing to MySQL – he could be a candidate for the role in Iron Man 3.
As a result of riding this cinematic rollercoaster, I experienced two profound revalations.
The first is that, despite it’s poor relation status in the array of tools available to the Oracle developer, SQL*Plus can still be incredibly useful ( OK, I’ve always thought this – but I’m flashing my Poetic License here and no, it hasn’t expired yet).
The second is that the opposite of a Chick Flick must be a Bloke Buster. I know, I should really just stick to the programming stuff.

For those poor souls who seldom leave the safety of the IDE I should explain that SQL*Plus is Oracle’s command-line SQL environment.
Most of the time, it’s used for formatting output ( e.g. when generating slave SQL). However, it can also prove extremely useful when you want to pass parameters to scripts at runtime.

Continuing the cinematic theme, let’s say we’ve got a table containing details of films and their genres.

CREATE TABLE films(
    film_name VARCHAR2(30),
    genre VARCHAR2(20))
/

DECLARE
    PROCEDURE ins( pa_film VARCHAR2, pa_genre VARCHAR2) IS
    BEGIN
        INSERT INTO films( film_name, genre)
        VALUES( pa_film, pa_genre);
    END ins;
BEGIN
    ins('BRICK', 'THRILLER');
    ins('TITANIC', 'CHICK FLICK');
    ins('IRON MAN 2', 'BLOKE BUSTER');
    ins('VOLVER', 'PARA LAS SENORITAS');
    ins('BLAZING SADDLES', 'COMEDY');
    ins('SEX IN THE CITY', 'CHICK FLICK');
    ins('WATCHMEN', 'BLOKE BUSTER');
    ins('WITHNAIL AND I', 'COMEDY');
    ins('GROSSE POINT BLANK', 'COMEDY');
    ins('GROSSE POINT BLANK', 'BLACK COMEDY');
END;
/
commit;

We’ve written a function to return the genre for a given film title

CREATE OR REPLACE FUNCTION get_genre( pa_film IN VARCHAR2)
    RETURN VARCHAR2 AS
    
    l_genre films.genre%TYPE;
BEGIN
    SELECT genre INTO l_genre
    FROM films
    WHERE film_name = pa_film;
    
    RETURN l_genre;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        RETURN 'I told you we needed a Primary Key.';
    WHEN NO_DATA_FOUND THEN
        RETURN q'[I don't think I've seen that one yet.]';
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Hmmm...proper error : '||SQLERRM);
END get_genre;
/

We want to do some testing before we release this function into production, so we write a test harness

--
-- Test harness for the get_genre function
--
set serveroutput on
DECLARE
    l_film films.film_name%TYPE := 'BRICK'; -- hard-coded parameter
    l_genre VARCHAR2(100);
BEGIN
    l_genre := get_genre( l_film);
    DBMS_OUTPUT.PUT_LINE( l_film||' - '||l_genre);
END;
/

That’s OK, but you’ve got to change the value of the l_film variable every time you want to run a test against a different film name.
So, let’s get SQL*Plus to ask for the value at runtime :

set serveroutput on
DECLARE
    l_film films.film_name%TYPE := UPPER('&film'); -- replacement at runtime
    l_genre VARCHAR2(100);
BEGIN
    l_genre := get_genre( l_film);
    DBMS_OUTPUT.PUT_LINE( l_film||' - '||l_genre);
END;
/

That’s better. At least we can run the script for different values without changing the script every time. However, it’s not exactly user-friendly. What happens if we want to run this test a few months later when we’ve forgotten all about it and need a handy hint to remind us of what value we need to enter :

set serveroutput on
set verify off
accept film char format a30 prompt 'Enter the film title to get the genre : '
DECLARE
    l_film films.film_name%TYPE; -- replacement at runtime
    l_genre VARCHAR2(100);
BEGIN
    l_film := UPPER('&film');
    l_genre := get_genre( l_film);
    DBMS_OUTPUT.PUT_LINE( l_film||' - '||l_genre);
END;
/
undefine film

Note here that we’ve also taken away that scruffy variable replacement text. This is done with the line set verify off.
Nice little prompt giving us a clue what to type. That’s much better.
Finally, say we wanted to script all our tests to run in one go without any of that command line interaction. Well, you can do that with SQL*Plus as well, simply by changing the test harness script to accept parameters by position :

--
-- th4.sql
-- accept the film name as a command line argument
--
set serveroutput on
set verify off
DECLARE
    l_film films.film_name%TYPE := UPPER('&1');
    l_genre VARCHAR2(100);
BEGIN
    l_genre := get_genre( l_film);
    DBMS_OUTPUT.PUT_LINE( l_film ||' - '||l_genre);
END;
/

We can now simply call this script from a master script, passing the parameters as part of the call :

--
-- Automatic test script for get_genre
--
spool get_genre_at.log
prompt Running Automated Test Suite for GET_GENRE
prompt ==========================================

prompt Test 1- a Chick Flick
prompt ---------------------

@th4.sql titanic

prompt Test 2 - Spanish chick flick
prompt ----------------------------

@th4.sql VOLVER

prompt Test 3 - spaces in the name
prompt ----------------------------

@th4.sql 'Iron Man 2'

prompt Test 4 - multiple entries for film in table
prompt -------------------------------------------

@th4.sql 'grosse point blank'

prompt Test 5 - Film does not exist in table
prompt -------------------------------------

@th4.sql 'Ellison Strikes Back'

spool off

The log file we get looks like this :

Running Automated Test Suite for GET_GENRE
==========================================
Test 1- a Chick Flick
-------------------- 
TITANIC - CHICK FLICK                                                           

PL/SQL procedure successfully completed.

Test 2 - Spanish chick flick
--------------------------- 
VOLVER - PARA LAS SENORITAS                                                     

PL/SQL procedure successfully completed.

Test 3 - spaces in the name
--------------------------- 
IRON MAN 2 - BLOKE BUSTER                                                       

PL/SQL procedure successfully completed.

Test 4 - multiple entries for film in table
------------------------------------------ 
GROSSE POINT BLANK - I told you we needed a Primary Key.                        

PL/SQL procedure successfully completed.

Test 5 - Film does not exist in table
------------------------------------ 
ELLISON STRIKES BACK - I don't think I've seen that one yet.                    

PL/SQL procedure successfully completed.

After all that excitement, I think I’ll sit down and watch a DVD. Now, what did I do with that Firefly box set ?

About these ads

Tags: Anti-Kyte, define, pass parameters by position, SQL*Plus