A note on Dynamic SQL and its implementation in PL/SQL Application
Oracle allows all purpose and flexible applications to be built with the concept of Dynamic SQL . The full text of a SQL statement may not be known at the time of compiling the program. Dynamic SQL allows the user to construct SQL statements as character strings at runtime. These strings hold the text of a SQL statement or PL/SQL block and also may include placeholders for bind variables. Dynamic SQL can be used in several development environments such as PL/SQL, Pro*C/C++ or Java.
Oracle Database enables the user to implement dynamic SQL in a PL/SQL application either by using native dynamic SQL that consists of placing dynamic SQL statements directly into PL/SQL blocks or by calling procedures in the DBMS_SQL package.
Static SQL provides the benefits of successful compilation verification of SQL statements referencing valid database objects and to check whether the privileges to access the objects are available by the user. The performance of static SQL is also better. However Static SQL cannot be used, if full text of SQL statement is not known and depends on user’s input or program’s processing logic.
The DDL statements such as CREATE,DROP,GRANT and REVOKE and SCL statements such as ALTER SESSION and SET ROLE and usage of the TABLE clause in the SELECT statements can be executed only by dynamic SQL within PL/SQL program unit. In the usage of dynamic queries, dynamic SQL allows users to input or to decide query search or sorting criteria at runtime, allow users to input or select optimizer hints at runtime, query database where data definitions of tables are continuously changing and where new tables are frequently created.
They can be used to refer database objects that do not exist at the time of compilation. For example a system may generate new tables on a quarterly basis and the table names may be denoted with the year and quarter suffixed. Based on the quarter and year in which the program is used, the table name can be generated dynamically at runtime.
Execution can be optimized directly by the process of concatenating strings in the SQL statement dynamically based on the status of current database statistics. Dynamic PL/SQL blocks can be executed by the EXECUTE IMMEDIATE statement, that is by using native dynamic SQL at runtime. By means of the invoker’s rights feature with dynamic SQL, applications can be built that issue dynamic SQL statements under the privileges and schema of the invoker.
There are two methods provided by Oracle Database for using dynamic SQL within PL/SQL, which are native dynamic SQL and the DBMS_SQL Package.
DBMS_SQL package on the other hand offers a PL/SQL API to execute dynamic SQL statements. The package contains procedures to open a cursor, parse a cursor and supply binds.
The DBMS_SQL package provides its own advantages over native dynamic SQL , such as it is supported in Client-Side Programs, supports statements with unknown number of inputs and outputs, supports SQL statements that are larger than 32KB and allows re usage of SQL statements.
The example below shows the usage of a simple query statement. It is to query for employees with the job description as ANALYST in the job column of the EMP table.
DBMS_SQL Query Operation
Dynamic SQL can thus be a time saver by helping to automate recurring tasks, prepare code that is flexible in any server or database and dynamically adjust the code to changing conditions.
| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |