Oracle Workflow Developer's Guide Release 2.6.4 Part Number B15853-01

Contents

Previous

Next

Defining Procedures and Functions for Oracle Workflow

Defining Procedures and Functions for Oracle Workflow

Oracle Workflow lets you integrate your own custom PL/SQL and Java procedures and functions at certain points in your workflow processes and in the Business Event System. To ensure that Oracle Workflow can properly execute your custom code, follow these standard APIs when developing your procedures and functions.

• Standard API for PL/SQL Procedures Called by Function Activities

• Standard API for Java Procedures Called by Function Activities

• Standard API for an Item Type Selector or Callback Function

• Standard APIs for "PL/SQL" Documents

• Standard API for an Event Data Generate Function

• Standard APIs for a Queue Handler

• Standard API for an Event Subscription Rule Function

Standard API for PL/SQL Procedures Called by Function Activities

All PL/SQL stored procedures that are called by function or notification activities in an Oracle Workflow process should follow this standard API format so that the Workflow Engine can properly execute the activity.

Attention: The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. For this reason, you should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.

For environments such as database triggers or distributed transactions that do not allow savepoints, the Workflow Engine automatically traps "Savepoint not allowed" errors and defers the execution of the activity to the background engine.

The example in this section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  procedure <procedure name> 
       (itemtype in varchar2,
        itemkey in varchar2,
        actid in number,
        funcmode in varchar2,
        resultout out varchar2) is

(2)  <local declarations> 

(3)  begin

     if ( funcmode = 'RUN' ) then
       <your RUN executable statements> 
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(4)  if ( funcmode = 'CANCEL' ) then
       <your CANCEL executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(5)  if ( funcmode = 'SKIP' ) then
       <your SKIP executable statements>  
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(6)  if ( funcmode = 'RETRY' ) then
       <your RETRY executable statements>  
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(7)  if ( funcmode = 'VALIDATE' ) then
       <your VALIDATE executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(8)  if ( funcmode = 'RESPOND' ) then
       <your RESPOND executable statements> 
       resultout := 'COMPLETE';
       return;
     end if;

(9)  if ( funcmode = 'FORWARD' ) then
       <your FORWARD executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(10) if ( funcmode = 'TRANSFER' ) then
       <your TRANSFER executable statements> 
       resultout := 'COMPLETE';
       return;
     end if;

(11) if ( funcmode = 'QUESTION' ) then
       <your QUESTION executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(12) if ( funcmode = 'ANSWER' ) then
       <your ANSWER executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(13) if ( funcmode = 'TIMEOUT' ) then
       <your TIMEOUT executable statements>  
       if (<condition_ok_to_proceed>) then
         resultout := 'COMPLETE';
       else
         resultout := wf_engine.eng_timedout;
       end if;
       return;
     end if;

(14) if ( funcmode = '<other funcmode>' ) then
       resultout := ' ';
       return;
     end if;

(15) exception
       when others then
       WF_CORE.CONTEXT ('<package name>', '<procedure name>', 
         <itemtype>, <itemkey>, 
         to_char(<actid>), <funcmode>);
       raise;

(16) end <procedure name>;

(1) When the Workflow Engine calls a stored procedure for a function activity, it passes four parameters to the procedure and may expect a result when the procedure completes. The parameters are defined here:

(2) This section declares any local arguments that are used within the procedure.

(3) The procedure body begins in this section with an IF statement. This section contains one or more executable statements that run if the value of funcmode is 'RUN'. One of the executable statements can return a result for the procedure. For example, a result can be 'COMPLETE:APPROVED'.

Note: The Workflow Engine automatically runs a post-notification function in RUN mode after the Notification System completes execution of the post-notification function in RESPOND mode. The RUN mode executable statements can perform processing such as vote tallying and determine what result to return for the notification activity.

(4) This section clears the activity and can contain executable statements that run if the value of funcmode is 'CANCEL'. Often, this section contains no executable statements to simply return a null value, but this section also provides you with the chance to 'undo' something if necessary. An activity can have a funcmode of 'CANCEL' in the special case where the activity is part of a loop that is being revisited.

The first activity in a loop must always have the Loop Reset option set in the activity properties Detail page. When the Workflow Engine encounters an activity that has already run, it verifies the activity's Loop Reset option. If this option is set to Reset, the engine then identifies the activities that belong in that loop and sets funcmode to 'CANCEL' for those activities. Next, the engine transitions through the loop in forward order and executes each activity in 'CANCEL' mode to clear all prior results for the activities so they can run again. See: Looping, Oracle Workflow API Reference and Loop Counter Activity.

(5) This section can contain executable statements that run if the value of funcmode is 'SKIP'. For example, you can validate the result supplied for the skipped activity or notify an administrator that the activity was skipped. An activity can have a funcmode of 'SKIP' if a user skips the activity from the Status Monitor, from an error notification, or using the WF_ENGINE.HandleError API.

Note: If you want to prevent users from skipping the activity, return a result of '#NOSKIP' or wf_engine.eng_noskip instead of 'COMPLETE:<result>'. When you set the result to specify that the activity cannot be skipped, Oracle Workflow raises an error instead of executing the skip operation. For example, set the result as follows:

resultout := wf_engine.eng_noskip;

(6) This section can contain executable statements that run if the value of funcmode is 'RETRY'. For example, you can verify that the retried activity is eligible to be reexecuted. An activity can have a funcmode of 'RETRY' if a user retries the activity from the Status Monitor, from an error notification, or using the WF_ENGINE.HandleError API.

(7) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'VALIDATE'. This mode is executed before 'RESPOND' mode, if you define this section in your post-notification function. For example, include execution statements that validate the response values before accepting and recording the response.

(8) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'RESPOND', that is, when a RESPOND operation is performed. For example, include execution statements that validate the response to the notification. After the Notification System completes execution of the post-notification function in RESPOND mode, the Workflow Engine then runs the post-notification function again in RUN mode. See: Post-Notification Functions, Oracle Workflow API Reference.

(9) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'FORWARD', that is, when a notification's state changes to 'FORWARD'. For example, include execution statements that validate the role to which the notification is being forwarded.

(10) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'TRANSFER', that is, when a notification's state changes to 'TRANSFER'. For example, include execution statements that validate the role to which the notification is being transferred.

(11) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'QUESTION', that is, when a user requests more information about a notification from another user. For example, include execution statements that validate the role to which the request for more information is being sent.

(12) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'ANSWER', that is, when a user answers a request. For example, include execution statements that validate the answering information.

Note: For the 'VALIDATE', 'RESPOND', 'FORWARD', 'TRANSFER', 'QUESTION', and 'ANSWER' modes, the resultout parameter is ignored, unless the returned value looks something like 'ERROR%'. Therefore, if you do not want the Validate, Respond, Forward, Transfer, Question, or Answer operation to occur after having executed your post-notification function, you can do one of two things:

• Return 'ERROR:<errcode>' in the resultout parameter to convert it to a generic exception with the error code mentioned in the message.

• Raise an exception directly in your procedure with a more informative error message. See: Post-Notification Functions, Oracle Workflow API Reference and Notification Model, Oracle Workflow API Reference.

(13) This section is needed only for post-notification functions. Use this section to include execution statements that run if a notification activity times out. You can include logic to test whether the workflow can proceed normally, and if so, to complete the activity so that the workflow can continue to the next activity. For example, if a Voting activity times out before all recipients respond, you can include logic that determines how to interpret the responses based on the current response pool and completes the activity with the appropriate result.

You should also include logic to return a result of wf_engine.eng_timedout if the workflow cannot proceed normally. Model any subsequent behavior in your process diagram using a <Timeout> transition to another activity. The Workflow Engine will follow the <Timeout> transition when the result wf_engine.eng_timedout is returned.

(14) This section handles execution modes other than those already specified. Other execution modes may be added in the future. Since your activity does not need to implement any of these other possible modes, it should simply return null.

(15) This section calls WF_CORE.CONTEXT() if an exception occurs, so that you can include context information in the error stack to help you locate the source of an error. See: CONTEXT, Oracle Workflow API Reference.

Standard API for Java Procedures Called by Function Activities

In standalone Oracle Workflow, you can create custom Java classes to be called by external Java function activities in an Oracle Workflow process. Java procedures that are called by function activities are implemented as classes that extend the WFFunctionAPI class. The custom Java classes should follow a standard API format so that they can be properly executed by the Oracle Workflow Java Function Activity Agent.

Note: This functionality is currently only available for the standalone version of Oracle Workflow. It is not available in Oracle Applications.

Attention: The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. For this reason, just as with PL/SQL procedures, you should never commit within the Java procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.

Many Workflow Engine and Notification APIs have corresponding Java methods that your Java program can call to communicate with Oracle Workflow. The WFFunctionAPI and WFAttribute classes also contain methods that your Java program can call to access item type and activity attributes. See: Oracle Workflow Java Interface, Oracle Workflow API Reference, Workflow Function APIs, Oracle Workflow API Reference, and Workflow Attribute APIs, Oracle Workflow API Reference.

To invoke a custom Java class from within a workflow process, create an external Java function activity that calls the class. See: To Create a Function Activity.

Java function activities are implemented as external procedures. When the Workflow Engine reaches an external Java function activity, the Workflow Engine places a message on the Workflow 'Outbound' queue. The Java Function Activity Agent monitors this queue and calls the class indicated in the Function Name property for the function activity. When the Java procedure is complete, the Java Function Activity Agent enqueues the results onto the 'Inbound' queue. See: Setting Up the Java Function Activity Agent, Oracle Workflow Administrator's Guide.

Note: These 'Outbound' and 'Inbound' queues are separate from the queues used for the Business Event System. See: Workflow Queue APIs, Oracle Workflow API Reference.

After a Java procedure completes, you must run a background engine to process the 'Inbound' queue and complete the function activity. Otherwise, the activity will remain in the DEFERRED status. See: Setting Up Background Engines, Oracle Workflow Administrator's Guide.

You must include the JAR files containing your custom classes in your CLASSPATH to make the classes accessible to the Java Function Activity Agent. The custom class files should reside on the same platform where the Java Function Activity Agent is run. The Java Function Activity Agent does not need to reside on the same tier as the database, however.

The example in this section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  package oracle.apps.fnd.wf;

(2)  import java.io.*; 
     import java.sql.*; 
     import java.math.BigDecimal; 
     import oracle.sql.*; 
     import oracle.jdbc.driver.*;

     import oracle.apps.fnd.common.*; 
     import oracle.apps.fnd.wf.engine.*; 
     import oracle.apps.fnd.wf.*; 

(3)  public class className extends WFFunctionAPI {

(4)    public boolean execute(WFContext pWCtx){

(5)      ErrorStack es = pWCtx.getWFErrorStack(); 
         try
         {

(6)        WFAttribute lAAttr = new WFAttribute(); 
           WFAttribute lIAttr = new WFAttribute(); 

(7)        loadActivityAttributes(pWCtx, itemType, 
             itemKey, actID); 
           loadItemAttributes(pWCtx); 

(8)        lAAttr = getActivityAttr("AATTR"); 
           lIAttr = getItemAttr("IATTR"); 

(9)        <your executable statements> 

(10)       lIAttr.value((Object)"NEWVALUE"); 
           setItemAttrValue(pWCtx, lIAttr); 

(11)     }
         catch (Exception e) 
         { 
           es.addMessage("WF","WF_FN_ERROR"); 
           es.addToken("MODULE",this.getClass().getName()); 
           es.addToken("ITEMTYPE",itemType); 
           es.addToken("ITEMKEY",itemKey); 
           es.addToken("ACTID",actID.toString()); 
           es.addToken("FUNCMODE",funcMode); 
           es.addToken("ERRMESSAGE",e.getMessage()); 
           return false; 
         }

(12)     return true; 
       }
     }

(1) By default, Java classes supplied by Oracle Workflow will be in the oracle.apps.fnd.wf package. This section is optional.

(2) For correct operation, you must include the listed packages.

(3) The custom Java class must extend the WFFunctionAPI class. This class provides class variables and methods that are essential to the operation of your function activity.

The parameters that are normally supplied to a PL/SQL function activity are available to the custom class as class variables. They are initialized prior to the call of the boolean execute() method. The resultOut and the errorStack are then passed back to the Oracle Workflow Engine.

The status of the completed activity will be set to COMPLETE unless a value is present in the errorStack variable. If there is a value in this variable, then the activity status will be set to ERROR. The contents of the errorStack variable can be set by using the ErrorStack class within the WFContext class. Refer also to sections 5 and 11 of this API for catching exceptions.

The predefined class variables include:

(4) The custom Java class must implement the boolean execute() method. This will be the main entry point for your Java class. On successful completion, this method should return true.

(5) It is important to catch exceptions with your custom Java class and pass them back to the engine via the ErrorStack class. Refer also to section 11 of this API for catching exceptions.

(6) To access item and activity attributes, a WFAttribute class is provided.

(7) The values of the item attributes are not automatically available to the Java class. They are loaded on demand. The values can be loaded explicitly with the void loadItemAttributes(WFContext) or void loadActivityAttributes(WFContext) methods. The values are also loaded implicitly when you call the WFAttribute getItemAttr(String) or WFAttribute getActivityAttr(String) methods. This section is optional.

(8) The actual values of the item and activity attributes are accessed via the WFAttribute getItemAttr(String) and WFAttribute getActivityAttr(String) methods. If you have not explicitly loaded the values of the attributes, they will be automatically loaded at this point.

(9) This section contains your own executable statements. Usually, you add these executable statements after retrieving the required item and activity attribute details (section 8) and before setting item attribute values (section 10).

(10) Setting the value of an item attribute with the void setItemAttrValue(WFContext, WFAttribute) method writes the value of your local WFAttribute to the database. You need to set the values of the WFAttribute class with the WFAttribute.value(Object) method.

(11) It is important to catch exceptions within your custom Java class and pass them back to the engine via the ErrorStack class.

An unsuccessful execution of the external Java function activity will return false.

Note that any message in the WFContext.ErrorStack class variable will be passed back to the Workflow Engine and will cause the activity to be assigned a completion status of ERROR.

(12) A successfully executed external Java function activity will return true.

Standard API for an Item Type Selector or Callback Function

For any given item type, you can define a single function that operates as both a selector and a callback function. A selector function is a PL/SQL procedure that automatically identifies the specific process definition to execute when a workflow is initiated for a particular item type but no process name is provided. Oracle Workflow also supports using a callback function to reset or test item type context information. You can define one PL/SQL procedure that includes both selector and callback functionality by following a standard API.

Oracle Workflow can call the selector/callback function with the following commands:

• RUN - to select the appropriate process to start when either of the following two conditions occur:

  • A process is not explicitly passed to WF_ENGINE.CreateProcess.

  • A process is implicitly started by WF_ENGINE.CompleteActivity with no prior call to WF_ENGINE.CreateProcess.

• SET_CTX - to establish any context information for an item type and item key combination that a function activity in the item type needs in order to execute. The Workflow Engine calls the selector/callback function with this command each time it encounters a new item type and item key combination, to ensure that the correct context information is always set.

• TEST_CTX - to determine if the current item type context information is correct before executing a function. For example, the selector/callback function in TEST_CTX mode lets you check if a form can be launched with the current context information just before the Notification Details Web page launches a reference form. If the context is incorrect, the form cannot be launched and a message is displayed to that effect. See: To View the Details of a Notification, Oracle Workflow User's Guide.

The standard API for the selector/callback function is as follows. The example in this section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  procedure <procedure name> 
       (item_type in varchar2,
        item_key in varchar2,
        activity_id in number,
        command in varchar2,
        resultout in out varchar2) is

(2)  <local declarations> 

(3)  begin

     if ( command = 'RUN' ) then
       <your RUN executable statements> 
       resultout := '<Name of process to run>';
       return;
     end if;

(4)  if ( command = 'SET_CTX' ) then
       <your executable statements for establishing 
         context information>  
       return;
     end if;

(5)  if ( command = 'TEST_CTX' ) then
       <your executable statements for testing 
         the validity of the current context information> 
       resultout := '<TRUE or FALSE or NOTSET> ';
       return;
     end if;

(6)  if ( command = '<other command>' ) then
       resultout := ' ';
       return;
     end if;

(7)  exception
       when others then
       WF_CORE.CONTEXT ('<package name>', '<procedure name>', 
         <itemtype>,<itemkey>, 
         to_char(<actid>), <command>);
       raise;

(8)  end <procedure name>;

(1) When the Workflow Engine calls the selector/callback function, it passes four parameters to the procedure and may expect a result when the procedure completes. The parameters are defined here:

(2) This section declares any local arguments that are used within the procedure.

(3) The procedure body begins in this section with an IF statement. This section contains one or more executable statements that make up your selector function. It executes if the value of command is 'RUN'. One of the executable statements should return a result for the procedure that reflects the process to run. For example, a result can be 'REQUISITION_APPROVAL', which is the name of an example process activity.

(4) This section contains one or more executable statements that set item type context information if the value of command is 'SET_CTX'. The Workflow Engine calls the selector/callback function with this command each time it encounters a new item type and item key combination, before executing any function activities for that combination. This command is useful when you need to set item type context information in a database session before the activities in that session can execute as intended. For example, you might need to set up the responsibility and organization context for function activities that are sensitive to multi-organization data.

(5) This section contains one or more executable statements that validate item type context information if the value of command is 'TEST_CTX'. The Workflow Engine calls the selector/callback function with this command to validate that the current database session context is acceptable before the Workflow Engine executes an activity. For example, this callback functionality executes just before the Notification Details Web page launches a reference form. The code in this section should return 'TRUE' if the context is correct, 'FALSE' if the context is incorrect, or 'NOTSET' if the context has not been initialized yet.

• If the result is 'TRUE', the Workflow Engine keeps the current context.

• If the result is 'NOTSET', the Workflow Engine runs the function in 'SET_CTX' mode to set the context.

• If the result is 'FALSE' and the Workflow Engine permits context switching at this point in its processing, it runs the function in 'SET_CTX' mode to set the correct context. However, if the result is 'FALSE' but the Workflow Engine requires the current context to be preserved, it defers the activity to be processed by a background engine instead.

(6) This section handles execution modes other than 'RUN', 'SET_CTX' or 'TEST_CTX' as others may be added in the future. Since your function does not need to implement any of these other possible commands, it should simply return null.

(7) This section calls WF_CORE.CONTEXT() if an exception occurs, so that you can include context information in the error stack to help you locate the source of an error. See: CONTEXT, Oracle Workflow API Reference.

Standard APIs for "PL/SQL" Documents

You can integrate a document into a workflow process by defining an attribute of type document for an item type, message, or activity. Oracle Workflow supports document types called "PL/SQL" documents, "PL/SQL CLOB" documents, and "PL/SQL BLOB" documents. A PL/SQL document represents data as a character string, a PL/SQL CLOB document represents data as a character large object (CLOB), and a PL/SQL BLOB document represents data as a binary large object (BLOB).

The document-type attribute that you create tells Oracle Workflow how to construct a dynamic call to a PL/SQL procedure that generates the document. You can embed a PL/SQL or PL/SQL CLOB document-type message attribute in a message body to display the document in a notification. You can also attach a PL/SQL, PL/SQL CLOB, or PL/SQL BLOB document-type message attribute to a message to include the document as an attachment to the notification.

The PL/SQL procedures that generate PL/SQL, PL/SQL CLOB, and PL/SQL BLOB documents must follow standard API formats.

Note: If you create a PL/SQL document or a PL/SQL CLOB document that contains HTML, you should take security precautions to ensure that only the HTML code you intend to include is executed. If you retrieve any data from the database at runtime for inclusion in the document, use the WF_NOTIFICATION.SubstituteSpecialChars() API to substitute entity references for any HTML tag characters in that data, so that those characters will not be interpreted as HTML code and executed. See: SubstituteSpecialChars, Oracle Workflow API Reference.

Note that you should not substitute entity references for HTML tags that you include in the document yourself. Otherwise, the document will not be displayed with your intended HTML formatting. You only need to perform this substitution for data that is retrieved from the database at runtime, which may be entered from an external source.

Related Topics

"PL/SQL" Documents

"PL/SQL CLOB" Documents

"PL/SQL BLOB" Documents

"PL/SQL" Documents

The PL/SQL procedure that generates a PL/SQL document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out varchar2,
   document_type in out varchar2)

The arguments for the procedure are as follows:

Related Topics

To Define a Document Attribute

"PL/SQL CLOB" Documents

The PL/SQL procedure that generates a PL/SQL CLOB document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out clob,
   document_type in out varchar2)

A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document or, if your database version is Oracle9i Database or higher, other binary data that is encoded to base64. You should first store the document in the database as a binary large object (BLOB) and then convert the document into a CLOB as part of the PL/SQL procedure that generates the CLOB. You can use the UTL_RAW.Cast_To_VARCHAR2 function to convert the data from the BLOB into VARCHAR2 data that you write to a CLOB. If your database version is Oracle9i Database or higher, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the binary data to base64. See: UTL_RAW, Oracle Supplied PL/SQL Packages and Types Reference and EncodeBLOB, Oracle Workflow API Reference.

Note: You can call WF_NOTIFICATION.WriteToClob() to help build a CLOB by appending a string of character data to it. See: WriteToClob, Oracle Workflow API Reference.

Note: Oracle8i Database does not support base64 encoding, so if you are using Oracle8i Database, the WF_MAIL_UTIL.EncodeBLOB procedure is not available, and you cannot store binary data other than PDF or RTF documents in a PL/SQL CLOB document. This feature is available only if you are using Oracle9i Database or higher.

However, both Oracle8i Database and Oracle9i Database and higher support the UTL_RAW.Cast_To_VARCHAR2 function, so you can store PDF and RTF documents in an attached PL/SQL CLOB document on any of these database versions.

The arguments for the procedure are as follows:

document_type := 'application/pdf; name=filename.pdf';

document_type := 'image/jpg; encoding=base64; name=filename.jpg';

Example

The following example shows a sample procedure to produce a PL/SQL CLOB document that contains a PDF or RTF document, using the UTL_RAW.Cast_To_VARCHAR2 function to convert a BLOB to a CLOB.

procedure cdoc 
  (document_id in varchar2,
   display_type in varchar2,
   document in out clob,
   document_type in out varchar2)

is

  bdoc blob;
  cdoc clob;
  content_type varchar2(100);

  lob_id number;
  amount number;

  bdoc_size number;
  block number := 10000;
  blockCount number;
  rawBuff RAW(32000);
  pos number;
  charBuff varchar2(32000);
  charBuff_size number;

  filename varchar2(200);
  location varchar2(200);
  data_type varchar2(100);

begin

  dbms_lob.createTemporary(cdoc, FALSE, dbms_lob.call);

  -- Determine the document to display from the 
  -- Document ID
  lob_id := to_number(document_id);

  -- Obtain the BLOB version of the document
  select filename, location, content_type, data_type, bdata 
  into filename, location, content_type, data_type, bdoc
  from sjm_lobs 
  where id = lob_id;

  -- recast the BLOB to a CLOB   
  bdoc_size := dbms_lob.getLength(bdoc);
  if block < bdoc_size then
    blockCount := round((bdoc_size/block)+0.5);
  else 
    blockCount := 1;
  end if;
  pos := 1;
  for i in 1..blockCount loop
    dbms_lob.read(bdoc, block, pos, rawBuff);
    charBuff := utl_raw.cast_to_varchar2(rawBuff);
    charbuff_size := length(charBuff);
    dbms_lob.writeAppend(cdoc, charbuff_size, charBuff);
    pos := pos + block;
  end loop;

  -- Now copy the content to the document
  amount := dbms_lob.getLength(cdoc);
  dbms_lob.copy(document, cdoc, amount, 1, 1);

  -- Set the MIME type as a part of the document_type.
  document_type := content_type||'; name='||filename;

  exception
    when others then 
      wf_core.context('LOBDOC_PKG', 'cdoc', 
        document_id, display_type);
      raise;

end cdoc;

Related Topics

To Define a Document Attribute

"PL/SQL BLOB" Documents

The PL/SQL procedure that generates a PL/SQL BLOB document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out blob,
   document_type in out varchar2)

The arguments for the procedure are as follows:

document_type := 'image/jpg; name=filename.jpg';

Related Topics

To Define a Document Attribute

Standard API for an Event Data Generate Function

When you define an event in the Business Event System, you can assign the event a generate function that can produce the complete event data from the event name, event key, and an optional parameter list. The event data gives additional details to describe what occurred and can be structured as an XML document. You should specify a generate function if the application that raises the event will not produce the event data itself.

When an event is raised locally, the Event Manager checks each subscription before executing it to determine whether the subscription requires the event data. If the event data is required but is not already provided, the Event Manager calls the generate function for the event to produce the event data. The generate function returns the event data in character large object (CLOB) format.

Note: If the event data is required but no generate function is defined for the event, Oracle Workflow creates a default set of event data using the event name and event key.

Note: If the generate function is costly, and you want to return control to the calling application more quickly after raising the event, you can defer all the subscriptions that require the complete event data. Then the Event Manager will not run the generate function until those subscriptions are executed at a later time. See: Deferred Subscription Processing.

Related Topics

To Define an Event

To Define an Event Subscription

Parameter List Structure, Oracle Workflow API Reference

Standard API for a PL/SQL Generate Function

A PL/SQL generate function must have the following standard API:

function <function_name> 
  (p_event_name in varchar2,
   p_event_key in varchar2
   p_parameter_list in wf_parameter_list_t default null) 
  return clob;

The arguments for the function are as follows:

Standard API for a Java Generate Function

In Oracle Applications only, you can assign an event a Java generate function to be executed in the middle tier, instead of a PL/SQL generate function. A Java generate function must be a Java class using the following Java interface:

public interface GenerateInterface
{
  public String generate(BusinessEvent event, 
      WorkflowContext context)
    throws BusinessEventException;
}

The arguments for the API are as follows:

For more information, see the Oracle Workflow Javadoc.

Standard APIs for a Queue Handler

When you define an agent in the Business Event System, you must assign the agent a queue handler. The queue handler is a package that translates between the standard Workflow event message format defined by the WF_EVENT_T datatype and the message format required by the queue associated with the agent. In Oracle Applications, a Java queue handler translates between the BusinessEvent object format and the queue's message format.

Oracle Workflow provides two standard queue handlers for queues that use the WF_EVENT_T format, WF_EVENT_QH for normal processing and WF_ERROR_QH for error queues. Oracle Workflow also provides a standard queue handler named WF_EVENT_OJMSTEXT_QH for queues that use JMS Text messages as their payload format.

You can also create your own custom queue handlers for queues that use other formats. If you create a custom queue handler, you must provide standard enqueue and dequeue APIs in your package. Java queue handlers must also include some other standard APIs.

Related Topics

Event Message Structure, Oracle Workflow API Reference

Agents (for standalone Oracle Workflow)

Agents (for Oracle Applications)

Mapping Between WF_EVENT_T and SYS.AQ$_JMS_TEXT_MESSAGE, Oracle Workflow API Reference

Standard APIs for a PL/SQL Queue Handler

Enqueue

The Enqueue procedure in a queue handler package must enqueue an event message onto a queue associated with an outbound agent. You can optionally specify an override agent where you want to enqueue the event message. Otherwise, the event message is enqueued on the From Agent specified within the message. The Enqueue procedure transforms the event message's header information if necessary to enqueue the message in the format required by the queue.

When an event message is being sent, the generic WF_EVENT.Enqueue procedure determines which queue handler is associated with the specified outbound agent and calls the Enqueue procedure in that queue handler to enqueue the message.

The PL/SQL Enqueue procedure must have the following standard API:

procedure enqueue 
  (p_event in WF_EVENT_T,
   p_out_agent_override in WF_AGENT_T);

The arguments for the procedure are as follows:

Dequeue

The Dequeue procedure in a queue handler package must dequeue an event message from the queue associated with the specified inbound agent, selecting the message to dequeue by the message priority. The procedure transforms the event message's header information if necessary and returns the event message in the standard WF_EVENT_T structure. Additionally, the Dequeue procedure can set the date and time when the message is dequeued into the RECEIVE_DATE attribute of the event message.

When an event message is being received, the WF_EVENT.Listen procedure determines which queue handler to use with the specified inbound agent and calls the Dequeue procedure in that queue handler to dequeue the message.

The PL/SQL Dequeue procedure must have the following standard API:

procedure dequeue 
  (p_agent_guid in raw,
   p_event out WF_EVENT_T);

The arguments for the procedure are as follows:

Standard APIs for a Java Queue Handler

In Oracle Applications only, you can provide a Java queue handler to be executed during Java event message processing in the middle tier, instead of a PL/SQL queue handler. A Java queue handler must be a Java class using the following Java interface:

public interface QueueHandlerInterface
{
  public void init(Connection conn,
                   AgentEO agent,
                   Log log,  
                   String uniqueLogId,
                   Properties props)
    throws QueueHandlerException;

  public String enqueue(BusinessEvent event) 
    throws QueueHandlerException;

  public BusinessEvent dequeue()
    throws QueueHandlerException;

  public void destroy(); 

  public String getMsgId()
    throws QueueHandlerException;

  public CLOB getEventData();
}

In addition to the enqueue and dequeue APIs, a Java queue handler must also contain methods to initialize the connection to the agent, destroy objects created during queue processing after the processing is complete, retrieve a message ID from a message on the queue, and retrieve the CLOB reference to the event data of the last business event dequeued by the queue handler.

The arguments for the init method are as follows:

The argument for the enqueue method is as follows:

For more information, see the Oracle Workflow Javadoc.

Standard API for an Event Subscription Rule Function

When you define an event subscription, you choose a function called a rule function to run on the event message. Oracle Workflow provides a standard default rule function named WF_RULE.Default_Rule to perform basic subscription processing. The default rule function includes the following actions:

• Sending the event message to a workflow process, if specified in the subscription definition

• Sending the event message to an agent, if specified in the subscription definition

See: Default_Rule, Oracle Workflow API Reference.

Oracle Workflow also provides additional standard rule functions that you can use for advanced processing, testing, debugging, or other purposes. Commonly used rule functions including the following:

• Default_Rule2 - Performs the basic subscription processing only if the parameter list within the event message includes parameters whose names and values match all the parameters defined for the subscription. See: Default_Rule2, Oracle Workflow API Reference.

• Default_Rule3 - Sets the parameter name and value pairs from the subscription parameters into the parameter list within the event message before performing the basic subscription processing. See: Default_Rule3, Oracle Workflow API Reference.

• SendNotification - Sends a notification as specified by the parameter list within the event message. This rule function lets you send a notification outside of a workflow process. See: SendNotification, Oracle Workflow API Reference.

• Instance_Default_Rule - Sends the event to all existing workflow processes that have eligible receive event activities waiting to receive it, identified by a business key attribute. See: Instance_Default_Rule, Oracle Workflow API Reference.

See: Event Subscription Rule APIs, Oracle Workflow API Reference.

You can extend your subscription processing by creating custom rule functions. Custom rule functions must be defined according to a standard API.

A rule function may read from or write to the event message or perform any other database action. However, you should never commit within a rule function. The Event Manager never issues a commit as it is the responsibility of the calling application to commit. Additionally, the rule function must not change the connection context in any way, including security and NLS settings.

Note: If your rule function writes to the event message, any subsequent subscriptions executed on the event will access the changed message.

If the subscription processing that you want to perform for an event includes several successive steps, you may find it advantageous to define multiple subscriptions to the event with simple rule functions that you can reuse, rather than creating complex specialized rule functions that cannot be reused. You can use the phase numbers for the subscriptions to specify the order in which they should be executed.

By default, the Event Manager uses the event key as the correlation ID for the event message when no other correlation ID is specified. If you want to specify a different correlation ID, you can use WF_EVENT_FUNCTIONS_PKG.AddCorrelation to add a correlation ID to the event message, either by calling this function within your custom rule function or by defining another subscription that uses WF_EVENT_FUNCTIONS_PKG.AddCorrelation as its rule function. See: AddCorrelation, Oracle Workflow API Reference.

If you want to send the event message to a workflow process or to an agent after running custom code on the message, you must either include the send processing in your rule function, or define a separate subscription that uses the default rule function to perform the send processing.

• Call WF_ENGINE.Event() to send the event message to a workflow process.

• Call WF_EVENT.Send() to send the event message to an agent.

• Call WF_RULE.Default_Rule() to include the default subscription processing that can send the event message both to a workflow process and to an agent.

Note: When you define a subscription in the Event Manager, you can define the workflow item type, workflow process name, out agent, to agent, priority, and parameters for your send processing, as well as defining the rule function. Any rule function can access these send attributes, but if you do not use the default rule function, you must explicitly include the send processing in your custom rule function if you want to send the event from the same subscription.

Related Topics

To Define an Event Subscription

Event Message Structure, Oracle Workflow API Reference

Workflow Core APIs, Oracle Workflow API Reference

Event Subscription Rule APIs, Oracle Workflow API Reference

Event(), Oracle Workflow API Reference

Send(), Oracle Workflow API Reference

Default_Rule, Oracle Workflow API Reference

SetErrorInfo(), Oracle Workflow API Reference

Standard API for a PL/SQL Subscription Rule Function

The standard API for a PL/SQL rule function is as follows. This section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  function <function_name> 
       (p_subscription_guid in raw,
        p_event in out WF_EVENT_T) 
       return varchar2 is

(2)  <local declarations> 

(3)  begin
       <your executable statements> 

(4)  <optional code for WARNING> 
       WF_CORE.CONTEXT('<package name>', 
                       '<function name>', 
                       p_event.getEventName( ), 
                       p_subscription_guid);
       WF_EVENT.setErrorInfo(p_event, 'WARNING');
       return 'WARNING';

(5)  return 'SUCCESS';

(6)  exception
       when others then
         WF_CORE.CONTEXT('<package name>', 
                         '<function name>', 
                         p_event.getEventName( ), 
                         p_subscription_guid);
         WF_EVENT.setErrorInfo(p_event, 'ERROR');
         return 'ERROR';

(7)  end;

(1) When the Event Manager calls the rule function, it passes two parameters to the function and expects a return code when the function completes. The parameters are defined here:

The function must return one of the following status codes:

• SUCCESS - The rule function completed successfully.

• WARNING - A warning condition occurred. The rule function reports a warning message using the Workflow Core error APIs and sets the warning information into the event message. The Event Manager places a copy of the event message on the WF_ERROR queue, but subscription processing continues.

• ERROR - An error occurred. The rule function reports an error message using the Workflow Core error APIs and sets the error information into the event message. The Event Manager halts subscription processing for this event, rolls back any subscriptions already executed for the event, and places the event message on the WF_ERROR queue.

(2) This section declares any local arguments that are used within the function.

(3) The procedure body begins in this section with one or more executable statements that make up your rule function.

(4) This optional section calls WF_CORE.CONTEXT() if a warning condition occurs, so that you can include context information in the error stack to help you locate the source of an error. It also sets the warning information into the event message and returns the status code 'WARNING'. See: CONTEXT, Oracle Workflow API Reference.

(5) This section returns the status code 'SUCCESS' when the rule function's normal processing completes successfully.

(6) This section calls WF_CORE.CONTEXT() if an exception occurs, so that you can include context information in the error stack to help you locate the source of an error. It also sets the error information into the event message and returns the status code 'ERROR'. See: CONTEXT, Oracle Workflow API Reference.

Note: If you raise an exception in the rule function, the Event Manager rolls back all subscription processing for the event and raises the error to the calling application. In this case the event message is not placed on the WF_ERROR queue.

Standard API for a Java Subscription Rule Function

In Oracle Applications only, you can provide a Java subscription rule function to be executed in the middle tier, instead of a PL/SQL rule function. A Java rule function must be a Java class using the following Java interface:

public interface SubscriptionInterface
{
  void onBusinessEvent(Subscription eo, BusinessEvent event,
                       WorkflowContext ctx)
    throws BusinessEventException;
}

The arguments for the API are as follows:

If the execution of the rule function does not succeed, the subscription can pass the error details to the Event Manager by encapsulating the details in a BusinessEventException.

Ensure that your rule function Java class is part of the JAVA_TOP that is included in the AF_CLASSPATH on the concurrent processing tier for your Oracle Applications installation.

Note: When raising an event from Java, you can also set a serializable object as a payload for the event. The payload is available to all Java subscriptions to the event.

For more information, see the Oracle Workflow Javadoc.

Copyright © 2003, 2005, Oracle. All rights reserved.