What is dynamic SQL?

What is dynamic SQL?

Rating:

Most PL/SQL programs perform a specific, predictable job. For example, a stored procedure may accept an employee number and salary increase and then update the Salary column of the Employees table. In this case, the full text of the UPDATE statement is known at compile time. Such statements do not change from execution to execution. Hence, they are called static SQL statements.

However, some programs need to build and process a variety of SQL statements at run time. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the full text of the statement is not known until run time. Such statements may change from execution to execution. Hence, they are called dynamic SQL statements.

Dynamic SQL statements are not embedded in the source program. They are stored as character strings that are input to or built by the program. In other words, the SQL statements can be created dynamically at run-time by using variables. For example, the dynamic SQL can be used to create a procedure that operates on a table whose name is not known until run-time.

Dynamic SQL programs can handle changes in data definitions, without the need to recompile. This makes dynamic SQL much more flexible than static SQL. Dynamic SQL allows users to write reusable code because the SQL can be easily adapted for different environments. Dynamic SQL also allows executing Data Definition Language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.

Oracle provides two methods for using dynamic SQL within PL/SQL: native dynamic SQL and the DBMS_SQL Oracle supplied package. Native dynamic SQL allows placing dynamic SQL statements directly into PL/SQL code. These dynamic statements include DML statements (including queries), PL/SQL anonymous blocks, DDL statements, transaction control statements, and session control statements.

In order to process most dynamic SQL statements, the EXECUTE IMMEDIATE statement is used. The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. Also, this is used for functionality such as objects and collections, which are not supported by DBMS_SQL. However, in order to process a multi-row query (SELECT statement), the OPEN-FOR, FETCH, and CLOSE statements are used.

The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically. The DBMS_SQL package has procedures to open a cursor, parse a cursor, supply binds, and so on. Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations.

Rating: