Module 06: Using Dynamic SQL

Using Dynamic SQL

01. Write down the Execution Flow of SQL.

All SQL statements go through some or all of the following stages: 

a. Parse.

b. Bind.

c. Execute.

d. Fetch.

 

02. How to Working with Dynamic SQL?

Use dynamic SQL to create a SQL statement whose structure may change during run time.

Dynamic SQL: 

a. Is constructed and stored as a character string, string variable, or string expression within the application.

b. Is a SQL statement with varying column data, or different conditions with or without placeholders (bind variables).

c. Enables DDL, DCL, or session-control statements to be written and executed from PL/SQL.

d. Is executed with Native Dynamic SQL statements or the DBMS_SQL package.

03. Write down about Native Dynamic SQL (NDS).

Native Dynamic SQL (NDS):

a. Provides native support for dynamic SQL directly in the PL/SQL language. 

b. Provides the ability to execute SQL statements whose structure is unknown until execution time. 

c. If the dynamic SQL statement is a SELECT statement that returns multiple rows, NDS gives you the following choices: 

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

ii. Use the OPEN-FOR, FETCH, and CLOSE statements.

d. In Oracle Database 11g, NDS supports statements larger than 32 KB by accepting a CLOB argument.