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;
   g_read_pos      Integer;
   g_read_len      Integer;
   g_RowCount      Integer;
   g_ind           Integer;
   Procedure Init;
   Procedure AddCH(p_val Varchar2);
   Procedure AddLN(p_val Varchar2);
   Procedure Ind(p_val Integer:=2);
   Procedure BuffOut(p_mode Varchar2:='WRAP');
   Procedure BuffExec(p_mode Varchar2:='EO');--Ex causes error; Xx excepts error; 
                                                   --xO put code to output in a cause of error; xS silent; 
                                                   --xA put code to output allways
End BW_Buffer;
/
CREATE OR REPLACE package body BW_Buffer as
----
Procedure Init is
Begin
  g_append_pos:=1;
  g_append_len:=0;
  g_read_pos:=1;
  g_read_len:=0;
  g_Buffer.delete;
  g_Buffer(1):='';
  g_ind:=0;
  Return;
End Init;  
---
Procedure DecWritePos(p_len Integer) is
  v_lines Integer:=FLOOR((g_append_len-p_len)/32767);
  v_chars Integer:=MOD((g_append_len-p_len),32767);
  v_ap Integer;
Begin
  g_append_len:=v_chars;
  v_ap:=g_append_pos+v_lines;
  if v_ap<g_append_pos then
    g_Buffer.Delete(g_append_pos-v_ap,g_append_pos); 
    g_append_pos:=v_ap;
  end if; 
  g_Buffer(v_ap):=substr(g_Buffer(v_ap),1,v_chars);
End DecWritePos;
----
Procedure AddCH(p_val Varchar2) is
  v_val Varchar2(32767):=replace(p_val,chr(10),chr(10)||lpad(' ',g_ind,' '));
  v_space Integer:=32767-g_append_len;
Begin
  if (length(v_val)>v_space) then
    AddCh(substr(v_val,1,v_space));
    v_val:=substr(v_val,v_space+1);
  end if;
  g_Buffer(g_append_pos):=concat(g_Buffer(g_append_pos),v_val);
  g_append_len:=g_append_len+length(v_val);
  if g_append_len=32767 then
    g_append_pos:=g_append_pos+1;
    g_Buffer(g_append_pos):='';
    g_append_len:=0;
  end if;
End AddCH;
----
Procedure AddLN(p_val Varchar2) is
Begin
  AddCH(concat(p_val,chr(10))); 
End AddLN;
----
Procedure Ind(p_val Integer:=2) is
Begin
 g_ind:=greatest(g_ind+p_val,0);
 if p_val>0 then
    AddCH(lpad(' ',p_val,' '));
  elsif p_val<0 then
    DecWritePos(-p_val);
  end if;
End Ind;
----
Procedure GetInit is
Begin
  g_read_pos:=1;
  g_read_len:=0;
End GetInit;
----
Procedure IncReadPos(p_len Integer) is
  v_lines Integer:=FLOOR((p_len+g_read_len)/32767);
  v_chars Integer:=MOD((p_len+g_read_len),32767);
Begin
  g_read_len:=v_chars;
  g_read_pos:=g_read_pos+v_lines;
End IncReadPos;
----
Function GetEOB return  Integer is
Begin
  if (g_read_pos<g_append_pos) or ((g_read_pos=g_append_pos)and(g_read_len<g_append_len)) then
    return 0;
  else
    return 1;
  end if;
End GetEOB;
----
Function GetLN return  Varchar2 is
  v_chunk Varchar2(32767);
  v_eoln  Integer;
Begin
  v_chunk:=substr(g_Buffer(g_read_pos),g_read_len+1);
  v_eoln:=instr(v_chunk,chr(10));
  if v_eoln>0 then
    IncReadPos(v_eoln);
    return substr(v_chunk,1,v_eoln-1);   
  else
    IncReadPos(length(v_chunk));
    if GetEOB=0 then
      return concat(v_chunk,GetLN());   
    else
      return v_chunk;   
    end if;
  end if;
End GetLN;
----
Procedure BuffOut(p_mode Varchar2:='WRAP') is
  v_str Varchar2(32767);
Begin
  DBMS_Output.Enable(1000000);
  GetInit;
  While (GetEOB=0) LOOP
    v_str:=getLN;
    if trim(upper(p_mode)) in ('WRAP','W') then
      while length(v_str)>0 LOOP 
        DBMS_Output.Put_line(substr(v_str,1,255));
        v_str:=substr(v_str,256);
      end LOOP;
    elsif trim(upper(p_mode)) in ('TRIM','T') then
      DBMS_Output.Put_line(substr(v_str,1,255));
    end if;
  End LOOP;  
End BuffOut;
----
Procedure BuffExec(p_mode Varchar2:='EO') is
  aSarr DBMS_SQL.Varchar2S;
  aSarrIdx Integer:=1;
  aSarrReq Integer:=256;
  c Integer;
  v_str Varchar2(32767);
Begin
  for i in 1..g_append_pos LOOP
    v_str:=g_Buffer(i);
    while length(v_str)>0 LOOP
      if aSarr.exists(aSarrIdx) then
        aSarr(aSarrIdx):=concat(aSarr(aSarrIdx),substr(v_str,1,aSarrReq));
      else 
        aSarr(aSarrIdx):=substr(v_str,1,aSarrReq);
      end if;
      v_str:=substr(v_str,aSarrReq+1);
      aSarrReq:=256-length(aSarr(aSarrIdx));
      if aSarrReq=0 then
        aSarrReq:=256;
        aSarrIdx:=aSarrIdx+1;
      end if;
    end LOOP;
  end LOOP;
  c:=DBMS_SQL.OPEN_CURSOR;
  Begin
    DBMS_SQL.PARSE(c,aSarr,aSarr.first,aSarr.last,FALSE,1);
    g_ROWCOUNT:=DBMS_SQL.EXECUTE(c);
    if upper(substr(p_mode,2,1))='A' then
      BuffOut('W');
    end if;
  Exception
    when others then
      if upper(substr(p_mode,2,1))='O' then
        BuffOut('W');
      end if;
      if upper(substr(p_mode,1,1))='E' then
        raise;
      end if;
  End;
  DBMS_SQL.CLOSE_CURSOR(c);
End BuffExec;
----
End BW_Buffer;
/