HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Oracle dynamic code buffer
ORACLE STUFFS
18-Aug-2008
To generate and execute dynamic SQL or PL/SQL code there is usefull buffer allowing compose code and execute it. Differently of standard usage of execute immediate :Varchar2 this one is not limited by 32767 bytes and allows compose really huge code. It could be implemented either as type or as package. Type form allows to use multiple instances simultaneously.

There are two versions of the buffer module:

  • Oracle Type - allows multiple instances
  • Oracle Package - single instance

Following example shows usage of TYPE (object) version of buffer:

SET SERVEROUTPUT ON FOR WRA 
Declare
  V_buff BW_BUFF:=BW_BUFF();
Begin
    v_buff.AddLN('Create table TTT(');
    v_buff.ind(2);--indents code
    v_buff.AddLN('A varchar2(40)');
    v_buff.AddLN(',B varchar2(50)');
    v_buff.AddLN(',C varchar2(40)');
    v_buff.AddLN(',D varchar2(40)');
    v_buff.AddLN(',E varchar2(100)');
    v_buff.ind(-2);--indents code
    v_buff.AddLN(')');
    v_buff.BuffOut(); -- or v_buff.BuffExec();
End;
/

Create table TTT( A varchar2(40) ,B varchar2(50) ,C varchar2(40) ,D varchar2(40) ,E varchar2(100) )

Script: BWbuff_type.sql

Create or replace type BW_ta_CHR is TABLE of Varchar2(32767);
/
show errors
Create or replace type BW_Buff AUTHID CURRENT_USER is OBJECT(
  --------------------------------------------------------------
  --Purpose: Execute and Output buffer to store texts         --
  --Author:  Bob Jankovsky, copyleft 2005                     --
  --Version: 1.2 /09-FEB-2005                                 --
  --------------------------------------------------------------
  g_Buffer BW_ta_CHR

...more

Following example shows usage of PACKAGE version of buffer:

SET SERVEROUTPUT ON FOR WRA
Begin 
  BW_Buffer.Init;
  BW_Buffer.AddLN('Create table TTT(');
  BW_Buffer.ind(2);--indents code
  BW_Buffer.AddLN('A varchar2(40)');
  BW_Buffer.AddLN(',B varchar2(50)');
  BW_Buffer.AddLN(',C varchar2(40)');
  BW_Buffer.AddLN(',D varchar2(40)');
  BW_Buffer.AddLN(',E varchar2(100)');
  BW_Buffer.ind(-2);--indents code
  BW_Buffer.AddLN(')');
  BW_Buffer.BuffOut; -- or BW_Buffer.BuffExec;
End;
/

Create table TTT( A varchar2(40) ,B varchar2(50) ,C varchar2(40) ,D varchar2(40) ,E varchar2(100) )

Script: BWbuff_package.sql

CREATE OR REPLACE package BW_Buffer AUTHID CURRENT_USER as
  --------------------------------------------------------------
  --Purpose: Execute and Output buffer to store texts         --
  --Author:  Bob Jankovsky, copyleft 2005                     --
  --Version: 1.2 /09-FEB-2005                                 --
  --------------------------------------------------------------
   TYPE TA_CHR is table of VARCHAR2(32767) index by binary_integer;
   g_Buffer TA_CHR;
   g_append_pos    Integer;
   g_append_len    Integer;

...more
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky