Hi, guys:
We uploaded multiple template RTF files and stored in database as clob. However, when we tried to download multiple letters based on different template RTF documents, it always downloaded only the first one. Could you share some suggestions to us? We downloaded multiple mails successfully with template html tags
I followed the instruction of this link:
http://rchallis.blogspot.com/2012/05/part-1-problem-definition.html
we are using Oracle 11GR2, APEX 4.1.1, OC4J on linux
/*p_destination: possible value: OFFENDER or DL, if offender, letter will be sent to offender, if DL, letter will besent to DA or LE, default value OFFENDER, which means OFFENDER,
p_seq_col: possible value: DL or OF, if OF, letters should be ordered by offender name, if DL, letters will be ordered by name of the DA or Law Enforcement agency, default value null, which means OF
p_offender_type: possible value STANDARD or LIFETIME, default value null, which means STANDARD
*/
procedure downloadLetters(p_batch_id number, p_destination varchar2 default 'OFFENDER', p_offender_type varchar2 default null, p_seq_col varchar2 DEFAULT NULL, p_offender_id number default null) is
type curType is ref cursor;
cv curType;
-- cv GenericCurTyp;
--s varchar2(32000):= null;
sord varchar2(400) := null;
crec SOR_TRACKING_LETTER_V%rowtype;
CURSOR ltr(pLetterId number) IS
SELECT *
FROM SOR_LETTER
WHERE letter_id = pLetterId;
strLetter clob := '';
current_section clob;
current_letter clob;
v_text_test varchar2(32000);
strLetter_len number:=DBMS_LOB.LOBMAXSIZE;
message varchar2(1500);
vletterid number;
voffice_id number;
vtitle varchar2(15);
vOffenderType varchar2(30);
vfilename varchar2(120);
vLineCount number := 0;
blnFirstPage boolean := true;
vSirName varchar2(30);
error_message varchar2(500);
v_mime varchar2(48) := 'application/msword';
v_blob BLOB;
v_blob_offset integer;
v_clob_offset integer;
v_blob_csid integer:=DBMS_LOB.DEFAULT_CSID;
v_lang_context integer:=DBMS_LOB.DEFAULT_LANG_CTX;
v_warning integer;
l_server sor_email_config.server%type;
l_sender sor_email_config.sender%type;
procedure alert(str varchar2) is
begin
htp.script('alert('''||str||''')');
end;
/*get tracking and letter detail info for a specific batch or individual offender*/
PROCEDURE open_cv (cv IN OUT curType) IS
BEGIN
IF lower(p_seq_col) = 'dl' THEN
/* SOR_TRACKING_LETTER_V records ltters's detail including letter type and offender detail
DL_NAME: For Delinquent Letters. The name of the DA or Law Enforcement agency to which the letter was sent.
This is stored on the tracking record so that if the value for the agency changes in SOR_OFFICES,
we will still know the exact value which was put on the letter.*/
OPEN cv FOR
SELECT *
FROM SOR_TRACKING_LETTER_V
WHERE batch_id = p_batch_id
and offender_id = nvl(p_offender_id,offender_id)
ORDER BY DL_NAME, dl_office, LAST_NAME, FIRST_NAME, MIDDLE_NAME, sir_name;
--order by tracking_ID;
ELSE
OPEN cv FOR
SELECT *
FROM SOR_TRACKING_LETTER_V
WHERE batch_id = p_batch_id
and offender_id = nvl(p_offender_id,offender_id)
ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME, sir_name;
END IF;
END;
function escape(s in clob) return clob as
begin
return(REGEXP_REPLACE(s,'''','''s'));
end escape;
function concat(a varchar2, b varchar2) return varchar2
as
str varchar2(200);
begin
if a is not null then
str := a;
if b is not null then
str := a || '<br>'||b;
end if;
else
str := b;
end if;
return str;
end;
BEGIN
select server, SENDER
into l_server, l_sender
from sor_EMAIL_config scon
where upper(scon.current_setting)='Y'
and scon.configuration_id=(select max(scon2.configuration_id)
from sor_EMAIL_config scon2
where scon2.current_setting='Y'
);
dbms_output.put_line('Assign to vOffenderType');
/*By default offender type is standard */
IF p_offender_type is null THEN
vOffenderType := 'STANDARD';
ELSE
vOffenderType := p_offender_type;
END IF;
/*assign letter file name according to letter destination type */
IF p_destination = 'DL' THEN
IF p_seq_col = 'DL' THEN
vfilename := 'DLDL';
ELSE
vfilename := 'DLOF';
END IF;
ELSE
vfilename := 'VRFY';
END IF;
vfilename := vfilename||to_char(sysdate,'mmyy')||'.doc';
strLetter:='';
begin
IF cv%ISOPEN THEN
close cv;
END IF;
/*cv is a reference cursor, cv is actually the cursor used in the procedure of open_cv */
open_cv (cv);
dbms_output.put_line('cv OPENED');
/*get letter text for every letters*/
LOOP
vLineCount := vLinecount + 1;
dbms_output.put_line('vLineCount: '||vLineCount);
current_letter:=null;
FETCH cv INTO crec;
/*IF vLineCount <> cv%rowcount then
exit;
END IF;*/
IF vLineCount = cv%rowcount then
IF vLineCount > 1 then
current_letter:='<p style="page-break-before:always;"> </p>';
END IF;
ELSE
exit;
END IF;
vLineCount := cv%rowcount;
IF cv%NOTFOUND THEN
exit;
end if;
/*if a tracking letter will be sent without location, record it into sor_offender_log, but the letter text will NOT generated*/
IF crec.location_id is null then
dbms_output.put_line('Location ID is null!');
insert into sor_offender_log (offender_id, message, process)values (crec.offender_id, 'No location available','TEST_LETTER FOR '||p_destination);
COMMIT;
ELSE
BEGIN
/*get the current letter text from letter template, and replace it with values in sor_tracking_letter_v, note there are multiple sections in a letter */
FOR lrec IN ltr(crec.letter_id) LOOP
if crec.sir_name is not null then
vSirName := ', '||crec.sir_name;
else
vSirName := '';
end if;
current_section := lrec.text;
--dbms_output.put_line(strLetter);
current_section := REGEXP_REPLACE(current_section,'<<maildate>>',to_char(crec.mail_date,'Month dd, yyyy'));
current_section := REGEXP_REPLACE(current_section,'<<title>>',crec.title);
current_section := REGEXP_REPLACE(current_section,'<<ofirst>>',crec.first_name);
current_section := REGEXP_REPLACE(current_section,'<<omiddle>>',crec.middle_name);
current_section := REGEXP_REPLACE(current_section,'<<olast>>',crec.last_name);
current_section := REGEXP_REPLACE(current_section,'<<osirname>>',vSirName);
current_section := REGEXP_REPLACE(current_section,'<<oaddress>>',concat(crec.address1,crec.address2));
current_section := REGEXP_REPLACE(current_section,'<<ocity>>',crec.city);
current_section := REGEXP_REPLACE(current_section,'<<ostate> >',crec.state);
current_section := REGEXP_REPLACE(current_section,'<<ozip>>',crec.zip);
IF crec.destination <> 'OFFENDER' THEN
current_section := REGEXP_REPLACE(current_section,'<<dlname>>',crec.dl_name);
current_section := REGEXP_REPLACE(current_section,'<<dloffice>>',crec.dl_office);
current_section := REGEXP_REPLACE(current_section,'<<dladdress>>',nvl(crec.dl_mailing_address,crec.dl_address));
current_section := REGEXP_REPLACE(current_section,'<<dlcity>>',crec.dl_city);
current_section := REGEXP_REPLACE(current_section,'<<dlstate>>',crec.dl_state);
current_section := REGEXP_REPLACE(current_section,'<<dlzip>>',crec.dl_zip);
current_section := REGEXP_REPLACE(current_section,'<<last_mail_date>>',crec.last_mail_date);
END IF;
current_letter:=current_letter||current_section;
END LOOP;
exception
WHEN OTHERS THEN
message:=message ||'LTR: cursor error'||sqlerrm;
raise;
END;
--COMMIT;
strLetter:=strLetter||current_letter;
END IF;
END LOOP;
exception
WHEN OTHERS THEN
message:=message||'c: cursor error'||sqlerrm;
raise;
END;
--v_text_test:=dbms_lob.substr( strLetter, 32000, 1 );
--dbms_output.put_line('strLetter:'||v_text_test);
v_blob_offset:=1;
v_clob_offset:=1;
DBMS_LOB.CREATETEMPORARY(v_blob, true);
DBMS_LOB.CONVERTTOBLOB(v_blob, strLetter, strLetter_len, v_blob_offset, v_clob_offset, v_blob_csid, v_lang_context, v_warning);
OWA_UTIL.mime_header(v_mime, FALSE);
if v_mime != 'text/html' then
htp.p('Content-Disposition: filename="' || vfilename || '"');
end if;
HTP.p ('Content-length: ' || strLetter_len);
-- close the headers
OWA_UTIL.http_header_close;
wpg_docload.download_file( v_blob );
apex_application.stop_apex_engine;
DBMS_LOB.FREETEMPORARY (v_blob);
commit;
EXCEPTION
when apex_application.e_stop_apex_engine then
raise; -- raise again the stop APEX engine exception
WHEN OTHERS THEN
error_message:=substr(SQLERRM, 1, 400);
raise_application_error(SOR_ERROR_CONSTANTS_PKG.errnum_GENERAL, 'SOR_COMPLIANCE_ADMIN.downloadLetters when generating document '||SOR_ERROR_CONSTANTS_PKG.errmsg_GENERAL||error_message);
END downloadLetters;
Thanks.
Sam
Edited by: lxiscas on May 30, 2013 6:07 PM