This chapter describes the standard APIs to use for Oracle Workflow PL/SQL and Java procedures and functions.
This chapter covers the following topics:
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.
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.
Important: 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.
Oracle Workflow components that continue workflow processing asynchronously, such as background engines and the Notification System, do issue commits when appropriate on behalf of the calling application.
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.
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, such as during background processing.
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, such as during background processing. 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.
Note: The selector/callback function should return'NOTSET' only when none of the context variables has been initialized. If one or more context variables are set but not all the required variables are set, then the selector/callback function should return 'FALSE'. Likewise, if any context variable is set to an invalid value, then the selector/callback function should return 'FALSE'.
Many context variables for Oracle E-Business Suite, such as FND_GLOBAL.user_id and FND_GLOBAL.resp_id, are already initialized by the Oracle Forms, Oracle Application Framework, or concurrent processing connection. Consequently, with such connections a selector/callback function should usually return 'FALSE' if the context is incorrect, rather than 'NOTSET'. The selector/callback function should return 'NOTSET' only when none of the required context variables is set, which may be the case with a SQL*Plus or adpatch connection that does not initialize Oracle E-Business Suite context variables.
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.
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_CORE.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.
Note: If your PL/SQL document contains date values, you can use the WF_NOTIFICATION_UTIL.GetCalendarDate() API to format the date value according to the calendar preference of the notification recipient. See: WF_NOTIFICATION_UTIL, Oracle Workflow API Reference.
Related Topics
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 nocopy varchar2, document_type in out nocopy varchar2)
The arguments for the procedure are as follows:
| Variable | Description |
|---|---|
| document_id | A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL" document (plsql:<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: plsql:po_wf.show_req/2034. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsql:po_wf.show_req/&POREQ_NUMBER. |
| display_type | One of three values that represents the content type used for the notification presentation, also referred to as the requested type:
|
| document | The outbound text buffer where up to 32K of document text is returned. |
| document_type | The 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. For a document that will be attached to the notification, the value for this argument can also include a file name for the attachment. If you do not specify a file name for the attachment as part of this argument, then Oracle Workflow uses the message attribute display name as the base file name and checks its list of defined MIME types and subtypes to determine the file extension, based on the display_type argument. If the MIME type and subtype specified in the display_type argument are not defined within Oracle Workflow, then the name of the specified subtype is used as the file extension. However, note that for some subtypes the file extension differs from the subtype name. Consequently, you must explicitly specify the file name for the attachment in the document_type parameter if either of the following conditions applies:
To specify a file name as part of the document_type argument, use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command: document_type := 'text/html; name=filename.htm'; Note: If the file name contains a semicolon (;) or an equal sign (=), then you should include a backslash (\) as an escape character preceding the semicolon or equal sign. For example, if the file name is my;document.htm then set the document_type value as follows: document_type := 'text/html; name=my\;document.htm'; |
Related Topics
To Define a Document Attribute
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 nocopy clob, document_type in out nocopy varchar2)
A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document. 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. See: UTL_RAW, Oracle Database PL/SQL Packages and Types Reference.
A PL/SQL CLOB document that you include as an attachment to a notification can also contain any other binary data that is encoded to base64. With this database version, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the PDF, RTF, or other binary data to base64. See: 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.
The arguments for the procedure are as follows:
| Variable | Description |
|---|---|
| document_id | A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL CLOB" document (plsqlclob:<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: plsqlclob:po_wf.show_req_clob/2036. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsqlclob:po_wf.show_req_clob/&POREQ_NUMBER. |
| display_type | One of three values that represents the content type used for the notification presentation, also referred to as the requested type:
|
| document | The outbound LOB locator pointing to where the document text is stored. This locator is a temporary LOB locator, so you must append your document text to this locator rather than overwriting or replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information about LOB locators and storing CLOB data, see DBMS_LOB - Operational Notes, Oracle Database PL/SQL Packages and Types Reference and Temporary LOBs, Oracle Application Developer's Guide - Large Objects (LOBs). |
| document_type | The 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. For a PDF or RTF document, this argument should specify an appropriate Multi-purpose Internet Mail Extension (MIME) type, such as 'application/pdf' or 'application/rtf'. For a document that will be attached to the notification, the value for this argument can also include a file name for the attachment. If you do not specify a file name for the attachment as part of this argument, then Oracle Workflow uses the message attribute display name as the base file name and checks its list of defined MIME types and subtypes to determine the file extension, based on the display_type argument. If the MIME type and subtype specified in the display_type argument are not defined within Oracle Workflow, then the name of the specified subtype is used as the file extension. However, note that for some subtypes the file extension differs from the subtype name. Consequently, you must explicitly specify the file name for the attachment in the document_type parameter if either of the following conditions applies:
To specify a file name as part of the document_type argument, use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command: document_type := 'application/pdf; name=filename.pdf'; Note: If the file name contains a semicolon (;) or an equal sign (=), then you should include a backslash (\) as an escape character preceding the semicolon or equal sign. For example, if the file name is my;document.pdf then set the document_type value as follows: document_type := 'application/pdf; name=my\;document.pdf'; Note: If you are using the WF_MAIL_UTIL.EncodeBLOB API to encode binary data to base64 in order to store the data in this PL/SQL CLOB document, then the document_type parameter should specify an appropriate MIME type with a primary type of either application or image, such as 'application/doc', 'application/pdf', 'image/jpg', or 'image/gif'. The MIME type must be followed by a semicolon (';') and then by the encoding specification 'encoding=base64' with no spaces before or after the equal sign. You can also optionally specify a file name for the attachment as part of this argument. Use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command: 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 nocopy clob,
document_type in out nocopy 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
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 nocopy blob, document_type in out nocopy varchar2)
The arguments for the procedure are as follows:
| Variable | Description |
|---|---|
| document_id | A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL BLOB" document (plsqlblob:<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: plsqlblob:po_wf.show_req_blob/2038. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsqlblob:po_wf.show_req_blob/&POREQ_NUMBER. |
| display_type | For a PL/SQL BLOB document, this value should be ' ' to represent the content type used for the notification presentation, also referred to as the requested type: '<type>/<subtype>' - the document is presented as a separate attachment to the notification. Any content type may be returned. |
| document | The outbound LOB locator pointing to where the document data is stored. This locator is a temporary LOB locator, so you must append your document data to this locator rather than overwriting or replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information about LOB locators and storing BLOB data, see DBMS_LOB - Operational Notes, Oracle Database PL/SQL Packages and Types Reference and Temporary LOBs, Oracle Application Developer's Guide - Large Objects (LOBs). |
| document_type | The 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. This argument should specify an appropriate MIME type with a primary type of either application or image, such as 'application/doc', 'application/pdf', 'image/jpg', or 'image/gif'. The value for this argument can also include a file name for the attachment. If you do not specify a file name for the attachment as part of this argument, then Oracle Workflow uses the message attribute display name as the base file name and checks its list of defined MIME types and subtypes to determine the file extension, based on the display_type argument. If the MIME type and subtype specified in the display_type argument are not defined within Oracle Workflow, then the name of the specified subtype is used as the file extension. However, note that for some subtypes the file extension differs from the subtype name. Consequently, you must explicitly specify the file name for the attachment in the document_type parameter if either of the following conditions applies:
To specify a file name as part of the document_type argument, use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document_type with the following command: document_type := 'image/jpg; name=filename.jpg'; Note: If the file name contains a semicolon (;) or an equal sign (=), then you should include a backslash (\) as an escape character preceding the semicolon or equal sign. For example, if the file name is my;image.jpg then set the document_type value as follows: document_type := 'image/jpg; name=my\;image.jpg'; |
Example
The following example shows a sample procedure to produce a PL/SQL BLOB document that contains a binary file such as an image or a PDF document.
Note: This example shows a simple API used for testing purposes. In a production instance, the API that produces a PL/SQL BLOB document should further validate the syntax of the document_type parameter when appending the base64 encoding specification.
/* API to produce a PL/SQL BLOB document for use as a
notification attachment based on the specified
document ID. */
procedure getBlobDoc
(document_id in varchar2,
content_type in varchar2,
document in out nocopy blob,
document_type in out nocopy varchar2)
is
l_docid pls_integer;
l_filename varchar2(100);
l_errmsg varchar2(100) :=
'The document is not found in the database';
l_bdoc blob;
l_data_type varchar2(100);
begin
-- Determine the document to display from the
-- Document ID
l_docid := to_number(document_id);
-- Obtain the BLOB version of the document
select filename, content_type, bdata
into l_filename, document_type, l_bdoc
from wftst_lobs
where id = l_docid;
-- Set the encoding as a part of the document_type
document_type := document_type || '; encoding=base64;';
-- Now copy the content to the document
dbms_lob.Copy(document, l_bdoc, dbms_lob.getLength(l_bdoc));
exception
when others then
dbms_lob.WriteAppend(document, length(l_errmsg), l_errmsg);
wf_core.context('WFMLR_TEST','getBLOBDoc',document_id);
raise;
end getBlobDoc;
Related Topics
To Define a Document Attribute
This table lists the MIME types and subtypes that are defined within Oracle Workflow, together with the file extension that corresponds to each subtype.
| MIME Type and Subtype | File Type | File Extension |
|---|---|---|
| text/plain | Text document | .txt |
| text/html | HTML document | .htm |
| text/richtext | Rich text document | .rtf |
| text/tab-separated-values | Tab-separated values | .tsv |
| text/comma-separated-values | Comma-separated values | .csv |
| application/msword | Microsoft Word document (versions prior to Microsoft Word 2007) | .doc |
| application/vnd.openxmlformats-officedocument.wordprocessingml.document | Microsoft Word 2007 and higher document | .docx |
| application/vnd.ms-word.document.macroEnabled.12 | Microsoft Word 2007 and higher macro-enabled document | .docm |
| application/vnd.openxmlformats-officedocument.wordprocessingml.template | Microsoft Word 2007 and higher template | .dotx |
| application/vnd.ms-word.template.macroEnabled.12 | Microsoft Word 2007 and higher macro-enabled document template | .dotm |
| application/vnd.ms-excel | Microsoft Excel workbook (versions prior to Microsoft Excel 2007) | .xls |
| application/vnd.openxmlformats-officedocument.spreadsheetml.sheet | Microsoft Excel 2007 and higher workbook | .xlsx |
| application/vnd.ms-excel.sheet.macroEnabled.12 | Microsoft Excel 2007 and higher macro-enabled workbook | .xlsm |
| application/vnd.openxmlformats-officedocument.spreadsheetml.template | Microsoft Excel 2007 and higher template | .xltx |
| application/vnd.ms-excel.template.macroEnabled.12 | Microsoft Excel 2007 and higher macro-enabled workbook template | .xltm |
| application/vnd.ms-excel.sheet.binary.macroEnabled.12 | Microsoft Excel 2007 and higher binary workbook | .xlsb |
| application/vnd.ms-excel.addin.macroEnabled.12 | Microsoft Excel 2007 and higher add-in | .xlam |
| application/vnd.ms-powerpoint | Microsoft PowerPoint presentation (versions prior to Microsoft PowerPoint 2007) | .ppt |
| application/vnd.openxmlformats-officedocument.presentationml.presentation | Microsoft PowerPoint 2007 and higher presentation | .pptx |
| application/vnd.ms-powerpoint.presentation.macroEnabled.12 | Microsoft PowerPoint 2007 and higher macro-enabled presentation | .pptm |
| application/vnd.openxmlformats-officedocument.presentationml.slideshow | Microsoft PowerPoint 2007 and higher slide show | .ppsx |
| application/vnd.ms-powerpoint.slideshow.macroEnabled.12 | Microsoft PowerPoint 2007 and higher macro-enabled slide show | .ppsm |
| application/vnd.openxmlformats-officedocument.presentationml.template | Microsoft PowerPoint 2007 and higher template | .potx |
| application/vnd.ms-powerpoint.template.macroEnabled.12 | Microsoft PowerPoint 2007 and higher macro-enabled presentation template | .potm |
| application/vnd.ms-powerpoint.addin.macroEnabled.12 | Microsoft PowerPoint 2007 and higher add-in | .ppam |
| application/vnd.openxmlformats-officedocument.presentationml.slide | Microsoft PowerPoint 2007 and higher slide | .sldx |
| application/vnd.ms-powerpoint.slide.macroEnabled.12 | Microsoft PowerPoint 2007 and higher macro-enabled slide | .sldm |
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.
Oracle Workflow provides a standard generate function that you can use for demonstration or testing purposes. See: Default_Generate, Oracle Workflow API Reference.
Related Topics
Parameter List Structure, Oracle Workflow API Reference
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:
| Variable | Description |
|---|---|
| p_event_name | The internal name of the event. |
| p_event_key | A string generated when the event occurs within a program or application. The event key uniquely identifies a specific instance of the event. |
| p_parameter_list | An optional list of additional parameter name and value pairs for the event. |
Note: If you use custom PL/SQL generate functions, you can optionally enable Oracle Workflow to call your custom functions statically to enhance performance. See: Enabling Static Function Calls for Custom PL/SQL Functions, Oracle Workflow Administrator's Guide.
You can optionally 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:
| Variable | Description |
|---|---|
| event | The BusinessEvent object. The payload passed to the BusinessEvent class must be serializable. |
| context | Workflow context information, including the Log object which can be used for logging. |
When you define an agent in the Business Event System, you must assign the agent a queue handler. A PL/SQL 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. 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
Mapping Between WF_EVENT_T and SYS.AQ$_JMS_TEXT_MESSAGE, Oracle Workflow API Reference
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:
| Variable | Description |
|---|---|
| p_event | The event message. |
| p_out_agent_override | The outbound agent on whose queue the event message should be enqueued. |
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.
You can optionally call the dequeue navigation APIs WF_EVENT.setNavigationParams, WF_EVENT.resetNavigationParams, and WF_EVENT.getQueueNavigation within a custom Dequeue procedure to control the order in which messages are dequeued. See: Event APIs, Oracle Workflow API Reference.
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:
| Variable | Description |
|---|---|
| p_agent_guid | The globally unique identifier of the inbound agent from whose queue the event message should be dequeued. |
| p_event | The event message. |
Note: If you use custom PL/SQL queue handlers, you can optionally enable Oracle Workflow to call your custom functions statically to enhance performance. See: Enabling Static Function Calls for Custom PL/SQL Functions, Oracle Workflow Administrator's Guide.
You can optionally 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:
| Variable | Description |
|---|---|
| conn | The JDBC connection used to establish the connection with the queue. |
| agent | The AgentEO object that contains the information for this agent and its queue. |
| log | The Log object which can be used for logging. |
| uniqueLogId | The log ID for recording debug messages. |
| props | The property mapping for enqueue and dequeue operations. |
The argument for the enqueue method is as follows:
| Variable | Description |
|---|---|
| event | The BusinessEvent object to be enqueued. |
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.
Default_Rule_Or - Performs the basic subscription processing only if the parameter list within the event message includes at least one parameter whose name and value match a parameter defined for the subscription. See: Default_Rule_Or, Oracle Workflow API Reference.
oracle.apps.fnd.wf.bes.WebServiceInvokerSubscription - Invokes a business process execution language (BPEL) process or other Web service using the Web Services Invocation Framework (WSIF) from Oracle Application Server. See: WebServiceInvokerSubscription, 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
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
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:
| Variable | Description |
|---|---|
| p_subscription_guid | The globally unique identifier for the subscription. |
| p_event | The event message. |
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 continues processing subscriptions to the event without rolling back any processing executed so far.
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 places the event message on the WF_ERROR queue. Depending on the error handling specified for the subscription, the Event Manager can either halt subscription processing for this event and roll back any subscriptions already executed for the event, or roll back only the errored subscription and continue processing the next subscription for the event.
(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 unhandled exception in a rule function, the Event Manager's treatment of the unhandled exception depends on the type of error handling specified for the subscription.
For Stop and Rollback error handling, the Event Manager rolls back all subscription processing for the event and raises the exception to the calling application. In this case the event message is not placed on the WF_ERROR queue.
For Skip to Next error handling, the Event Manager rolls back only the current subscription and does not raise the exception to the calling application. However, it does place the event message on the WF_ERROR queue.
Note: If you use custom PL/SQL rule functions, you can optionally enable Oracle Workflow to call your custom functions statically to enhance performance. See: Enabling Static Function Calls for Custom PL/SQL Functions, Oracle Workflow Administrator's Guide.
You can optionally 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:
| Variable | Description |
|---|---|
| eo | The Subscription object, which provides information about the subscription such as the phase number and any parameters. |
| event | The BusinessEvent object, which provides information about the business event that occurred, including the event name, event key, event data, and the optional payload object. |
| ctx | Workflow context information, including the database connection and the Log object which can be used for logging. |
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. Depending on the error handling specified for the subscription, the Event Manager can either halt subscription processing for this event and roll back any subscriptions already executed for the event, or roll back only the errored subscription and continue processing the next subscription for the event.
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 E-Business Suite 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.