Native Dynamic SQL – Dodgy Code and DRS

Dynamic. That’s a positive word if ever there was one. Ascribing this adjective to anything would convey an image of energy and forward momentum.
On the question of Dynamic SQL, the images are rather more equivocal.
On the plus side, Native Dynamic SQL gives you the ability to :

  • execute DDL statements from within PL/SQL programs
  • code for instances where the required DML statement is not known ahead of time

On the flip side, it can also mean code that is:

  • difficult to read and maintain
  • prone to performance problems
  • insecure

To wander through this minefield, I have enlisted the support of a world where DRS does not stand for Dodgy Review System.
Yes, it’s the wacky and entertaining (not always intentionally so) world of Formula 1…

The test tables

First, we’re going to create a table to hold a list of drivers who have won the F1 World Championship :

CREATE TABLE driver_champions(
    year NUMBER(4) PRIMARY KEY,
    first_name VARCHAR2(30),
    last_name VARCHAR2(30),
    country VARCHAR2(50),
    team VARCHAR2(50),
    race_wins NUMBER(2))
/

…and one for the teams who have won the Constructors Title…

CREATE TABLE constructor_champions(
    year NUMBER(4) PRIMARY KEY,
    team VARCHAR2(50),
    country VARCHAR2(50),
    engine VARCHAR2(50),
    wins NUMBER(2))
/

For reasons entirely related to providing an example for this blog…

  • the tables are populated by an overnight PL/SQL job
  • the job needs to cleardown the tables before running

    Generally, the quickest way to cleardown data from a table is to truncate it. But TRUNCATE is a DDL command.
    How do we implement this in PL/SQL ?

    NDS DDL statements

    Is it a bird ? is it a plane ? Nope, it’s Native Dynamic SQL.
    Say we have a requirement to cleardown the tables prior to populating them… script…

    DECLARE
        l_stmnt VARCHAR2(4000);
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE driver_champions';
        --
        -- Or, using a variable...
        --
        l_stmnt := 'TRUNCATE TABLE constructor_champions';
        EXECUTE IMMEDIATE l_stmnt;
        ...
    

    Nice and simple…except…you need to remember that a DDL statement will end the current transaction, commiting all outstanding transactions. If this is not what you intend, then you need to make sure that the NDS statement executes in a completely separate transaction…

    DECLARE
        l_stmnt VARCHAR2(4000);
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE driver_champions';
        --
        -- Or, using a variable...
        --
        l_stmnt := 'TRUNCATE TABLE constructor_champions';
        EXECUTE IMMEDIATE l_stmnt;
        ...
    

    NDS DML

    Anyway, moving on, we’ve been asked to write a function to return the name of a champion (constructor OR driver) for a given year. It’s last thing on Friday so we don’t want to waste time writing two functions.
    But how can we write a query where we don’t really know which table we’re selecting from at design time ?

    Dynamic Select statement

    One answer ( one that uses NDS, anyway) is…

    CREATE OR REPLACE FUNCTION get_champion_fn(
        i_year IN NUMBER,
        i_team_or_driver IN VARCHAR2 DEFAULT 'TEAM')
    RETURN VARCHAR2 IS
    --
    -- Function to demonstrate use of dynamic sql where table name is known
    -- only at runtime.
    -- Also binds the predicate value.
    --
        l_qry VARCHAR2(4000);
        l_rtn VARCHAR2(50);
    BEGIN
        IF i_year IS NULL THEN
            RAISE_APPLICATION_ERROR(-20000, 'No year specified.');
        END IF;
        IF UPPER(i_team_or_driver) NOT IN ('TEAM', 'DRIVER') THEN
            RAISE_APPLICATION_ERROR(-20001, 'Valid values are TEAM or DRIVER.');
        END IF;
        l_qry := 'SELECT '||
            CASE i_team_or_driver
                WHEN 'TEAM' THEN 'team '
                ELSE 'last_name '
            END
            ||' FROM '||
            CASE UPPER(i_team_or_driver) 
                WHEN 'TEAM' THEN 'constructor_champions '
                ELSE 'driver_champions '
            END
            ||' WHERE year = :1';
        --
        -- Now execute, passing in the year as a bind variable and returning
        -- the result into a variable
        --
        EXECUTE IMMEDIATE l_qry INTO l_rtn USING i_year;
        RETURN l_rtn;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid Year for this championship.');
    END;
    /
    

    The syntax of the EXECUTE IMMEDIATE statement bears closer examination.

    The INTO clause simply returns the result of the query into a variable. Unlike with an Insert statement, you don’t specify the RETURNING keyword.
    The USING clause binds the specified variable value into the syntax of the query itself, in this instance replacing the :1 placeholder.

    By binding variables into the query, we handle two of the Dynamic SQL drawbacks in one fell swoop.
    First, as the query uses bind variables, subsequent executions will not be hard-parsed.
    Secondly, as the value of the input parameter is bound, the query is not injectible.

    If you’re still a bit unclear on how Dynamic SQL can be vulnerable to injection, have a look here for an example of user input injection and here for something a bit more subtle.

    For all the care we’ve taken here however, the code itself will still take some figuring out.

    Alternatives to NDS DML

    Dynamic SQL is a bit like the KERS power-boost on a Formula 1 car. You need to use it at the right time if you don’t want to end up sitting in a pile of exquisitly engineered carbon-fibre shards ( or some similarly unpleasant position).

    How can we re-write this function to produce the same result without using NDS ?

    CREATE OR REPLACE FUNCTION get_champion_nd_fn(
        i_year IN NUMBER,
        i_team_or_driver IN VARCHAR2 DEFAULT 'TEAM')
    RETURN VARCHAR2 IS
    --
    -- Non-dynamic version of get_champions function.
    -- Here, we declare two cursors but just use one of them.
    --
        l_rtn VARCHAR2(50);
    BEGIN
        --
        -- As in the original function, check the input parameters
        --
        IF i_year IS NULL THEN
            RAISE_APPLICATION_ERROR(-20000, 'No year specified.');
        END IF;
        IF UPPER(i_team_or_driver) NOT IN ('TEAM', 'DRIVER') THEN
            RAISE_APPLICATION_ERROR(-20001, 'Valid values are TEAM or DRIVER.');
        END IF;
        --
        -- Now work out which champion we need to return and execute
        -- the appropriate statement
        --
        IF UPPER(i_team_or_driver) = 'TEAM' THEN
            SELECT team INTO l_rtn
            FROM constructor_champions
            WHERE year = i_year;
        ELSE
            SELECT last_name INTO l_rtn
            FROM driver_champions
            WHERE year = i_year;
        END IF;
        RETURN l_rtn;
    END;
    /
    

    Although we’ve got two implicit cursors here (one for each table) only one of them will execute on each call.
    As ever, the fastest code is the code that doesn’t run.
    So, the overhead of writing that extra statement does not actually cause the code to be any less efficient.
    Also, you can quickly see what’s going on just by looking at the code.

    Now for another classic example of when NDS may come calling – the web application query form.
    Let’s say that we have an application which allows the user to query driver champions by the following criteria :

  • first_name
  • last_name
  • country
  • team

This sort of requriement will be common to everything from web applications to those dreaded Oracle Reports After Parameter Form triggers.
We’re not going to know what the predicate of the query needs to be until runtime. Surely NDS is the answer here ?
Well, perhaps. However, you might want to consider the following as an alternative :

CREATE OR REPLACE FUNCTION qry_champs_fn(
    i_first_name driver_champions.first_name%TYPE DEFAULT NULL,
    i_last_name driver_champions.last_name%TYPE DEFAULT NULL,
    i_country driver_champions.country%TYPE DEFAULT NULL,
    i_team driver_champions.team%TYPE DEFAULT NULL)
    RETURN SYS_REFCURSOR IS
    
    l_rc SYS_REFCURSOR;
BEGIN
    OPEN l_rc FOR 
        SELECT first_name, last_name, country, team, race_wins
        FROM driver_champions
        WHERE first_name = NVL(i_first_name, first_name)
        AND last_name = NVL(i_last_name, last_name)
        AND country = NVL(i_country, country)
        AND team = NVL(i_team, team);
    RETURN l_rc;
END;
/

The query you end up running in this example is no less efficient than the first.
The added benefit here is that :

  • You save yourself some typing
  • The code is much easier to read (and therefore maintain)
  • because PL/SQL binds the variables automagically, you don’t have to worry about injection

Conclusion

If you need to perform DDL in PL/SQL, or if you need to select against an unkown table, then NDS is probably the best
tool for the job.
Provided you use binds for any variables, you should be fine. However, bear in mind that there may be a better (saner) way of achieving the same effect.
It’s The Belgian Grand Prix this weekend. Will Hamilton triumph again ? Are Alonso and Ferrari back on form ? Will Kimi be disappointed ?
Ah, F1. Just like WWE for petrolheads.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s