Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Dynamic SQL in PL/SQL

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.  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.  If you want the details of how the database used bind variables, look through any Oracle database tuning book, they will cover it in detail.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.