PL/SQL document: Workflow Notification with Dynamic HTML Body

To generate a HTML notification we can just write HTML code on the message notification but unfortunately message body accepts only content 32kb of content. If message body exceeds 32kb size then we can go for “PL/SQL” documents, “PL/SQL CLOB” documents, and “PL/SQL BLOB” documents. We can also use this PL/SQL documents when we need to generate html code dynamically.

More about
“PL/SQL” Documents – represents data as a character string of 32KB
“PL/SQL CLOB” Documents – represents data as a character large object (CLOB) of 4GB
“PL/SQL BLOB” Documents – represents data as a binary large object (BLOB) of 4GB

In this article lets see how to create a workflow notification using PL/SQL document to print Employee name and number from EMP table.

Create a small workflow process with 3 nodes Start node, Notification node and End node like shown in the below screen shot.

Create an attribute ‘XX_BODY’ of ‘Document‘ type as shown in the below screen shot:

Create a Message with display name as “Message of HTML Body” and Internal Name as “XX_MSG”. Add content to the message body as shown in the below screen shot:

Create a Notification with display name as “Notification for HTML Body” and Internal Name as “XX_NOTIF”. Add message, role to the newly created notification as shown in the below screen shot

Package Specification

CREATE OR REPLACE
PACKAGE xx_emp_wf_doc_pkg
AS
  PROCEDURE xx_create_wf_doc(
      document_id   IN VARCHAR2,
      display_type  IN VARCHAR2,
      document      IN OUT nocopy VARCHAR2,
      document_type IN OUT nocopy VARCHAR2 );
END xx_emp_wf_doc_pkg;
/
SHOW errors;
EXIT;

package Body

CREATE OR REPLACE
PACKAGE body xx_emp_wf_doc_pkg
AS
PROCEDURE xx_create_wf_doc(
    document_id   IN VARCHAR2,
    display_type  IN VARCHAR2,
    document      IN OUT nocopy VARCHAR2,
    document_type IN OUT nocopy VARCHAR2 )
IS
  l_body VARCHAR2(32767);
BEGIN
  --
  document_type := 'text/html';
  --
  l_body := ' 
<table>
<thead>
<tr>
<th style="background-color:#CFE0F1;">Employee Name</th>
<th style="background-color:#CFE0F1;">Employee Number</th>
</thead>
</tr>
<tbody>
';
  FOR i IN
  (SELECT ename,empno FROM emp
  )
  LOOP
    BEGIN
      l_body := l_body || '<tr>    
<td>'|| i.ename || '</td>     
<td>' || i.empno || '</td>     
</tr>';
    END;
  END LOOP;
  document := l_body;
--
--Setting document type which is nothing but MIME type
--
  document_type := 'text/html';
EXCEPTION
WHEN OTHERS THEN
  document := '<H4>Error: '|| sqlerrm || '</H4>';
END xx_create_wf_doc;
END xx_emp_wf_doc_pkg;
/
SHOW errors;
EXIT;

What does the above package do?

The above package generates a PL/SQL document which has a dynamic HTML code to display employee details.

Script to run the workflow from backend

DECLARE
   l_itemtype            varchar2 (8)                := 'XXHTM_N';
   l_process             varchar2 (80)               := 'XX_HTM_P';
   l_itemkey             VARCHAR2 (20)               := '1234-1'; --this should be unique
   l_user_key            VARCHAR2 (20)               := '123';
   l_document_id 		 CLOB;
--
--
BEGIN
 
--
--Creating Workflow Process
--
   wf_engine.createprocess (itemtype        => l_itemtype,
                            itemkey         => l_itemkey,
                            process         => l_process,
                            user_key        => l_user_key,
                            owner_role      => 'SYSADMIN'
                           );
--
--Calling PLSQL document for generating HTML code
--
l_document_id := 'PLSQL:XX_EMP_WF_DOC_PKG.XX_CREATE_WF_DOC/' || l_itemkey;						   
--
--Setting Value for document type attribute
--
   wf_engine.setitemattrtext (itemtype      => l_itemtype,
                              itemkey       => l_itemkey,
                              aname         => 'XX_BODY',
                              avalue        => l_document_id
                             );    
--
--Start Process
--
   wf_engine.startprocess (itemtype   => l_itemtype,
                           itemkey    => l_itemkey);
--
--
   commit;
   DBMS_OUTPUT.put_line ('Done!');
--
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
/

What does the above block of code do?

The above code triggers a workflow and sets the document type of attribute of workflow with PL/SQL document which holds the HTML code assigned to workflow message.

Output

You can download the workflow file from this URL: http://www.oracleappsdna.com/uploads/XXHTM_N.wft

Seeded Procedure Signature for Procedure which generated Pl/SQL document

The PL/SQL procedure that generates a PL/SQL document has some seeded parameters which should NOT be altered, below is the syntax:

PROCEDURE <PROCEDURE name> (document_id   IN VARCHAR2,
                            display_type  IN VARCHAR2,
                            document      IN OUT VARCHAR2,
                            document_type IN OUT VARCHAR2)

document_id is nothing but a string that uniquely identifies a document. Usually we can pass Item Key to this parameter.
display_type represents the content type used for the notification presentation
Eg:- ‘text/html’ OR ‘text/plain’
document the outbound text buffer where up to 32K of document text is returned
document_typethe 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

Calling PL/SQL document procedure

Syntax

PL/SQL:<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:

l_document_id := 'PLSQL:XX_EMP_WF_DOC_PKG.XX_CREATE_WF_DOC/' || l_itemkey;

Please leave comments if you need any additional information.