Examples of Native Dynamic SQL Method 2

View All Scripts Login to Run Script

Script Name Examples of Native Dynamic SQL Method 2
Description Method 2 means your dynamically-constructed SQL statement in a non-query DML (insert, update, delete, merge) with a fixed number of bind variables. That is, at the time you are writing your code, you know how many variables you must bind into placeholders.
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday March 10, 2016

Statement 1

Copy Table from HR to Change It

CREATE TABLE employees 
AS 
   SELECT * FROM hr.employees

Table created.

Statement 2

This is dynamic SQL method 3, by the way: a SELECT with fixed number of bind variables and/or SELECT list elements (INTO clause). We take basic steps to guard against SQL injection with the DBMS_ASSERT package. But there is still a GAPING HOLE with the WHERE clause. Thus, this program should never be callable directly by a user, with un-checked user input.

Silly Little Helper Function

CREATE OR REPLACE FUNCTION table_count (   
   table_name_in   IN all_tables.table_name%TYPE,  
   where_in        IN VARCHAR2 DEFAULT NULL)  
   RETURN PLS_INTEGER  
   AUTHID CURRENT_USER  
IS  
   l_table_name   all_tables.table_name%TYPE;  
   l_return       PLS_INTEGER;  
BEGIN  
   l_table_name := sys.DBMS_ASSERT.sql_object_name (table_name_in);  
   EXECUTE IMMEDIATE  
      'SELECT COUNT(*) FROM ' || table_name_in || ' WHERE ' || where_in  
      INTO l_return;  
  
   RETURN l_return;  
END;

Function created.

Statement 3

First of all, remember: you can only bind variable values. You cannot bind parts of the SQL statement that are needed at the time the statement is parsed, such as the table name, column name, where clause, etc. This code will compile, but fail to run.

Can't Bind Column Names

CREATE OR REPLACE PROCEDURE set_to_10000 (col_in             IN VARCHAR2, 
                                          department_id_in   IN PLS_INTEGER) 
IS 
   l_update CONSTANT VARCHAR2 (1000) :=  
      'UPDATE employees SET :colname = 10000  
        WHERE department_id = :dept'; 
BEGIN 
   EXECUTE IMMEDIATE l_update USING col_in, department_id_in; 
 
   DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); 
END;

Procedure created.

Statement 4

Can't Bind Column Names - In Action

BEGIN 
   set_to_10000 ('salary', 50); 
/* ORA-01747: invalid user.table.column, table.column, or column specification */ 
END;

ORA-01747: invalid user.table.column, table.column, or column specification ORA-06512: at "SQL_ZMXEDLYQPZKPQGLNZCWHRGSTM.SET_TO_10000", line 8
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-01747

Statement 5

Now I concatenate in the name of the column - which introduces the possibility of SQL injection, so watch out! I bind the department ID. Each placeholder - in them form :name, where name could also be an integer value - must have a corresponding value in the USING clause.

Bind Single Variable

CREATE OR REPLACE PROCEDURE set_to_10000 (col_in             IN VARCHAR2, 
                                          department_id_in   IN PLS_INTEGER) 
IS 
   l_update   VARCHAR2 (1000) 
      :=    'UPDATE employees SET ' 
         || col_in 
         || ' = 10000  
        WHERE department_id = :dept'; 
BEGIN 
   EXECUTE IMMEDIATE l_update USING department_id_in; 
 
   DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); 
END;

Procedure created.

Statement 6

BEGIN 
   DBMS_OUTPUT.put_line ( 
         'Before ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
 
   set_to_10000 ('salary', 50); 
 
   DBMS_OUTPUT.put_line ( 
         'After ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
   ROLLBACK; 
END;

Before 0
Rows updated: 45
After 45

Statement 7

I have two placeholders with different names. I therefore have two expressions in the USING clause.

Multiple Placeholders, Different Names

CREATE OR REPLACE PROCEDURE updnumval (col_in             IN VARCHAR2, 
                                       department_id_in   IN PLS_INTEGER, 
                                       val_in             IN NUMBER) 
IS 
   l_update   VARCHAR2 (1000) 
      :=    'UPDATE employees SET ' 
         || col_in 
         || ' = :val  
        WHERE department_id = :dept'; 
BEGIN 
   EXECUTE IMMEDIATE l_update USING val_in, department_id_in; 
 
   DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); 
END;

Procedure created.

Statement 8

BEGIN 
   DBMS_OUTPUT.put_line ( 
         'Before ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
 
   updnumval ('salary', 50, 10000); 
 
   DBMS_OUTPUT.put_line ( 
         'After ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
   ROLLBACK; 
END;

Before 0
Rows updated: 45
After 45

Statement 9

Now I have three placeholders and the name "val" is used twice. When I execute a dynamic SQL statement, I must have an expression in the USING clause for each placeholder - by position, not name. So you see three variables, including val_in twice. Of course, I could use a different expression for the second "val" placeholder. They are NOT connected by name.

Three Placeholders, Repeating Names

CREATE OR REPLACE PROCEDURE updnumval (col_in             IN VARCHAR2, 
                                       department_id_in   IN PLS_INTEGER, 
                                       val_in             IN NUMBER) 
IS 
   l_update   VARCHAR2 (1000) 
      :=    'UPDATE employees SET ' 
         || col_in 
         || ' = :val  
        WHERE department_id = :dept and :val IS NOT NULL'; 
BEGIN 
   EXECUTE IMMEDIATE l_update USING val_in, department_id_in, val_in; 
 
   DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); 
END;

Procedure created.

Statement 10

BEGIN 
   DBMS_OUTPUT.put_line ( 
         'Before ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
 
   updnumval ('salary', 50, 10000); 
 
   DBMS_OUTPUT.put_line ( 
         'After ' 
      || table_count ('employees', 
                      'department_id = 50 AND salary = 10000')); 
   ROLLBACK; 
END;

Before 0
Rows updated: 45
After 45

Additional Information

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