rem -----------------------------------------------------------------------
rem Filename:  mailmerge.sql
rem Purpose:   Merge data from the table/view data sources into a custom 
rem            template.
rem Date:      30 Dec 2004
rem Author:    Claudiu Ariton  (aritonc@yahoo.com)
rem
rem Package detail: 
rem    TEMP_MAIL_MERGE - temporary table to store the result 
rem    DATA_SOURCES - data sources table 
rem    mail_merge procedure - Merge data from list of values into template 
rem    generic_mail_merge - Merge data from a table/view into template 
rem
rem To define a template use $n where n is the position of a field in 
rem data_sources_v view (escape character is $). 
rem
rem E.G.: $2 $3 born on $4 has to pay $$$5. Call us at $6.
rem -----------------------------------------------------------------------


/***************************  Create result temp table, test table and context *************************/

create  GLOBAL TEMPORARY table TEMP_MAIL_MERGE
(
  PKID                               NUMBER         ,
  RESULT                             VARCHAR2(4000) )
On Commit Delete Rows
/

create table data_sources
(ID        Number(10) primary key,
 P2        Varchar2(10),
 P3        Varchar2(50),
 P4        DATE,
 P5        NUMBER
)
/

Insert into DATA_SOURCES
(ID, P2, P3, P4, P5)
Values
(1, 'Claudiu', 'Ariton', TO_DATE('09/21/1976 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100000)
/
Commit
/

create or replace context mm_ctx using mail_merge
/

create or replace view data_sources_v as
select ID PKID, P2, P3, P4, P5,
SYS_CONTEXT('mm_ctx','param1',2000) param1 from
data_sources
/


/***************************  Create package head  *************************/

Create or Replace Package mail_merge as

/**************************************************************************************
*
* TITLE......: Mail Merge 
* DESCRIPTION: Merge data from the data source into a custom template  
*   
* AUTHOR.....: Claudiu Ariton
* DATE.......: april 2004 
*
* Modifications
*
**************************************************************************************/

  type Argv is table of varchar2(4000) index by binary_integer;
  emptyargv argv;

  procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx'  );

  function mail_merge(
  p_message       in varchar2,
  p_argv          in argv ,
  p_esc_char      in varchar2 default '$') return varchar2;
  
  Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
  p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv);
  
end mail_merge;
/

/***************************  Create package body  *************************/

Create or Replace Package Body mail_merge as

-- -----------------------------------------------------------------------------------
-- Set_ctx - set generic context
-- -----------------------------------------------------------------------------------
procedure set_ctx( p_name in varchar2, p_value in varchar2,p_ctx in varchar2 default 'mm_ctx'  )
as

begin
 dbms_session.set_context( p_ctx, p_name, p_value, USER);
end;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from list of vaalues into a custom template
-- -----------------------------------------------------------------------------------
function mail_merge(
  p_message       in varchar2,
  p_argv          in argv ,
  p_esc_char      in varchar2 default '$') return varchar2 is
--
  l_message long := null;
  l_str long := p_message;
  l_idx number := 1;
  l_ptr number := 1;
  l_poz varchar2(10);
  l_on number;
begin
  if nvl( instr( p_message, p_esc_char ), 0 ) = 0 and
     nvl( instr( p_message, '\' ), 0 ) = 0 then
    return p_message;
  end if;
  loop

    l_on:=0;
    l_ptr := instr( l_str, p_esc_char );
    exit when l_ptr = 0 or l_ptr is null;
    l_message := l_message || substr( l_str, 1, l_ptr-1 );
    l_str :=  substr( l_str, l_ptr+1 );

     l_poz:=null;
     while substr( l_str, 1, 1 ) in ('0','1','2','3','4','5','6','7','8','9') loop
      l_poz:=l_poz||substr( l_str, 1, 1 );
      l_str := substr( l_str, 2 );
      l_on:=1;
     end loop;

     begin
      l_message := l_message || p_argv(to_number(l_poz));
     exception
      when no_data_found then l_message := l_message || '<unknown>';
      when others then null;
     end;

    if (substr( l_str,1,1 ) = p_esc_char) and (l_on=0) then
      l_message := l_message || p_esc_char;
      l_str := substr( l_str, 2 );
    end if;

  end loop;

  l_str := l_message || l_str;
  l_message := null;
  loop
    l_ptr := instr( l_str, '\' );
    exit when l_ptr = 0 or l_ptr is null;
    l_message := l_message || substr( l_str, 1, l_ptr-1 );
    l_str :=  substr( l_str, l_ptr+1 );
    if substr( l_str, 1, 1 ) = 'n' then
      l_message := l_message || chr(10);
      l_str := substr( l_str, 2 );
    elsif substr( l_str, 1, 1 ) = 't' then
      l_message := l_message || chr(9);
      l_str := substr( l_str, 2 );
    elsif substr( l_str, 1, 1 ) = '\' then
      l_message := l_message || '\';
      l_str := substr( l_str, 2 );
    else
      l_message := l_message || '\';
    end if;
  end loop;

  return l_message || l_str;
end mail_merge;

-- -----------------------------------------------------------------------------------
-- mail_merge - Merge data from a table/viiew into a custom template
-- -----------------------------------------------------------------------------------
Procedure generic_mail_merge(p_ttext varchar2, p_query in varchar2,
p_date_format in varchar2 default 'dd-MON-yyyy hh24:mi:ss',
p_bindid in number default null,p_list_val in argv default emptyargv)
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_defcolumn     varchar2(4000);
    l_columnValue   argv;
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    v_result        varchar2(4000);
    v_pkid          number;
begin
    execute immediate
    'alter session set
        nls_date_format='''|| p_date_format ||'''';

    dbms_sql.parse(  l_theCursor, p_query, dbms_sql.native );

    if p_bindid is not null then
     dbms_sql.bind_variable( l_theCursor, ':1', p_bindid );
    end if;

    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    -- seteaza context
    begin
     for i in 1..p_list_val.count loop
      set_ctx('param'||to_char(i),p_list_val(i));
     end loop;
    exception
     when others then null;
    end;

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_defcolumn, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue(i) );
            if upper(l_descTbl(i).col_name)='PKID' then
             v_pkid:=to_number(l_columnValue(i));
            end if;
        end loop;

        v_result:=mail_merge( p_ttext,l_columnValue);
        insert into TEMP_MAIL_MERGE(pkid,result) values (v_pkid,v_result);
    end loop;
    dbms_sql.close_cursor(  l_theCursor);
    execute immediate
        'alter session set nls_date_format=''dd-MON-rr'' ';
exception
    when others then
      if dbms_sql.is_open(l_theCursor) then
      dbms_sql.close_cursor(  l_theCursor);
      end if;
      execute immediate
          'alter session set nls_date_format=''dd-MON-rr'' ';
      raise;
end;

end mail_merge;
/

/***************************  Test it  *************************/
declare
 v_list_val mail_merge.argv;
 v_query varchar2(4000);
begin
 v_list_val(1):='(040)-2313543';
 v_query:= 'select * from data_sources_v vt where 1=1'||' AND vt.pkid=:1';
 mail_merge.generic_mail_merge('$2 $3 born on $4 has to pay $$$5. Call us at $6. Thank you very much.',
  v_query,'dd-MON-yyyy',1,v_list_val);
end;
/

select * from temp_mail_merge
/