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
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; ...
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.
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 :
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
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.