REM dbdrv: sql ~PROD ~PATH ~FILE MVIEW_RECOMMENDATIONS SYSTEM &systempwd \ REM dbdrv: sqlplus &phase=plb REM +=======================================================================+ REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA | REM | All rights reserved. | REM +=======================================================================+ REM | FILENAME REM | wfmlutb.pls REM | REM | DESCRIPTION REM | Mailer Utility package body. This package has dependency on 9i database. REM | This will compile successfully in 9i database only. REM | REM | NOTES REM | This file is compiled by adpatch on 9i Database only. CheckObject REM | feature is used in dbdrv to check for a 9i specific object in SYSTEM REM | schema before compiling this file. If the object is found this file REM | is compiled. REM | REM | Any APIs/public procedures added to this file should also be added to REM | wfmlut8b.pls which is specific to 8i Database. REM | REM | HISTORY REM | Vijay Shanmugam(10/11/2002) - Created REM +=======================================================================+ SET VERIFY OFF WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; create or replace package body WF_MAIL_UTIL as /* $Header: wfmlutb.pls 26.11 2005/03/03 09:55:00 smayze ship $ */ -- EncodeBLOB -- Receives a BLOB input and encodes it to Base64 CLOB -- IN -- BLOB data -- OUT -- CLOB data procedure EncodeBLOB(pIDoc in blob, pODoc in out nocopy clob) is rawData raw(32767); chunksize integer; amount binary_integer := 32767; position integer := 1; base64raw RAW(32767); chunkcount binary_integer := 0; cBuffer varchar2(32000); begin chunksize := 12288; amount := dbms_lob.getLength(pIDoc); if(chunksize < amount) then chunkcount := round((amount / chunksize)+0.5); else chunkCount := 1; end if; for i in 1..chunkcount loop dbms_lob.read(pIDoc, chunksize, position, rawData); base64raw := utl_encode.base64_encode(rawData); cBuffer := utl_raw.cast_to_varchar2(base64Raw); dbms_lob.writeAppend(pODoc, length(cBuffer), cBuffer); position := position + chunksize; end loop; dbms_lob.WriteAppend(pODoc, 1, wf_core.newline); exception when others then wf_core.context('WF_MAIL_UTIL', 'EncodeBLOB'); raise; end EncodeBLOB; -- DecodeBLOB -- Receives a CLOB input and decodes it from Base64 to BLOB -- IN -- CLOB data -- OUT -- BLOB data procedure DecodeBLOB(pIDoc in clob, pODoc in out nocopy blob) is rawData raw(32767); chunksize integer; amount binary_integer := 32767; position number := 1; base64raw RAW(32767); chunkcount binary_integer := 0; cBuffer varchar2(32000); bufsize number; begin amount := dbms_lob.getLength(pIDoc); chunksize := 16896; -- Do not change if(chunksize < amount) then chunkcount := round((amount / chunksize)+0.5); else chunkCount := 1; end if; dbms_lob.trim(pODoc, 0); for i in 1..chunkcount loop dbms_lob.read(pIDoc, chunksize, position, cBuffer); base64raw := utl_raw.cast_to_raw(cBuffer); rawData := utl_encode.base64_decode(base64Raw); bufsize := utl_raw.length(rawData); dbms_lob.writeAppend(pODoc, bufsize, rawData); position := position + chunksize; end loop; exception when others then wf_core.context('WF_MAIL_UTIL','DecodeBLOB'); raise; end DecodeBLOB; -- StrParser -- Parse a string and seperate the elements into a memeory table based on the -- content of the seperators. -- IN -- str - The Varchar2 that is to be parsed -- sep - The list of SINGLE character seprators that will -- segment the str. -- RETURN -- parserStack_t a memory table of Varchar2 -- function strParser(str in varchar2, sep in varchar2) return parserStack_t is quot pls_integer; i pls_integer; c varchar2(1); attr varchar2(2000); defv varchar2(2000); stack parserStack_t; buf varchar2(2000); begin if str is not null or str <> '' then quot := 1; i := 1; buf := ''; while i <= length(str) loop c := substrb(str, i, 1); if instrb(sep, c,1 ,1)>0 then if buf is not null or buf <> '' then -- Push the buffer to the stack and start again stack(quot) := trim(buf); quot := quot + 1; buf := ''; end if; elsif c = '\' then -- Escape character. Consume this and the next character. i := i + 1; c := substrb(str, i, 1); buf := buf ||c; else buf := buf || c; end if; i := i + 1; end loop; if buf is not null or buf <> '' then stack(quot) := trim(buf); end if; end if; return stack; end strParser; -- ParseContentType -- Parses document type returned by the PLSQL/PLSQLCLOB/PLSQLBLOB document -- APIs and returns the parameters -- IN -- pContentType - Document Type -- OUT -- pMimeType - Content Type of the document -- pFileName - File Name -- pExtn - File Extension -- pEncoding - Content Encoding procedure parseContentType(pContentType in varchar2, pMimeType out nocopy varchar2, pFileName out nocopy varchar2, pExtn out nocopy varchar2, pEncoding out nocopy varchar2) is i pls_integer; l_content_type varchar2(255); l_paramlist parserStack_t; l_sublist parserStack_t; begin -- Derive the name for the attachment. l_content_type := pContentType; pExtn := ''; pFilename := ''; pMimeType := ''; pEncoding := ''; l_paramlist := strParser(l_content_type, ';'); if (l_paramlist is null) then return; end if; pMimeType := l_paramlist(1); for i in 1..l_paramlist.COUNT loop l_sublist := strParser(l_paramlist(i),'/'); if l_sublist.COUNT = 2 then pExtn := l_sublist(2); end if; l_sublist.DELETE; l_sublist := strParser(l_paramList(i),'="'); for i in 1..l_sublist.COUNT loop if lower(l_sublist(i)) = 'name' then pFilename := l_sublist(i+1); end if; if lower(l_sublist(i)) = 'encoding' then pEncoding := l_sublist(i+1); end if; end loop; l_sublist.DELETE; end loop; if lower(pExtn) = 'tab-separated-values' then pExtn := 'tsv'; elsif lower(pExtn) = 'comma-separated-values' then pExtn := 'csv'; elsif lower(pExtn) = 'plain' then pExtn := 'txt'; elsif lower(pExtn) like '%excel' then pExtn := 'xls'; elsif lower(pExtn) = 'html' then pExtn := 'htm'; elsif lower(pExtn) like '%msword' then pExtn := 'doc'; end if; end parseContentType; end WF_MAIL_UTIL; / commit; exit;