     p_cur_desc in dbms_sql.desc_tab2,
     p_key_value_tab in out nocopy key_value_tab,
     p_row_tab in out nocopy row_tab,
-    p_first_column_is_template boolean default false)
+    p_first_column_is_template boolean default false) 
   as
   begin
     while dbms_sql.fetch_rows(p_cur) > 0 loop
       copy_values(
-        p_cur => p_cur, 
-        p_cur_desc => p_cur_desc, 
-        p_key_value_tab => p_key_value_tab, 
+        p_cur => p_cur,
+        p_cur_desc => p_cur_desc,
+        p_key_value_tab => p_key_value_tab,
         p_first_column_is_template => p_first_column_is_template);
-        
+
       p_row_tab(dbms_sql.last_row_count) := p_key_value_tab;
     end loop;
   end copy_values;
-  
-  
+
+
   /* Initialisierungsprozedur des Packages */
-  procedure initialize
+  procedure initialize 
   as
   begin
     g_ignore_missing_anchors := true;
   end initialize;
-  
-  
-  /* INTERFACE */    
+
+
+  /* INTERFACE */
   procedure set_ignore_missing_anchors(
-    p_flag in boolean)
+    p_flag in boolean) 
   as
   begin
     g_ignore_missing_anchors := p_flag;
   end set_ignore_missing_anchors;
-  
-  
-  function get_ignore_missing_anchors
-    return boolean
+
+
+  function get_ignore_missing_anchors return boolean 
   as
   begin
     return g_ignore_missing_anchors;
   end get_ignore_missing_anchors;
-  
-  
+
+
   procedure copy_row_to_key_value_tab(
-    p_stmt in varchar2,
-    p_key_value_tab in out nocopy key_value_tab)
+    p_stmt in clob,
+    p_key_value_tab in out nocopy key_value_tab) 
   as
     l_cur integer;
     l_cur_desc dbms_sql.desc_tab2;
   begin
     describe_cursor(
-      p_stmt => p_stmt, 
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
+      p_stmt => p_stmt,
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
       p_key_value_tab => p_key_value_tab);
-      
+
     if dbms_sql.fetch_rows(l_cur) > 0 then
       copy_values(l_cur, l_cur_desc, p_key_value_tab);
     end if;
-    
+
     dbms_sql.close_cursor(l_cur);
   end copy_row_to_key_value_tab;
-  
-  
+
+
   procedure copy_row_to_key_value_tab(
     p_cursor in out nocopy sys_refcursor,
-    p_key_value_tab in out nocopy key_value_tab)
+    p_key_value_tab in out nocopy key_value_tab) 
   as
     l_cur integer;
     l_cur_desc dbms_sql.desc_tab2;
   begin
     describe_cursor(
-      p_cursor => p_cursor, 
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
+      p_cursor => p_cursor,
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
       p_key_value_tab => p_key_value_tab);
-      
+
     if dbms_sql.fetch_rows(l_cur) > 0 then
       copy_values(l_cur, l_cur_desc, p_key_value_tab);
     end if;
-    
+
     dbms_sql.close_cursor(l_cur);
   end copy_row_to_key_value_tab;
-  
-    
+
+
   procedure copy_table_to_row_tab(
-    p_stmt in varchar2,
-    p_row_tab in out nocopy row_tab)
+    p_stmt in clob,
+    p_row_tab in out nocopy row_tab,
+    p_first_column_is_template boolean default false) 
   as
     l_cur integer;
     l_cur_desc dbms_sql.desc_tab2;
     l_key_value_tab key_value_tab;
   begin
     describe_cursor(
-      p_stmt => p_stmt, 
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
-      p_key_value_tab => l_key_value_tab);
-      
+      p_stmt => p_stmt,
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
+      p_key_value_tab => l_key_value_tab,
+      p_first_column_is_template => p_first_column_is_template);
+
     copy_values(
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
-      p_key_value_tab => l_key_value_tab, 
-      p_row_tab => p_row_tab);
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
+      p_key_value_tab => l_key_value_tab,
+      p_row_tab => p_row_tab,
+      p_first_column_is_template => p_first_column_is_template);
   end copy_table_to_row_tab;
-  
-    
+
+
   procedure copy_table_to_row_tab(
     p_cursor in out nocopy sys_refcursor,
     p_row_tab in out nocopy row_tab,
-    p_first_column_is_template boolean default false)
+    p_first_column_is_template boolean default false) 
   as
     l_cur integer;
     l_cur_desc dbms_sql.desc_tab2;
     l_key_value_tab key_value_tab;
   begin
     describe_cursor(
-      p_cursor => p_cursor, 
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
+      p_cursor => p_cursor,
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
       p_key_value_tab => l_key_value_tab,
       p_first_column_is_template => p_first_column_is_template);
-      
+
     copy_values(
-      p_cur => l_cur, 
-      p_cur_desc => l_cur_desc, 
-      p_key_value_tab => l_key_value_tab, 
+      p_cur => l_cur,
+      p_cur_desc => l_cur_desc,
+      p_key_value_tab => l_key_value_tab,
       p_row_tab => p_row_tab,
       p_first_column_is_template => p_first_column_is_template);
   end copy_table_to_row_tab;
-  
-
+
+
+  function bulk_replace(
+    p_template in varchar2,
+    p_chunks in char_table)
+    return varchar2
+  as
+    l_result varchar2(32767);
+    l_key_value_tab key_value_tab;
+  begin
+    if p_chunks is not null then 
+      for i in p_chunks.first .. p_chunks.last loop
+        if mod(i, 2) = 1 then
+          l_key_value_tab(p_chunks(i)) := p_chunks(i + 1);
+        end if;
+      end loop;
+    end if;
+    bulk_replace(p_template, l_key_value_tab, l_result);
+    return l_result;
+  end bulk_replace;
+    
+    
+  procedure bulk_replace(
+    p_template in varchar2,
+    p_chunks in char_table,
+    p_result out varchar2)
+  as
+  begin
+    p_result := bulk_replace(p_template, p_chunks);
+  end bulk_replace;
+    
+    
+  procedure bulk_replace(
+    p_text in out nocopy varchar2,
+    p_chunks in char_table)
+  as
+  begin
+    p_text := bulk_replace(p_text, p_chunks);
+  end bulk_replace;
+    
+    
   procedure bulk_replace(
     p_template in varchar2,
     p_key_value_tab in key_value_tab,
-    p_result out varchar2)
-  as
-    /* SQL-Anweisung, um generisch aus einem Template alle Ersetzungsanker
-       auszulesen und optionale Pre- und Postfixe sowie Ersatzwerte fuer NULL
-       zu ermitteln
-       Syntax des Ersetzungsankers:
-       #<Name>[|<Prefix>[|<Postfix>[|<NULL-Ersatzwert>]]]#
-       Beispiele:
-        #COLUMN_VALUE#
-        #COLUMN_VALUE||, #
-        #EMP_ID|(|), |ohne ID#
+    p_result out varchar2) 
+  as
+    /* Format des Ersetzungsankers:
+     * #<Name des Ersetzungsankers, muss Tabellenspalte entsprechen>
+     *        |<Pr�fix, falls Wert not null>
+     *        |<Postfix, falls Wert not null>
+     *        |<Wert, falls NULL>#
      */
     cursor replacement_cur(p_template in varchar2) is
-        with data as(
-             select p_template template
-               from dual),
-             anchors as(
-              select regexp_substr(template, '#.+?#', 1, level) a
-                from data
-             connect by level <= regexp_count(template, '#')/2)
-      select a replacement_string, 
-             upper(regexp_substr(a, '[^#|]+')) anchor, 
-             regexp_substr(a, '[^#|]+', 1, 2) prefix, 
-             regexp_substr(a, '[^#|]+', 1, 3) postfix, 
-             regexp_substr(a, '[^#|]+', 1, 4) not_null
-        from anchors;  
+      with data as
+       (select p_template template
+        from   dual),
+      anchors as
+       (select trim('#' from regexp_substr(template, '#.+?#', 1, level)) replacement_string
+        from   data
+        connect by level <= regexp_count(template,'#') / 2)
+      select '#'||replacement_string||'#' as replacement_string
+            ,upper(regexp_substr(replacement_string, '[^|]+', 1, 1)) anchor
+            ,regexp_substr(replacement_string, '(.*?)(\||$)', 1, 2, null, 1) prefix
+            ,regexp_substr(replacement_string, '(.*?)(\||$)', 1, 3, null, 1) postfix                         
+            ,regexp_substr(replacement_string, '(.*?)(\||$)', 1, 4, null, 1) not_null
+      from   anchors;
+
     l_value varchar2(32767);
     l_missing_anchors varchar2(32767);
   begin
     p_result := p_template;
+
     for rep in replacement_cur(p_template) loop
-    
       if p_key_value_tab.exists(rep.anchor) then
         l_value := p_key_value_tab(rep.anchor);
         if l_value is not null then
-          p_result := replace(p_result, rep.replacement_string, rep.prefix || l_value || rep.postfix);
+          p_result := replace(
+                        p_result, 
+                        rep.replacement_string, 
+                        rep.prefix || l_value || rep.postfix);
         else
           p_result := replace(p_result, rep.replacement_string, rep.not_null);
         end if;

create or replace package body code_generator
as
  
  C_ROW_TEMPLATE constant varchar2(30) := '$$ROW_TEMPLATE';
  g_ignore_missing_anchors boolean;
  
  /* Hilfsfunktionen */
  
  /* Prozedur zum Oeffnen eines Cursors
   * %param p_cur ID des Cursors
   * %usage Oeffnet einen neuen Cursor fuer eine uebergebene SQL-Anweisung
   */
  procedure open_cursor(
    p_cur out integer)
  as
  begin
    p_cur := dbms_sql.open_cursor;
  end open_cursor;
  
  
  /* Prozedur zum Oeffnen eines Cursors
   * %param p_cur ID des Cursors
   * %param p_cursor SYS_REFCURSOR, Ueberladung fuer einen bereits geoffneten Cursor

      p_first_column_is_template => true);
  end generate_text;


  procedure generate_text(
    p_stmt in clob,
    p_result out varchar2,
    p_delimiter in varchar2 default null) 
  as
    l_row_tab row_tab;
  begin
    copy_table_to_row_tab(
      p_stmt => p_stmt,
      p_row_tab => l_row_tab,
      p_first_column_is_template => true);

    bulk_replace(
      p_template => null,
      p_row_tab => l_row_tab,
      p_delimiter => p_delimiter,
      p_result => p_result,
      p_first_column_is_template => true);
  end generate_text;


  procedure generate_text(
    p_stmt in clob,
    p_result out dbms_sql.varchar2a,
    p_delimiter in varchar2 default null) 
  as
    l_row_tab row_tab;
  begin
    copy_table_to_row_tab(
      p_stmt => p_stmt,
      p_row_tab => l_row_tab,
      p_first_column_is_template => true);

    bulk_replace(
      p_template => null,
      p_row_tab => l_row_tab,
      p_delimiter => p_delimiter,
      p_result => p_result,
      p_first_column_is_template => true);
  end generate_text;


  function generate_text(
    p_stmt in clob,
    p_delimiter in varchar2 default null) 
    return varchar2 
  as
    l_string varchar2(32767);
  begin
    generate_text(
      p_stmt => p_stmt,
      p_result => l_string,
      p_delimiter => p_delimiter);
    return l_string;
  end generate_text;


  function generate_text(
    p_cursor in sys_refcursor,
    p_delimiter in varchar2 default null) 
    return varchar2 
  as
    l_string varchar2(32767);
    l_cur sys_refcursor := p_cursor;
  begin
    generate_text(
      p_cursor => l_cur,
      p_result => l_string,
      p_delimiter => p_delimiter);
    return l_string;
  end generate_text;


begin
  initialize;
end code_generator;
