Dynamic SQL: Don't concatenate, bind!

View All Scripts Login to Run Script

Script Name Dynamic SQL: Don't concatenate, bind!
Description When you are writing a program with dynamic SQL in it (that is, you construct your statement at runtime and execute it with EXECUTE IMMEDIATE or DBMS_SQL - most likely and preferably the former), you should make sure to bind all variable values into that statement, and not concatenate.
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday December 18, 2015

Statement 1

Local Copy of Employees

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

Here's a great example of a too-generic subprogram: update any numeric (well, I suppose it would work for strings, too) column in the employees table whose hire date falls within the specified range. I use concatenation to put it all together. That leads to lots of typing, lots of code to look out and lots of single quotes. It is unlikely that a program as generic as this will ever truly be needed!

Update Any Column In Table - Concatenation

CREATE OR REPLACE PROCEDURE updnumval ( 
   col_in     IN   VARCHAR2 
 , start_in   IN   DATE 
 , end_in     IN   DATE 
 , val_in     IN   NUMBER 
) 
IS 
   c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS'; 
BEGIN 
   EXECUTE IMMEDIATE    'UPDATE employees SET ' 
                     || col_in 
                     || ' = ' 
                     || val_in 
                     || ' WHERE hire_date BETWEEN TO_DATE (''' 
                     || TO_CHAR ( start_in, c_format ) 
                     || ''', ''' 
                     || c_format 
                     || ''') AND TO_DATE (''' 
                     || TO_CHAR ( end_in, c_format ) 
                     || ''', ''' 
                     || c_format 
                     || ''')'; 
END;

Procedure created.

Statement 3

BEGIN 
   updnumval ('salary', 
              DATE '2002-01-01', 
              DATE '2002-12-31', 
              20000); 
END;

Statement processed.

Statement 4

SELECT * 
  FROM employees 
 WHERE salary = 20000


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 20000  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 20000  - 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 20000  - 100 30
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 20000  - 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 20000  - 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 20000  - 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 20000  - 205 110

7 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	20000	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	20000	-	108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	20000	-	100	30
203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	20000	-	101	40
204	Hermann	Baer	HBAER	515.123.8888	07-JUN-02	PR_REP	20000	-	101	70
205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	20000	-	101	110
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	20000	-	205	110

Statement 5

A rewrite of the original, still full of concatenation, but now using the Q literal terminator feature to specify another character as the terminator of the literal, so I can avoid having code like ''','''. I'm not convinced this is all that much better - the benefit is more obvious when you are a large dynamic SQL statement as a single string - full of doubled-up quotes.

Well You Don't Need All Those Single Quotes

CREATE OR REPLACE PROCEDURE updnumval ( 
   col_in     IN   VARCHAR2 
 , start_in   IN   DATE 
 , end_in     IN   DATE 
 , val_in     IN   NUMBER 
) 
IS 
   c_format CONSTANT VARCHAR2 ( 100 ) := 'YYYYMMDDHH24MISS'; 
BEGIN 
   EXECUTE IMMEDIATE    'UPDATE employees SET ' 
                     || col_in 
                     || ' = ' 
                     || val_in 
                     || q'[ WHERE hire_date BETWEEN TO_DATE (']' 
                     || TO_CHAR ( start_in, c_format ) 
                     || q'[', ']' 
                     || c_format 
                     || q'[') AND TO_DATE (']' 
                     || TO_CHAR ( end_in, c_format ) 
                     || q'[', ']' 
                     || c_format 
                     || q'[')]'; 
END;

Procedure created.

Statement 6

BEGIN 
   updnumval ('salary', 
              DATE '2002-01-01', 
              DATE '2002-12-31', 
              30000); 
END;

Statement processed.

Statement 7

SELECT * 
  FROM employees 
 WHERE salary = 30000


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 30000  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 30000  - 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 30000  - 100 30
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 30000  - 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 30000  - 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 30000  - 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 30000  - 205 110

7 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	30000	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	30000	-	108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	30000	-	100	30
203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	30000	-	101	40
204	Hermann	Baer	HBAER	515.123.8888	07-JUN-02	PR_REP	30000	-	101	70
205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	30000	-	101	110
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	30000	-	205	110

Statement 8

Now I rewrite the procedure to bind everything I can possibly bind: the column value, low and high dates. My code is much simpler, easier to read, and is performance-optimized (more likely to avoid unnecessary parsing). Note that I cannot bind in the column NAME. That information is needed in order to parse the SQL statement (which comes before binding).

Switch to Binding with USING Clause

CREATE OR REPLACE PROCEDURE updnumval (col_in     IN VARCHAR2, 
                                       start_in   IN DATE, 
                                       end_in     IN DATE, 
                                       val_in     IN NUMBER) 
IS 
BEGIN 
   EXECUTE IMMEDIATE 
         'UPDATE employees SET ' 
      || col_in 
      || ' = :val  
        WHERE hire_date BETWEEN :lodate AND :hidate' 
      USING val_in, start_in, end_in; 
END;

Procedure created.

Statement 9

BEGIN 
   updnumval ('salary', 
              DATE '2002-01-01', 
              DATE '2002-12-31', 
              40000); 
END;

Statement processed.

Statement 10

SELECT * 
  FROM employees 
 WHERE salary = 40000


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 40000  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 40000  - 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 40000  - 100 30
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 40000  - 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 40000  - 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 40000  - 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 40000  - 205 110

7 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
108	Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	40000	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	40000	-	108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	40000	-	100	30
203	Susan	Mavris	SMAVRIS	515.123.7777	07-JUN-02	HR_REP	40000	-	101	40
204	Hermann	Baer	HBAER	515.123.8888	07-JUN-02	PR_REP	40000	-	101	70
205	Shelley	Higgins	SHIGGINS	515.123.8080	07-JUN-02	AC_MGR	40000	-	101	110
206	William	Gietz	WGIETZ	515.123.8181	07-JUN-02	AC_ACCOUNT	40000	-	205	110

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database