"PL/SQL CLOB" Documents (Oracle Workflow Developer's Guide)

Oracle Workflow Developer's Guide
Release 2.6.3.5
Part Number B12161-02


	Previous	Next		Contents	Index	Glossary

"PL/SQL CLOB" Documents

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 clob,

                            document_type in out varchar2)

A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document or, if your database version is Oracle9i Database or higher, other binary data that is encoded to base64. 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. If your database version is Oracle9i Database or higher, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the binary data to base64. See: UTL_RAW, Oracle Supplied PL/SQL Packages and Types Reference and EncodeBLOB, Oracle Workflow API Reference.

Note: You can call WF_NOTIFICATION.WriteToClob() to help build a CLOB by appending a string of character data to it. See: WriteToClob, Oracle Workflow API Reference.

Note: Oracle8i Database does not support base64 encoding, so if you are using Oracle8i Database, the WF_MAIL_UTIL.EncodeBLOB procedure is not available, and you cannot store binary data other than PDF or RTF documents in a PL/SQL CLOB document. This feature is available only if you are using Oracle9i Database or higher.

However, both Oracle8i Database and Oracle9i Database and higher support the UTL_RAW.Cast_To_VARCHAR2 function, so you can store PDF and RTF documents in an attached PL/SQL CLOB document on any of these database versions.

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:

text/plain--the document is embedded inside a plain text representation of the notification.

text/html--the document is embedded inside an HTML representation of the notification as viewed from the Notification Web page. The procedure must generate an HTML representation of the document, but should not include top level HTML tags like <HTML> or <BODY> since the HTML page that the document is being inserted into already contains these tags. If you include top level HTML tags accidentally, Oracle Workflow removes the tags for you when the document attribute is referenced in a message body. Note that the procedure can alternatively generate a plain text document, as the notification system can automatically surround plain text with the appropriate HTML tags to preserve formatting.

' '--the document is presented as a separate attachment to the notification. Any content type may be returned.

document The outbound LOB locator pointing to where the document text is stored. This locator is a temporary LOB locator, so you must write your document text to this locator rather than replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information, see 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'. 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 := 'application/pdf; name=filename.pdf';

Note: If your database version is Oracle9i Database or higher, and 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';

Example

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 clob,

                document_type in out 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;

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:
	text/plain--the document is embedded inside a plain text representation of the notification.
	text/html--the document is embedded inside an HTML representation of the notification as viewed from the Notification Web page. The procedure must generate an HTML representation of the document, but should not include top level HTML tags like <HTML> or <BODY> since the HTML page that the document is being inserted into already contains these tags. If you include top level HTML tags accidentally, Oracle Workflow removes the tags for you when the document attribute is referenced in a message body. Note that the procedure can alternatively generate a plain text document, as the notification system can automatically surround plain text with the appropriate HTML tags to preserve formatting.
	' '--the document is presented as a separate attachment to the notification. Any content type may be returned.
document	The outbound LOB locator pointing to where the document text is stored. This locator is a temporary LOB locator, so you must write your document text to this locator rather than replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information, see 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'. 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:

"PL/SQL CLOB" Documents

See Also