Dynamic SQL in PL/SQL

What is Dynamic SQL?

     Dynamic SQL is a programming methodology for generating and running SQL statements at run time.It will be helpful when you do not know the full text of a SQL statement or the number or data types of its input and output variables at  compilation time.

Advantages of Dynamic SQL:

Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL.

There are 2 ways to write Dynamic SQL. 

1.       Native dynamic SQL :

              Native dynamic SQL processes most dynamic SQL statements with the EXECUTEIMMEDIATE statement.

You can use EXECUTE IMMEDIATEstatement with the BULK COLLECTINTO clause and And  you can Use the OPEN FOR, FETCH, and CLOSE statements.

Example for Dynamic SQL for Anonymous PL/SQL block :

Procedure to create employee name:  

 

 

PL/SQL Block which is Invoking the subprogram:

                               

    

1.       DBMS_SQL Package:

        

   The DBMS_SQLpackage defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it.

You must use the DBMS_SQL package to run a dynamic SQL statement when you do not know SELECT list OR What placeholders in a SELECT.

Example using DBMS_SQL Package.

When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using

DBMS_SQL.TO_REFCURSOR Function

DBMS_SQL.TO_CURSOR_NUMBER Function

Difference  Between Native Dynamic SQL and DBMS_SQL package

Native Dynamic SQL :

·         Supports fetching into records

·         Supports user-defined types

·         Directly we can include sql code into our query or  procedure using EXECUTE IMMEDIATE

·         using native dynamic sql we can directly use DML and DDL

DBMS_SQL Package:

·         It offers an API that execute SQL statement dynamically.

·         we can use dml and DDL but here we have to  declare cursor so coding is lengthy

·         Supports SQL Statements Larger than 32KB

·         Supports Multiple Row Updates and Deletes with a RETURNING Clause

·         lets you reuse your statements