The PL/SQL procedure that generates a PL/SQL CLOB document must have the following standard API:
procedure <procedure name> (document_id in varchar2, display_type in varchar2, document in out nocopy clob, document_type in out nocopy varchar2)
A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document. You should first store the document in the database as a binary large object (BLOB) and then convert the document into a CLOB as part of the PL/SQL procedure that generates the CLOB. You can use the UTL_RAW.Cast_To_VARCHAR2 function to convert the data from the BLOB into VARCHAR2 data that you write to a CLOB. See: UTL_RAW, Oracle Database PL/SQL Packages and Types Reference.
A PL/SQL CLOB document that you include as an attachment to a notification can also contain any other binary data that is encoded to base64. With this database version, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the PDF, RTF, or other binary data to base64. See: EncodeBLOB.
Note: You can call WF_NOTIFICATION.WriteToClob() to help build a CLOB by appending a string of character data to it. See: WriteToClob.
The arguments for the procedure are as follows:
| document_id | A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL CLOB" document (plsqlclob:<procedure>/<document_identifier>). <procedure> should be replaced with the PL/SQL package and procedure name in the form of package.procedure. The phrase <document_identifier> should be replaced with the PL/SQL argument string that you want to pass directly to the procedure. The argument string should identify the document. For example: plsqlclob:po_wf.show_req_clob/2036. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsqlclob:po_wf.show_req_clob/&POREQ_NUMBER. |
| display_type | One of three values that represents the content type used for the notification presentation, also referred to as the requested type:
|
| document | The outbound LOB locator pointing to where the document text is stored. This locator is a temporary LOB locator, so you must append your document text to this locator rather than overwriting or replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information about LOB locators and storing CLOB data, see DBMS_LOB - Operational Notes, Oracle Database PL/SQL Packages and Types Reference and Temporary LOBs, Oracle Application Developer's Guide - Large Objects (LOBs). |
| document_type | The outbound text buffer where the document content type is returned. Also referred to as the returned type. If no type is supplied, then 'text/plain' is assumed. For a PDF or RTF document, this argument should specify an appropriate Multi-purpose Internet Mail Extension (MIME) type, such as 'application/pdf' or 'application/rtf'. For a document that will be attached to the notification, the value for this argument can also include a file name for the attachment. If you do not specify a file name for the attachment as part of this argument, then Oracle Workflow uses the message attribute display name as the base file name and checks its list of defined MIME types and subtypes to determine the file extension, based on the display_type argument. If the MIME type and subtype specified in the display_type argument are not defined within Oracle Workflow, then the name of the specified subtype is used as the file extension. However, note that for some subtypes the file extension differs from the subtype name. Consequently, you must explicitly specify the file name for the attachment in the document_type parameter if either of the following conditions applies:
To specify a file name as part of the document_type argument, use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command: document_type := 'application/pdf; name=filename.pdf'; Note: If the file name contains a semicolon (;) or an equal sign (=), then you should include a backslash (\) as an escape character preceding the semicolon or equal sign. For example, if the file name is my;document.pdf then set the document_type value as follows: document_type := 'application/pdf; name=my\;document.pdf'; Note: If you are using the WF_MAIL_UTIL.EncodeBLOB API to encode binary data to base64 in order to store the data in this PL/SQL CLOB document, then the document_type parameter should specify an appropriate MIME type with a primary type of either application or image, such as 'application/doc', 'application/pdf', 'image/jpg', or 'image/gif'. The MIME type must be followed by a semicolon (';') and then by the encoding specification 'encoding=base64' with no spaces before or after the equal sign. You can also optionally specify a file name for the attachment as part of this argument. Use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command: document_type := 'image/jpg; encoding=base64; name=filename.jpg'; |
The following example shows a sample procedure to produce a PL/SQL CLOB document that contains a PDF or RTF document, using the UTL_RAW.Cast_To_VARCHAR2 function to convert a BLOB to a CLOB.
procedure cdoc
(document_id in varchar2,
display_type in varchar2,
document in out nocopy clob,
document_type in out nocopy varchar2)
is
bdoc blob;
cdoc clob;
content_type varchar2(100);
lob_id number;
amount number;
bdoc_size number;
block number := 10000;
blockCount number;
rawBuff RAW(32000);
pos number;
charBuff varchar2(32000);
charBuff_size number;
filename varchar2(200);
location varchar2(200);
data_type varchar2(100);
begin
dbms_lob.createTemporary(cdoc, FALSE, dbms_lob.call);
-- Determine the document to display from the
-- Document ID
lob_id := to_number(document_id);
-- Obtain the BLOB version of the document
select filename, location, content_type, data_type, bdata
into filename, location, content_type, data_type, bdoc
from sjm_lobs
where id = lob_id;
-- recast the BLOB to a CLOB
bdoc_size := dbms_lob.getLength(bdoc);
if block < bdoc_size then
blockCount := round((bdoc_size/block)+0.5);
else
blockCount := 1;
end if;
pos := 1;
for i in 1..blockCount loop
dbms_lob.read(bdoc, block, pos, rawBuff);
charBuff := utl_raw.cast_to_varchar2(rawBuff);
charbuff_size := length(charBuff);
dbms_lob.writeAppend(cdoc, charbuff_size, charBuff);
pos := pos + block;
end loop;
-- Now copy the content to the document
amount := dbms_lob.getLength(cdoc);
dbms_lob.copy(document, cdoc, amount, 1, 1);
-- Set the MIME type as a part of the document_type.
document_type := content_type||'; name='||filename;
exception
when others then
wf_core.context('LOBDOC_PKG', 'cdoc',
document_id, display_type);
raise;
end cdoc;