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.

It cannot be used in the case that certain DDL statements not supported by static SQL are to be executed or program needs to handle changes in data definitions without requirement to recompile. As a result to overcome these difficulties, dynamic SQL is used to execute DDL (Data Definition Language)and SCL statements (Session Control Language) in PL/SQL , to execute dynamic queries, reference database objects that may not exist at the time of compilation, executing dynamic PL/SQL blocks, performing dynamic operations using invoker’s rights and to optimize execution dynamically. The details regarding the mentioned features are as below.

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.

Native dynamic SQL allows dynamic SQL statements to be built directly into PL/SQL code. These dynamic statements can be of queries and DML statements, PL/SQL anonymous blocks, DDL statements, Transaction Control Statements or Session Control Statements. For most of these statements EXECUTE IMMEDIATE command is used. For multi row SELECT statement the corresponding cursor based commands that are OPEN-FOR, FETCH and CLOSE statements are used.

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 advantages of native dynamic SQL over DBMS_SQL are that it provides ease of use, faster, supports User-Defined types, supports Fetching into Records.

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
DECLARE
x_stmt_str VARCHAR2(200);
x_cur_hdl INT;
x_rows_processed INT;
x_name VARCHAR2(10);
x_salary INT;
BEGIN
x_cur_hdl := DBMS_SQL.OPEN_CURSOR; -- open cursor
x_stmt_str := 'SELECT ename, sal FROM emp
WHERE job = :y_jobname';
DBMS_SQL.PARSE(x_cur_hdl,x_stmt_str,DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE(x_cur_hdl, 'y_jobname','ANALYST');
-- Describe defines
DBMS_SQL.DEFINE_COLUMN(x_cur_hdl, 1, x_name, 200);
DBMS_SQL.DEFINE_COLUMN(x_cur_hdl, 2, x_salary);
-- Execute
x_rows_processed := DBMS_SQL.EXECUTE(x_cur_hdl);
LOOP
-- Fetch a row
IF DBMS_SQL.FETCH_ROWS(x_cur_hdl) > 0 THEN
-- Fetch columns from the row
DBMS_SQL.COLUMN_VALUE(x_cur_hdl, 1, x_name);
DBMS_SQL.COLUMN_VALUE(x_cur_hdl, 2, x_salary);
-- Process
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(x_cur_hdl); -- close cursor
END;
/
Native Dynamic SQL Query Operation
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
x_emp_cursor EmpCurTyp;
x_stmt_str VARCHAR2(200);
x_name VARCHAR2(20);
x_salary NUMBER;
BEGIN
x_stmt_str:= 'SELECT ename, sal
FROM emp
WHERE job = :1';
OPEN x_emp_cursor FOR x_stmt_str
USING 'ANALYST';
LOOP
FETCH x_emp_cursor
INTO x_name, x_salary;
EXIT WHEN x_emp_cursor%NOTFOUND;
-- Process data
END LOOP;
CLOSE x_emp_cursor;
END;
/

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 |