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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Execute Immediate
Oracle Tips by Burleson
 

The Oracle EXECUTE IMMEDIATE command is one of the easiest commands available.  The syntax is:

EXECUTE IMMEDIATE <SQL or SPL Commands>
  [INTO <variable list>]
  [USING <bind variable list>];

In its most basic form, Oracle EXECUTE IMMEDIATE takes only a single parameter and that is a command string.

Here is an example showing how to use dynamic DDL to create, drop and re-create a table:

BEGIN
  EXECUTE IMMEDIATE 'create table abcd (efgh NUMBER)';
  EXECUTE IMMEDIATE 'drop table abcd';
  EXECUTE IMMEDIATE 'create table abcd (efgh VARCHAR2(10))';
END;

You can use this method to execute any DDL.

More than likely, you will use dynamic SQL to execute DML commands more often than DDL.  With dynamic SQL you can issue inserts, updates and deletes just as you can with static SQL:

BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO abcd (efgh) VALUES (:text_string)'
         USING 'ijkl';
  EXECUTE IMMEDIATE 'INSERT INTO abcd (efgh) VALUES (:text_string)'
         USING 'mnop';
  EXECUTE IMMEDIATE 'UPDATE abcd ' ||
            'SET efgh = :text_string WHERE efgh = :second_string'
         USING 'qrst', 'mnop';
  EXECUTE IMMEDIATE 'DELETE FROM abcd ' ||
            'WHERE efgh = :text_string '
         USING 'qrst';
 

END;

As useful as DDL and DML are, a database is not very useful if you can't get your data out.  You can also use dynamic SQL to select your data back out. 

DECLARE
  v_data abcd.efgh%TYPE; 

  v_data_row abcd%ROWTYPE; 

BEGIN 

  EXECUTE IMMEDIATE 'SELECT efgh FROM abcd WHERE efgh = :text_string'
         INTO v_data
         USING 'ijkl';

  DBMS_OUTPUT.PUT_LINE( 'Column Variable: ' || v_data ); 

  EXECUTE IMMEDIATE 'SELECT * FROM abcd WHERE efgh = :text_string'
         INTO v_data_row
         USING 'ijkl'; 

  DBMS_OUTPUT.PUT_LINE( 'Row Variable: ' || v_data_row.efgh ); 

END; 

INFO:  Column Variable: ijkl
INFO:  Row Variable: ijkl

             

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

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

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter