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.

Dancing with Oracle Package with Code only

CREATE OR REPLACE PACKAGE PL_PKG AS 

--Public Variables Declaration
v_tax NUMBER;

--Public Procedures Declaration
PROCEDURE CALCULATOR;

--Public Functions Declaration
FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER;

END PLPU04;

CREATE OR REPLACE 
PACKAGE BODY PL_PKG AS

--Private Variables Declaration
V_COUNTER NUMBER DEFAULT 0;


--Forward Declaration
PROCEDURE P_SP_CALC(NUM IN NUMBER);


--Body Implementation
PROCEDURE CALCULATOR
AS
BEGIN
--Reference Resolved
P_SP_CALC(100);
V_COUNTER := V_COUNTER+1;
DBMS_OUTPUT.PUT_LINE ('V_COUNTER := '||V_COUNTER);
END CALCULATOR;


--The Implementation
PROCEDURE P_SP_CALC(NUM IN NUMBER)
AS
BEGIN
NULL;
END P_SP_CALC;

FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER IS
    V_RATE NUMBER := 0.1;
BEGIN
    RETURN (P_VALUE * V_RATE);
END TAX;


--Initialization Portion(Should be at the end of the package body)
BEGIN
V_COUNTER := 100;

END PLPU04;

DECLARE
  P_VALUE NUMBER;
  v_Return NUMBER;
BEGIN
  PL_PKG .CALCULATOR();
  PL_PKG .CALCULATOR();
  PL_PKG .CALCULATOR();
  P_VALUE := NULL;

  v_Return := PLPU04.TAX(P_VALUE => P_VALUE);
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

Forward Declarations Problem

Problem

In general, PL/SQL is like other block-structured languages and does not allow forward references. You must declare an identifier before using it. For example, a subprogram must be declared before you can call it.

NB

Coding standards often require that subprograms be kept in alphabetical sequence to make them easy to find. In this case, you may encounter problems.

Solution

You can solve the illegal reference problem by reversing the order of the two procedures. However, this easy solution does not work if the coding rules require subprograms to be declared in alphabetical order.

The solution in this case is to use forward declarations provided in PL/SQL. A forward declaration enables you to declare the heading of a subprogram, that is, the subprogram specification terminated by a semicolon.

Forward Declarations

A forward declaration may be required for private subprograms in the package body, and consists of the subprogram specification terminated by a semicolon. Forward declarations help to:

• Define subprograms in logical or alphabetical order.
• Define mutually recursive subprograms. Mutually recursive programs are programs that call each other directly or indirectly.
• Group and logically organize subprograms in a package body.

When creating a forward declaration:

• The formal parameters must appear in both the forward declaration and the subprogram body.
• The subprogram body can appear anywhere after the forward declaration, but both must appear in the same program unit.

CREATE OR REPLACE PACKAGE PL_TEST AS 

--Public Procedure Declaration
PROCEDURE CALLER;

END PLPU04;

CREATE OR REPLACE 
PACKAGE BODY PL_TEST AS
--Forward Declaration
PROCEDURE CALC(NUM IN NUMBER);

PROCEDURE CALLER
AS
BEGIN
--Reference Resolved
CALC(100);
END CALLER;

--The Implementation
PROCEDURE CALC(NUM IN NUMBER)
AS
BEGIN
NULL;
END CALC;

END PLPU04;

When to use Forward Declarations with Packages?

Typically, the subprogram specifications go in the package specification, and the subprogram bodies go in the package body. The public subprogram declarations in the package specification do not require forward declarations.

Overloading Subprograms and it's Limitations

The overloading feature in PL/SQL enables you to develop two or more packaged subprograms with the same name. Overloading is useful when you want a subprogram to accept similar sets of parameters that have different data types. For example, the TO_CHAR function has more than one way to be called, enabling you to convert a number or a date to a character string.
PL/SQL allows overloading of package subprogram names and object type methods.
The key rule is that you can use the same name for different subprograms as long as their formal parameters differ in number, order, or data type family.

Consider using overloading when:

1. Processing rules for two or more subprograms are similar, but the type or number of parameters used varies
2. Providing alternative ways for finding different data with varying search criteria. For example, you may want to find employees by their employee ID and also provide a way to find employees by their last name. The logic is intrinsically the same, but the parameters or search criteria differ.
3. Extending functionality when you do not want to replace existing code

You cannot overload(Restrictions):

1. Two subprograms if their formal parameters differ only in data type and the different data types are in the same family (NUMBER and DECIMAL belong to the same family.)
2. Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL subtypes of VARCHAR2.)
3. Two functions that differ only in return type, even if the types are in different families

You get a run-time error when you overload subprograms with the preceding features.