Swimming Inside Dynamic SQL using Native Dynamic SQL (NDS) & DBMS_SQL Package

In PL/SQL, dynamic SQL executes the following SQL statements where the full text is unknown at compile time such as:

• A SELECT statement that includes an identifier that is unknown at compile time (such as a table name).
• A WHERE clause in which the column name is unknown at compile time.

Native Dynamic SQL
Native Dynamic SQL provides the ability to dynamically execute SQL statements whose structure is constructed at execution time.
The EXECUTE IMMEDIATE statement: can be used to execute SQL statements or PL/SQL anonymous blocks.

Method 1:

This method lets your program accept or build a dynamic SQL statement, and then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables.

CREATE OR REPLACE PROCEDURE create_table(
  p_table_name VARCHAR2, p_col_specs  VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 
   'CREATE TABLE ' || p_table_name || ' (' || p_col_specs || ')';
END;
/

BEGIN
  create_table('EMPLOYEE_NAMES',
   'id NUMBER(4) PRIMARY KEY, name VARCHAR2(40)');
END;
/

CREATE OR REPLACE PROCEDURE add_col(p_table_name VARCHAR2,
                         p_col_spec VARCHAR2) IS
  v_stmt VARCHAR2(100) := 'ALTER TABLE ' || p_table_name ||
                        ' ADD '|| p_col_spec;
BEGIN
  EXECUTE IMMEDIATE v_stmt;
END;
/

EXECUTE add_col('employee_names', 'salary number(8,2)')

Method 2: 

This method lets your program accept or build a dynamic SQL statement, and then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the data types of the input host variables must be known at precompile time.

CREATE PROCEDURE add_row(p_table_name VARCHAR2,
   p_id NUMBER, p_name VARCHAR2) IS
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO '|| p_table_name||
        ' VALUES (:1, :2)' USING p_id, p_name;
END;
/

CREATE FUNCTION del_rows(p_table_name VARCHAR2)
RETURN NUMBER IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM '|| p_table_name;
  RETURN SQL%ROWCOUNT;
END;
/

SET SERVEROUTPUT ON

BEGIN
  DBMS_OUTPUT.PUT_LINE(del_rows('EMPLOYEE_NAMES')|| ' rows deleted.');
END;
/

With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables. SQL data definition statements such as CREATE and GRANT are executed when they are prepared. 

Method 3:

This method lets your program accept or build a dynamic query, and then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the data types of the input host variables must be known at precompile time.

CREATE OR REPLACE FUNCTION get_emp(p_emp_id NUMBER)
RETURN employees%ROWTYPE IS
  v_stmt VARCHAR2(200);
  v_emprec employees%ROWTYPE;
BEGIN
  v_stmt := 'SELECT * FROM employees ' ||
          'WHERE employee_id = :id';
  EXECUTE IMMEDIATE v_stmt INTO v_emprec USING p_emp_id;

  RETURN v_emprec;
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_emprec employees%ROWTYPE := get_emp(100);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Emp: '|| v_emprec.last_name);
END;
/

Method 4:

This method lets your program accept or build a dynamic SQL statement, and then process it using descriptors. A descriptor is an area of memory used by your program and Oracle to hold a complete description of the variables in a dynamic SQL statement. The number of select-list items, the number of placeholders for input host variables, and the data types of the input host variables can be unknown until run time. 

CREATE OR REPLACE FUNCTION annual_sal( p_emp_id NUMBER)
RETURN NUMBER IS
  v_plsql varchar2(200) :=
    'DECLARE '||
    ' rec_emp employees%ROWTYPE; '||
    'BEGIN '||
    ' rec_emp := get_emp(:empid); ' ||
    ' :res := rec_emp.salary  * 12; ' ||
    'END;';
  v_result NUMBER;
BEGIN
 EXECUTE IMMEDIATE v_plsql
         USING IN p_emp_id, OUT v_result;
  RETURN v_result;
END; 
/

SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.PUT_LINE(annual_sal(100))

Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables. With this method, you use the DBMS_SQL package, which is covered later in this lesson. Situations that require using Method 4 are rare. 

Using the DBMS_SQL Package Subprograms

The DBMS_SQL package provides the following subprograms to execute dynamic SQL:

• OPEN_CURSOR to open a new cursor and return a cursor ID number
• PARSE to parse the SQL statement. Every SQL statement must be parsed by calling the PARSE procedures. Parsing the statement checks the statement’s syntax and associates it with the cursor in your program. You can parse any DML or DDL statement. DDL statements are immediately executed when parsed.
• BIND_VARIABLE to bind a given value to a bind variable identified by its name in the statement being parsed. This is not needed if the statement does not have bind variables.
• EXECUTE to execute the SQL statement and return the number of rows processed
• FETCH_ROWS to retrieve the next row for a query (use in a loop for multiple rows)
• CLOSE_CURSOR to close the specified cursor

CREATE OR REPLACE PROCEDURE insert_row (p_table_name VARCHAR2,
  p_id VARCHAR2, p_name VARCHAR2, p_region NUMBER) IS
  v_cur_id     INTEGER;
  v_stmt       VARCHAR2(200);
  v_rows_added NUMBER;

BEGIN
  v_stmt := 'INSERT INTO '|| p_table_name ||
          ' VALUES (:cid, :cname, :rid)';
  v_cur_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur_id, v_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cid', p_id);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':cname', p_name);
  DBMS_SQL.BIND_VARIABLE(v_cur_id, ':rid', p_region);
  v_rows_added := DBMS_SQL.EXECUTE(v_cur_id);
  DBMS_SQL.CLOSE_CURSOR(v_cur_id);
  DBMS_OUTPUT.PUT_LINE(v_rows_added ||' row added');
END;
/

SET SERVEROUTPUT ON
EXECUTE insert_row('countries', 'LB', 'Lebanon', 4)

Note: Using the DBMS_SQL package to execute DDL statements can result in a deadlock. For example, the most likely reason is that the package is being used to drop a procedure that you are still using.