f) How To Attach Document With Notification

Oracle Workflow also provide us a provision to attach document and help us to share information with the different stakeholders of business process (whereever is required) .

In a workflow process, we can attach documents generated by a PL/SQL procedure, which we call PL/SQL, PL/SQL CLOB, or PL/SQL BLOB documents.

Here we will discuss about the ways to attach PL/SQL BLOB and PL/SQL CLOB with the notification.

Basic Requirement

Our old requirement looks like "when a person applies for a leave it should go his/her supervisor for approval.Approver must be able to provide his/her approval/rejection comments. The comments must be entered by the approver while rejecting the leave requisition.If he/she rejects the leave requisition without entering comments, then it should raise an application error.If he/she approves the requisition it should be optional.Approver must also be able to select theOrganization Leave Type from a list of values . If the leave gets rejected, don’t store any information in database.The approver should able to view/edit the person "Special Information Types" before approving the leave request."

In addition to the above requirement:- When a Person applies for leave* he/she also submits the medical document. Approver should able to see the submitted medical document submitted by person.

* ==> Here we are considering only sick leave.

Assumption

Following are the assumptions based on which our design will be build

1) Here Workflow design always consider that leave type is "sick leave".

2) User submitted document is available at server side(not in oracle table).

3) Application always check that when a person submits Sick Type leave, he/she also submitted the medical document

4) Submitted medical document is available at server side "/usr/tmp" location

Solution Approach

Here we will mainly discuss about the PL/SQL BLOB document attribute and its usages.We also explain how we can modify the code for PL/SQL CLOB document attribute.

1) Workflow Part

a) First we have to create a attribute of type text.

Internal Name:- BLOB_DOC_ID

Display Name:- Document Id for Blob

Type :- Text

This attribute will store the document id for Blob document.This will help workflow(also developer) to uniquely identify the document available in database

b) Now create a document** type item attribute

Internal Name:- LEAVE_BLOB_DOC

Display Name:- Leave document of Type Blob

Type :- Document

Default Value:-plsqlblob:XX_TEST_LEAVE_PKG.GETBLOBDOC/&BLOB_DOC_ID

** ==>The "Frame Target" field is not applicable for attributes of type document. For document attributes, this field is reserved for future use.

***==> If we want to pass a static value for document identifier(document id) then the format will be look like

plsqlblob:<package>.<procedure_name>/XXXXX . Here XXXX is a string value.

Example:- plsqlblob:XX_TEST_LEAVE_PKG.GETBLOBDOC/123

plsqlblob:XX_TEST_LEAVE_PKG.GETBLOBDOC/ABC_123

For CLOB document the format will be

For static:- plsqlclob:<package>.<procedure_name>/XXXXX

For dynamic:- plsqlclob:<package>.<procedure_name>/&<Internal name of Document id attribute>

c) Now copy the both the attribute and paste it in message(make it a message attribute).Open the "LEAVE_BLOB_DOC" message attribute (attribute under message) and click on the "Attach Content". This will display the document as icon in the notification

d) Save the workflow definition to database.

2) Pl/Sql Part

We have already save our workflow definition to database. Now we have to create pl/sql procedure that will generate the Blob document.

The format of the PL/SQL procedure must be as mentioned below.We need not to bother about the input parameter. Oracle Workflow Engine will take care of that.

PROCEDURE <procedure_Name> (document_id IN VARCHAR2,

content_type IN VARCHAR2,

document IN OUT NOCOPY BLOB,

document_type IN OUT NOCOPY VARCHAR2

) is

Begin

<Executable logic>;

---------------

--------------

Exception

When Others then

wf_core.CONTEXT('<Package Name>',

'<Procedure Name>',

document_id,

content_type

);

RAISE;

End <procedure_Name>;

a) First we have to set the value item attribute "BLOB_DOC_ID" for document identifier.This can be done within triggering workflow procedure where we are setting other attribute values.The value can be anything depending on the business requirement and design.We can define our own pl/sql logic to identify the desired value.

Example:- Since we are reading file from server hence we can set the value as <name of the medical document>_<itemkey>.

For simplicity we have use item key as document id.

b) Now we create a new procedure with the name same as that is given in default value of document attribute.(Remember the procedure will be under the same package as we have mentioned in default value

1) create a oracle directory pointing to the directory where our file exists.

2) Define BFILE locator of the location where actually file is located.(bfilename utility is explained in 5) iRecruitment Data Migration - How To Migrate Resume topic)

3) Create a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace using dbms_lob.createtemporary utility. This will help us

to manipulate the temporary LOBs with PL/SQL.

DBMS_LOB.CREATETEMPORARY (lob_loc IN OUT NOCOPY BLOB,

cache IN BOOLEAN,

dur IN PLS_INTEGER := 10);

lob_loc:- LOB Locator

cache :- Specifies if LOB should be read into buffer cache or not.

dur :- Specifies whether the temporary LOB is cleaned up at the end of the session or call. If dur is omitted, then the session duration is used.

Note:-This function can be used for CLOB also.

4) Open the file in read-only mode.

5) Now load the Blob into a local variable from file using utility DBMS_LOB.LOADBLOBFROMFILE.

DBMS_LOB.LOADBLOBFROMFILE (dest_lob => l_temp_blob,

src_bfile=>l_file_on_os,

amount=>dbms_lob.getlength(l_file_on_os)

,dest_offset=>l_dest_offset --in out parameter

,src_offset =>l_src_offset);

This will store the data from BFILE to internal BLOB.

dest_lob :- BLOB locator of the target for the load.

src_bfile :- BFILE locator of the source for the load.

amount :- Number of bytes to load from the BFILE.

dest_offset:- Offset in bytes in the destination BLOB (origin: 1) for the start of the write.

src_offset :- Offset in bytes in the source BFILE (origin: 1) for the start of the read

6) Now close the file.

7) Again create a temporary BLOB so that we can copy our recently created BLOB data from local variable to our output variable "document"

8) Copy the BLOB stored in local variable to document output parameter using dbms_lob.Copy utility

DBMS_LOB.COPY (dest_lob IN OUT NOCOPY BLOB,

src_lob IN BLOB,

amount IN INTEGER,

dest_offset IN INTEGER := 1,

src_offset IN INTEGER := 1);

dest_lob :- Locator for the destination LOB

src_lob :- Locator for the source LOB

amount :- Number of bytes (BLOB) or characters (CLOB, NCLOB) to copy

dest_offset :- Location of the byte (BLOB) or character (CLOB, NCLOB) in the destination LOB at which the copy operation begins. Note default value of 1

src_offset :- Location of the byte (BLOB) or character (CLOB, NCLOB) in the source LOB at which the copy operation begins. Note default value of 1

Note:-This function can be used for CLOB also

9) Now we have to set the "document_type" parameter. We have to set the value in the following format

document_type := '<MIME TYPE>; name=<document name>';

MIME TYPE: can be determined from the extension of the document. The details of MIME type is discussed in 5) iRecruitment Data Migration - How To Migrate Resume topic

Here for shake of simplicity we have hard coded the value.

10) Compile the code and ready for triggering the worklfow.

NOTE:- 1) For CLOB Document most of the part is same, only we have to use DBMS_LOB.LOADCLOBFROMFILE utility to load the data from BFILE.

DBMS_LOB.LOADCLOBFROMFILE (dest_lob IN OUT NOCOPY BLOB, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, bfile_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);

dest_lob :- CLOB/NCLOB locator of the target for the load.

src_bfile :- BFILE locator of the source for the load.

amount :- Number of bytes to load from the BFILE.

dest_offset :- Offset in characters in the destination CLOB (origin: 1) for the start of the write.

src_offset :-Offset in bytes in the source BFILE (origin: 1) for the start of the read.

bfile_csid :- Character set id of the source (BFILE) file

lang_context :- Language context, such as shift status, for the current load.

warning :- Indicates something unexpected happening during load.

2) Instead of storing it in local variable then to output parameter, we can directly store it is output parameter.

Just for the shake of understanding of dbms_lob.Copy utility we stored it in local variable.

3) For PL/SQL and PL/SQL CLOB documents that contain text or HTML, the document generated by

the PL/SQL procedure can either be displayed within the text of a notification or included as an attachment.

Other types of content in PL/SQL CLOB documents, as well as all PL/SQL BLOB documents, can only be included as attachments.

4) If no document type is supplied, then ’text/plain’ is assumed.

5) if we don't make document id attribute(here for BLOB it is "BLOB_DOC_ID" and for CLOB it is "LEAVE_CLOB_DOC_ID" ) as message attribute,

we will face an error while clicking on the attachment icon

References:- 1) https://metalink.oracle.com

2) Oracle Workflow Developer's Guide ( Release 12) B31433-04

Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle may not provide you a support for any data

corruption or any other problem in your custom code/problem arises because of the custom code. The author is not responsible for any

kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.