"PL/SQL BLOB" Documents

The PL/SQL procedure that generates a PL/SQL BLOB document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out nocopy blob,
   document_type in out nocopy varchar2)

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 BLOB" document (plsqlblob:<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: plsqlblob:po_wf.show_req_blob/2038. 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: plsqlblob:po_wf.show_req_blob/&POREQ_NUMBER.
display_type For a PL/SQL BLOB document, this value should be ' ' to represent the content type used for the notification presentation, also referred to as the requested type:
'<type>/<subtype>' - 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 data is stored. This locator is a temporary LOB locator, so you must append your document data 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 BLOB 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. This argument 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 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:

  • You want to use a base file name that is different than the message attribute display name.

  • The MIME type and subtype for your document are not on the list defined within Oracle Workflow, and the file extension for the MIME subtype differs from the subtype name. For the list of MIME types and subtypes, see: MIME Types and Subtypes Defined Within Oracle Workflow.


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 := 'image/jpg; name=filename.jpg';

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;image.jpg then set the document_type value as follows:

document_type := 'image/jpg; name=my\;image.jpg';
Example

The following example shows a sample procedure to produce a PL/SQL BLOB document that contains a binary file such as an image or a PDF document.

Note: This example shows a simple API used for testing purposes. In a production instance, the API that produces a PL/SQL BLOB document should further validate the syntax of the document_type parameter when appending the base64 encoding specification.

/* API to produce a PL/SQL BLOB document for use as a  
   notification attachment based on the specified 
   document ID. */

procedure getBlobDoc
  (document_id in varchar2,
   content_type in varchar2,
   document in out nocopy blob,
   document_type in out nocopy varchar2)

is

  l_docid  pls_integer;
  l_filename varchar2(100);
  l_errmsg varchar2(100) := 
    'The document is not found in the database';
  l_bdoc blob;
  l_data_type varchar2(100);

begin

  -- Determine the document to display from the 
  -- Document ID
  l_docid := to_number(document_id);

  -- Obtain the BLOB version of the document
  select filename, content_type, bdata
  into l_filename, document_type, l_bdoc
  from wftst_lobs
  where id = l_docid;

  -- Set the encoding as a part of the document_type
  document_type := document_type || '; encoding=base64;';

  -- Now copy the content to the document
  dbms_lob.Copy(document, l_bdoc, dbms_lob.getLength(l_bdoc));

exception
  when others then
    dbms_lob.WriteAppend(document, length(l_errmsg), l_errmsg);
    wf_core.context('WFMLR_TEST','getBLOBDoc',document_id);
    raise;

end getBlobDoc;

Related Topics