Notes on Using Dynamic SQL in PL/SQL

Objectives

• Recall the stages through which all SQL statements pass.
• Describe the reasons for using dynamic SQL to create a SQL statement.
• List four PL/SQL statements supporting Native Dynamic SQL.
• Describe the benefits of EXECUTE IMMEDIATE over DBMS_SQL for Dynamic SQL.

Purpose

• Learn to construct and execute SQL statements dynamically at runtime using Native Dynamic SQL statements in PL/SQL.
• Dynamically executing SQL and PL/SQL code extends PL/SQL capabilities beyond static query and transactional operations.
• Compare Native Dynamic SQL to DBMS_SQL package, which provides similar capabilities.

Execution Flow of SQL

1. Stages of SQL Execution:

   • Parse: Syntax checking, object existence, privileges.
   • Bind: Getting actual values of any referenced variables.
   • Execute: The statement is executed.
   • Fetch: Results returned to the user.
   • Note: Fetch is not applicable for all types of statements (e.g., not for DML).

2. PL/SQL Subprogram Execution:

   • During PL/SQL compilation, SQL statement parsing and binding occur at compile time.
   • Problem arises when SQL text is not known at create-time.

Dynamic SQL

• Definition: SQL statements whose text is not known in advance, constructed and stored as character strings within subprograms.
• Purpose: Alter SQL statement structure at runtime, access DDL statements, and execute varied SQL functionality in PL/SQL.

Native Dynamic SQL (NDS)

• PL/SQL does not allow direct execution of DDL statements in a program.
• NDS allows SQL construction and storage as character strings within subprograms to execute operations like DDL, DML, etc.
• Key Points:
  • Execute with statements such as EXECUTE IMMEDIATE.
  • Supports the use of OPEN-FOR, FETCH, and CLOSE PL/SQL statements.

Using the EXECUTE IMMEDIATE Statement

• Syntax:

  EXECUTE IMMEDIATE dynamic_string [INTO {define_variable [, define_variable] ... | record}] [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_argument] ... ];

• Components:
  • dynamic_string: Character variable/literal containing SQL text.
  • define_variable: PL/SQL variable for selected column values.
  • record: User-defined or %ROWTYPE record for storing selected rows.
  • bind_argument: Expressions passed to dynamic SQL at execution time.

Examples of Dynamic SQL

1. DDL Statement Example:

   • Procedure to drop a table with dynamic SQL based on user input.
   • Syntax structure:

    CREATE PROCEDURE drop_any_table(p_table_name VARCHAR2) IS
        v_dynamic_stmt VARCHAR2(50);
    BEGIN
        v_dynamic_stmt := 'DROP TABLE ' || p_table_name;
        EXECUTE IMMEDIATE v_dynamic_stmt;
    END;
   

2. DML Statement Example (Delete):

   • Deleting rows from a table and returning count:

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

3. DML Statement Example (Insert):
   • Inserting a row with dynamic SQL:

   CREATE PROCEDURE add_row(p_table_name VARCHAR2, id NUMBER, p_name VARCHAR2) IS
       BEGIN
           EXECUTE IMMEDIATE 'INSERT INTO ' || p_table_name || ' VALUES (' || id || ', ''' || p_name || ''')';
       END;

4. Recompiling PL/SQL Code Example:
   • Procedure that recompiles a PL/SQL object entered at runtime by the user:

   CREATE PROCEDURE compile_plsql(p_name VARCHAR2, p_type VARCHAR2) IS
       v_stmt VARCHAR2(200);
   BEGIN
       v_stmt := 'ALTER ' || p_type || ' ' || p_name || ' COMPILE';
       EXECUTE IMMEDIATE v_stmt;
   END;

Using the DBMS_SQL Package

• Some key procedures/functions: OPENCURSOR, PARSE, BINDVARIABLE, EXECUTE, FETCHROWS, CLOSECURSOR.
• DML Statements With DBMS_SQL:
  • Alternates to dynamic SQL (e.g., delrows implemented using DBMSSQL). Note the simplicity of using EXECUTE IMMEDIATE compared to DBMS_SQL.

Comparison of Native Dynamic SQL and DBMS_SQL Package

• Native Dynamic SQL Advantages:
  • Easier to use than DBMS_SQL.
  • Requires less code.
  • Often executes faster due to reduced statement overhead.

Key Terminology

• Native Dynamic SQL: Dynamic SQL functionalities supported natively within PL/SQL.
• EXECUTE IMMEDIATE: Command used to execute dynamic SQL statements immediately.