PL/SQL document: Workflow Notification with Dynamic HTML Body
Shailender Thallam
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.
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 := '
Employee Name
Employee Number
';
FOR i IN
(SELECT ename,empno FROM emp
)
LOOP
BEGIN
l_body := l_body || '
'|| i.ename || '
' || i.empno || '
';
END;
END LOOP;
document := l_body;
--
--Setting document type which is nothing but MIME type
--
document_type := 'text/html';
EXCEPTION
WHEN OTHERS THEN
document := '
Error: '|| sqlerrm || '
';
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.
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 (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 > 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:
