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

 

 

   
 

EnterpriseDB: Dynamic SQL
Oracle Tips by Burleson
 

Dynamic SQL is SQL, either DML or DDL, that is not completely known to the database until runtime.  It may be dynamic because you don't have all of the criteria until runtime or it may be that you want to achieve maximum reusability from your code.  Either way, dynamic SQL is very powerful.  Dynamic SQL can also be very hard to maintain so it's important to only use dynamic SQL where your programs really benefit from the added complexity.

There are two types of dynamic SQL that are used in SPL:  dynamic ref cursors and execute immediate.

Dynamic Ref Cursors

A dynamic ref cursor has a query string assigned to it at run time.  The steps to using a dynamic ref cursor are to create a weakly typed ref cursor and then build the SQL statement.  You associate the query string to the ref cursor replacing the static query after the FOR keyword with the query string:

DECLARE

  v_curs SYS_REFCURSOR; 

  v_query_string VARCHAR2(200); 

  v_ename emp.ename%TYPE;

BEGIN 

  v_query_string := 'SELECT ename FROM emp'; 

  OPEN v_curs FOR v_query_string;
  FETCH v_curs INTO v_ename;
  CLOSE v_curs; 

  DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || v_ename ); 

END; 

INFO:  Emp Name: SMITH

You can associate variables with the query by giving them a name and preceding them with a colon (:).   This is called a bind variable.  The variable will be bound to the query at runtime.

DECLARE

  v_curs SYS_REFCURSOR; 

  v_query_string VARCHAR2(200); 

  v_ename emp.ename%TYPE; 

BEGIN 

  v_query_string := 'SELECT ename FROM emp WHERE empno = :employee_number'; 

  OPEN v_curs FOR v_query_string USING 7788;
  FETCH v_curs INTO v_ename;
  CLOSE v_curs;

  DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || v_ename ); 

END; 

INFO:  Emp Name: SCOTT

You can loop through a dynamic ref cursor in the same manner as a static cursor.  As a matter of a fact, you can use any of the features available for a static cursor with a dynamic ref cursor. 

Ref cursors allow you to dynamically run select statements.  They are the way to go if you are passing data to external programs or if you are looping through the data.  The down side to dynamic ref cursors is that they do not allow you to run dynamic DDL.  There is a second way to create dynamic statements that does allow DDL.  This method is called "execute immediate".

             

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