Oracle Dynamic SQL Tips Oracle Database Tips by Donald Burleson

We will discuss about the below topics in detail:
 
1.       Introduction to Dynamic SQL, its different types and its main use in programming.
 
2.       Understanding the Native Dynamic SQL methodology with various real time examples and latest enhancements.
 
3.       Dusting the attention less DBMS_SQL API and its associated subprograms with supporting suitable examples along with their special functionality that is dependent only to this API.
 
Dynamic SQL is a power-packed programming methodology that allows us to generate and execute SQL statements and PL/SQL blocks at run time. During the compile time, the dynamic SQL statements are considered as a mere character string and will not be validated against the objects in the database, providing a greater advantage to the programmers with the flexibility of re-arranging the program, based on the business requirements during the run time. The process of using the SQL statements that are not embedded in our source program and are constructed as strings which are parsed and executed at run time, is called as Dynamic SQL processing technique.
 
This method of programming is mainly useful when,
 
1.       Writing unpredictable and ad-hoc programs.
 
2.       When there is a need for the PL/SQL program units to process data definition language (DDL) statements and data control language (DCL) statements
 
3.       When a part of an SQL statement or a PL/SQL block like table name, column name, number of parameters, and data type of the input and the output variables are not known during the compile time.
 
The below figure shows the basic idea of processing a dynamic query.
                   
Fig.4.1 Dynamic Query Processing Technique Illustration

There are two methods in which any dynamic SQL can be processed. They are, Native Dynamic SQL language and DBMS_SQL API that are explained below

There are many times when a SQL statement needs to be built and executed dynamically. When using SQL*Plus creating and executing dynamic SQL is a fairly simple task; create a script that generates the SQL, and then run the script. The script below will create the commands to truncate all the tables in the PUBS schema.

set pages 0 line 132 feedback off trim on
spool /opt/script/truncate.sql
select
'truncate table '||tablename||';'
from user_tables;
spool off;
set pages 999 feedback on
@/opt/script/truncate.sql

In PL/SQL the SQL statement is created as a string, which is then executed using the EXECUTE IMMEDIATE clause .

SQL> declare
2 v_str varchar2(200);
3 begin
4 for r_c1 in (select * from user_tables) loop
5 v_str:= 'delete '||r_c1.table_name;
6 dbms_output.put_line(v_str);
7 execute immediate v_str;
8 end loop;
9 end;
10 /

delete AUTHOR
delete EMP
delete JOB
delete PUBLISHER
delete SALES
delete STORE
delete BOOK_AUTHOR
delete BOOK

SQL> rollback

Notice that you still need to execute a commit to make the changes permanent. The ROLLBACK statement in the above example returns the deleted rows to the tables.

The EXECUTE IMMEDIATE clause allows the application to build different SQL statements based on user input or application errors. It also allows the developer to dynamically tune the application. A report may require a set of indexes to operate efficiently; however, these indexes may harm database performance if left in the database all the time. Using dynamic SQL, the application can build the indexes, run the report, and then delete the indexes upon completion.

SQL> begin
2 execute immediate 'create index ln_idx
3 on author(author_last_name)';
4 execute immediate 'create index fn_idx
5 on author(author_first_name)';
6 -- run_big_report;
7 execute immediate 'drop index ln_idx';
8 execute immediate 'drop index fn_idx';
9 end;
10 /

Dynamic SQL is easy to implement and very powerful. When creating database objects, such as shown in the example above, be careful that the cost of creating the object is not greater than the cost of running the report without the object.

There is one significant issue with using dynamic SQL. A dynamical SQL statement submitted to the database using EXECUTE IMMEDIATE does not use bind variables. SQL statements in PL/SQL blocks automatically use bind variables, but dynamic SQL cannot. Bind variables allow the database to reuse stored execution plans. Never use dynamic SQL when a normal SQL statement will work.

The above book excerpt is from the book Easy Oracle PL/SQL Programming, by John Garmany.