Sending Attachment using Oracle Workflow Notification

While you are working with oracle workflow, sending notification with attachment is very common requirement. In this article I have tried to show
this simple process in step by step.

First create a workflow process like fig1 ->

Create one attribute "Attachment2" like fig2.

Drug and drop this attribute under "Attachment Message". Set the Attributes property like fig3.

Your workflow is complete. Now write the following PL/SQL code and run the workflow to get the notification.




CREATE OR REPLACE PACKAGE BODY RCT_EXAMPLE IS


    PROCEDURE start_training_wf(unique_id IN VARCHAR2) IS
        l_itemtype  VARCHAR2(30) := 'RCT_ITM1';
        l_itemkey   VARCHAR2(300);
        l_file_name VARCHAR2(100) := 'holidaylist.xls';
        l_unique_id VARCHAR2(50) := unique_id;
   
    BEGIN
        l_itemkey := 'RCT_ITM1 ' ||
                     to_char(SYSDATE,
                             'dd/mm/yyhh:mm:ss');
        wf_engine.createprocess(l_itemtype,
                                l_itemkey,
                                'RCT_PROCESS');
        wf_engine.setitemattrdocument(itemtype   => l_itemtype,
                                      itemkey    => l_itemkey,
                                      aname      => 'ATTACHMENT2',
                                      documentid => 'PLSQLBLOB:RCT_EXAMPLE.xx_notif_attach_procedure/' ||
                                                    l_file_name);
        wf_engine.startprocess(l_itemtype,
                               l_itemkey);
   
    END;


    PROCEDURE xx_notif_attach_procedure(document_id   IN VARCHAR2,
                                        display_type  IN VARCHAR2,
                                        document      IN OUT BLOB,
                                        document_type IN OUT VARCHAR2) IS
        l_file_name VARCHAR2(100) := document_id;
        bdoc        BLOB;
    BEGIN
       
        document_type := 'Excel' || ';name=' || l_file_name;
   
        SELECT stored_file
          INTO bdoc
          FROM BLOB_TABLE
         WHERE file_name = l_file_name;
   
        dbms_lob.copy(document,
                      bdoc,
                      dbms_lob.getlength(bdoc));
    EXCEPTION
        WHEN OTHERS THEN
           
            wf_core.CONTEXT('xx_g4g_package',
                            'xx_notif_attach_procedure',
                            'document_id',
                            'display_type');
            RAISE;
    END;


END RCT_EXAMPLE;






Note ->


        We are storing our blob file in a table called "blob_table". There is a file whose name is "holidaylist.xls". The table structure is as like fig4.


If you are facing some issues, you can leave a comment below.

Some more article on Oracle workflow, you can find here.