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
<Declare local Variable>
Begin
<Executable logic>;
---------------
--------------
Exception
When Others then
wf_core.CONTEXT('<Package Name>',
'<Procedure Name>',
document_id,
content_type
);
RAISE;
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.
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>';
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.
src_offset :-
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.
dest_offset :- Offset in characters in the destination CLOB (origin: 1) for the start of the write.
amount
:- Number of bytes to load from the BFILE.
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.
No comments:
Post a Comment