Examples of Native Dynamic SQL Method 2

View All Scripts Login and 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.
Category PL/SQL General
Contributor Steven Feuerstein (Oracle)
Created Thursday March 10, 2016

Statement 1

Copy Table from HR to Change It

CREATE TABLE employees
AS
   SELECT * FROM hr.employees

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;

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;

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;

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;

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;

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;

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;

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;

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;

Additional Information

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