Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Dynamic SQL Tips

Oracle Database Tips by Donald Burleson
Oracle PL/SQL allows for dynamic SQL to be executed by building an execution string.  The programmer actually has two methods for executing a SQL statement within PL/SQL, the embedded method which we have been using throughout the book, and implementing dynamic SQL.

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
'truncate table '||tablename||';'
from user_tables;
spool off;
set pages 999 feedback on

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 SALES
delete STORE
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.


Oracle dynamic DDL

You can also execute DDL dynamically within PL/SQL:
Dynamic SQL Question:
I'm having a problem with dynamic SQL, specifically getting my incoming parameter into the SQL statement. I am using Oracle 9I and this procedure will be part of a package. I have a dot Net front end that will generate the WHERE portion of statement and pass it in.
Here's the code:
  2  IS
  3   CURSOR curGetRecs
  4   IS
  5    SELECT InstCode, InstructCode, ProgTitle, DegCode, CIP,
  6    SUBSTR(CIP,1,2) CIP2,
  7    NVL(Deg_Year1, 0) Deg_Year1,
  8    NVL(Deg_Year2, 0) Deg_Year2,
  9    NVL(Deg_Year3, 0) Deg_Year3,
 10    NVL(Deg_Year4, 0) Deg_Year4,
 11    NVL(Deg_Year5, 0) Deg_Year5,
 12   (Deg_Year1+Deg_Year2+Deg_Year3+Deg_Year4+Deg_Year5)         Degree_Total,
 13   (Deg_Year1+Deg_Year2+Deg_Year3+Deg_Year4+Deg_Year5/5)
 14    NVL(Enr_Year1, 0) Enr_Year1,
 15    NVL(Enr_Year2, 0) Enr_Year2,
 16    NVL(Enr_Year3, 0) Enr_Year3,
 17    NVL(Enr_Year4, 0) Enr_Year4,
 18    NVL(Enr_Year5, 0) Enr_Year5,
 19    (Enr_Year1+Enr_Year2+Enr_Year3+Enr_Year4+Enr_Year5)          Enroll_Total,
 20    (Enr_Year1+Enr_Year2+Enr_Year3+Enr_Year4+Enr_Year5/5)
 21    LoPro, InstName, AcadYear
 22    FROM ods.v_5YearProductivity; -- || sWHERE_in;
 26   BEGIN
 27   OPEN curGetRecs;
 28   FETCH curGetRecs INTO v_Prod5;
 29   WHILE curGetRecs%FOUND
 30   LOOP
 32   VALUES v_Prod5;
 33   END LOOP;
 34   CLOSE curGetRecs;
This compiles with the WHERE statement commented out (line 22), but when I include it, I get the following two error messages: 
-------- --------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
22/32    PL/SQL: ORA-00933: SQL command not properly ended
I realize I'm fairly new to Oracle, but this shouldn't be that difficult!

Thanks for any help you can provide.
This question posed on 23 January 2006

Maybe this example will help:
create or replace function get_count (tab_name varchar2) return number as
cur integer;
sql_text varchar2(200);
rowcount NUMBER;
row_proc integer;
      sql_text:='select count(*) X from '||tab_name;
      return rowcount;

You have to send the SQL text to be created as a CURSOR you can't start with a cursor and then modify the statement.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.