/*************************** 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 || '';
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
/
(
geocities.com/aritonc)