Jijo K Jose

PL/SQL Dynamic SQL

To execute DDL commands we need Dynamic SQL. Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

•     Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements

•     DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.

When You Need Dynamic SQL

In PL/SQL, you need dynamic SQL to run:

•     SQL whose text is unknown at compile time

    For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.

•     SQL that is not supported as static SQL

If you do not need dynamic SQL, use static SQL, which has these advantages:

•     Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.
•     Successful compilation creates schema object dependencies.
•     For information about schema object dependencies, see Oracle Database Advanced Application Developer's Guide.

Native dynamic SQL

Native dynamic SQL processes most dynamic SQL statements with the EXECUTE IMMEDIATE statement.

If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:

•     Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.

•     Use the OPEN FOR, FETCH, and CLOSE statements.

The SQL cursor attributes work the same way after native dynamic SQL INSERT, UPDATE, DELETE, MERGE, and single-row SELECT statements as they do for their static SQL counterparts. For more information about SQL cursor attributes, see "Cursors".

EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements.If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind variables and the only result that it can possibly return is an error), then the EXECUTE IMMEDIATE statement needs no clauses.

Example

create or replace
PROCEDURE TMP_STUDENT_PLSQL AS
BEGIN

    BEGIN
      EXECUTE IMMEDIATE ('CREATE TABLE STUDENT AS SELECT * FROM TMP_STUDENT');
    END TMP_CREATE_TABLE_PL;
    
    BEGIN
      EXECUTE IMMEDIATE ('TRUNCATE TABLE STUDENT');
    END TMP_TRUNCATE_TABLE_PL;
    
    BEGIN
      EXECUTE IMMEDIATE ('DROP TABLE STUDENT');
    END TMP_DROP_TABLE_PL;
    
END;