, , , ,

Oracle Workflow APIs in Detail

Oracle Workflow APIs:

1 WF_ENGINE
2 WF_CORE
3 WF_PURGE
4 WF_DIRECTORY
5 WF_PREF
6 WF_MONITOR
7 Oracle Workflow Views
8 WF_QUEUE
9 FND_DOCUMENT_MANAGEMENT
10 WF_NOTIFICATIONS

WF_ENGINE APIs:

The Workflow Engine APIs can be called by an application program or a workflow function in the runtime phase to communicate with the engine and to change the status of each of the activities. These APIs are
defined in a PL/SQL package called WF_ENGINE.

CreateProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default );
Creates a new runtime process for an application item. For example, a Requisition item type may have a Requisition Approval Process as a top level process. When a particular requisition is created, an application calls CreateProcess to set up the information needed to start the defined process.

SetItemUserKey (itemtype in varchar2,itemkey in varchar2, userkey in varchar2);
Lets you set a user–friendly identifier for an item in a process, which is initially identified by an item type and item key. The user key is intended to be a user–friendly identifier to locate items in the Workflow
Monitor and other user interface components of Oracle Workflow.

GetItemUserKey (itemtype in varchar2,itemkey in varchar2) return varchar2;
Returns the user–friendly key assigned to an item in a process, identified by an item type and item key. The user key is a user–friendly identifier to locate items in the Workflow Monitor and other user interface components of Oracle Workflow.

GetActivityLabel (actid in number) return varchar2;
Returns the instance label of an activity, given the internal activity instance ID. The label returned has the following format, which is suitable for passing to other Workflow Engine APIs, such as CompleteActivity and HandleError, that accept activity labels as arguments: :

SetItemOwner (itemtype in varchar2,itemkey in varchar2,owner in varchar2);
A procedure to set the owner of existing items. The owner must be a valid role. Typically, the role that initiates a transaction is assigned as the process owner, so that any participant in that role can find and view
the status of that process instance in the Workflow Monitor.

StartProcess (itemtype in varchar2,itemkey in varchar2);
Begins execution of the specified process. The engine locates the activity marked as START and then executes it. CreateProcess( ) must first be called to define the itemtype and itemkey before calling
StartProcess( ).

LaunchProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”,userkey in varchar2 default ”,owner in varchar2 default ”);
Launches a specified process by creating the new runtime process and beginning its execution. This is a wrapper that combines CreateProcess and StartProcess.

SuspendProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”);
Suspends process execution so that no new transitions occur. Outstanding notifications can complete by calling CompleteActivity( ), but the workflow does not transition to the next activity. Restart suspended processes by calling ResumeProcess( ). ResumeProcess(itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”); Returns a suspended process to normal execution status. Any activities that were transitioned to while the process was suspended are now executed.

AbortProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”,result in varchar2 default eng_force);
Aborts process execution and cancels outstanding notifications. The process status is considered COMPLETE, with a result specified by the result argument. Also, any outstanding notifications or subprocesses are set to a status of COMPLETE with a result of force, regardless of the result argument.

CreateForkProcess (copy_itemtype in varchar2,copy_itemkey in varchar2,new_itemkey in varchar2,same_version in boolean default TRUE);
Forks a runtime process by creating a new process that is a copy of the original. After calling CreateForkProcess(), you can call APIs such as SetItemOwner(), SetItemUserKey(), or the SetItemAttribute APIs to reset any item properties or modify any item attributes that you want for the new process. Then you must call StartForkProcess() to start the new process. Use CreateForkProcess() when you need to change item specific attributes during the course of a process. For example, if an order cannot be met due to insufficient inventory stock, you can use CreateForkProcess() to fork a new transaction for the backorder quantity. Note that any approval notification will be copied. The result is as if two items were created for this transaction.

StartForkProcess(itemtype in varchar2,itemkey in varchar2);
Begins execution of the new forked process that you specify. Before you call StartForkProcess( ), you must first call CreateForkProcess( ) to create the new process. You can modify the item attributes of the new
process before calling StartForkProcess().
If the new process uses the same version as the original, StartForkProcess() copies the status and history of each activity in the forked process, activity by activity. If the new process uses the latest version, then StartForkProcess() executes StartProcess().
If you call StartForkProcess() from within a process, any function activity in the process that had a status of ‘Active’ is updated to have a status of ‘Notified.’ You must call CompleteActivity() afterwards to continue the process.
StartForkProcess() automatically refreshes any notification attributes that are based on item attributes. Any open notifications in the original process are copied and sent again in the new process. Closed notifications are copied but not resent; their status remains remains ‘Complete.’
Any Wait activities in the new process are activated at the same time as the original activities. For example, if a 24 hour Wait activity in the original process is due to be eligible in two hours, the new Wait activity
is also eligible in two hours.

Background (itemtype in varchar2,minthreshold in number default null,
maxthreshold in number default null,process_deferred in boolean default TRUE,process_timeout in boolean default TRUE);
Runs a background engine for processing deferred and/or timed out activities using the parameters specified. The background engine executes all activities that satisfy the given arguments at the time that
the background engine is invoked. This procedure does not remain running long term, so you must restart this procedure periodically. Any activities that are newly deferred or timed out after the current background engine starts are processed by the next background engine that is invoked. You may run a script called wfbkgchk.sql to get a list of the activities waiting to be processed by the next background engine run. If you are using the standalone version of Oracle Workflow, you can use one of the sample background engine looping scripts described below or create your own script to make the background engine procedure loop indefinitely. If you are using the version of Oracle Workflow embedded in Oracle Applications, you can use the concurrent program version of this procedure and take advantage of the concurrent manager to schedule the background engine to run periodically.

AddItemAttr (itemtype in varchar2,itemkey in varchar2,aname in varchar2);
Adds an empty item type attribute variable to the process. Although most item type attributes are defined at design time, developers can create new attributes at runtime for a specific process.

SetItemAttrText(itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in varchar2);
SetItemAttrNumber(itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in number);
SetItemAttrDate (itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in date);
Sets the value of an item type attribute in a process. Use the correct procedure for your attribute type. All attribute types except number and date use SetItemAttrText.

SetItemAttrDocument(itemtype in varchar2,itemkey in varchar2,aname in varchar2,documentid in varchar2);
Sets the value of an item attribute of type document, to a document identifier.

GetItemAttrText(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return varchar2;
GetItemAttrNumber(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return number;
GetItemAttrDate(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return date;
Returns the value of an item type attribute in a process. Use the correct function for your attribute type. All attribute types except number and date use GetItemAttrText.

GetItemAttrDocument(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return varchar2;
Returns the document identifier for a document–type item attribute.The document identifier is a concate- nated string of the following values:
DM::: is the node ID assigned to the document management system node as defined in the Document Management Nodes web page. is the document ID of the document, as assigned by the document management system where the document resides. is the version of the document. If a version is not specified, the latest version is assumed.

GetItemAttrInfo(itemtype in varchar2,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);
Returns information about an item type attribute, such as its type and format, if any is specified. Currently, subtype information is not available for item type attributes

GetActivityAttrInfo(itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);
Returns information about an activity attribute, such as its type and format, if any is specified. This procedure currently does not return any subtype information for activity attributes.

GetActivityAttrText(itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return varchar2;
GetActivityAttrNumber (itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return number;
GetActivityAttrDate (itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return date;
Returns the value of an activity attribute in a process. Use the correct function for your attribute type. If the attribute is a Number or Date type, then the appropriate function translates the number/date value to a text string representation using the attribute format.
Note: Use GetActivityAttrText for Form, URLs, lookups and document attribute types.

BeginActivity (itemtype in varchar2,itemkey in varchar2,activity in varchar2);
Determines if the specified activity can currently be performed on the process item and raises an exception if it cannot. The CompleteActivity() procedure automatically performs this function as part of its validation. However, you can use BeginActivity to verify that the activity you intend to perform is currently allowed before actually calling it.

CompleteActivity(itemtype in varchar2,itemkey in varchar2,activity in varchar2,result_code in varchar2);
Notifies the workflow engine that the specified activity has been completed for a particular item. This procedure can be called for the following situations:

To indicate a completed activity with an optional result—This signals the Workflow Engine that an asynchronous activity has been completed. This procedure requires that the activity currently has a status of ‘Notified’. An optional activity completion result can also be passed. The result can determine what transition the process takes next.

To start and create a new item—Call CompleteActivity for a START activity to create a new item. START activities are designated as the beginning of a process in the Workflow Builder. The item type and key specified in this call must be passed to all subsequent calls that operate on this item.
Note: You can call CompleteActivity instead of StartActivity when you want to start a process with an activity node that is mid–stream in a process thread and not at the beginning of a process thread. The activity node you specify as the beginning of the process must be set to Start in the Node tab of its
property page or else an error will be raised.

AssignActivity(itemtype in varchar2,itemkey in varchar2,activity in varchar2,performer in varchar2);
Assigns or reassigns an activity to another performer. This procedure may be called before the activity is transitioned to. For example, a function activity earlier in the process may determine the performer of
a later activity. If a new user is assigned to a notification activity that already has an
outstanding notification, the outstanding notification is canceled and a new notification is generated for the new user by calling WF_Notification.Transfer.

HandleError (itemtype in varchar2,itemkey in varchar2,activity in varchar2,command in varchar2,result in varchar2);
This procedure is generally called from an activity in an ERROR process to handle any process activity that has encountered an error.

SetItemParent(itemtype in varchar2,itemkey in varchar2,parent_itemtype in varchar2,parent_itemkey in varchar2, parent_context in varchar2);
Defines the parent/child relationship for a master process and a detail process. This API must be called by any detail process spawned from a master process to define the parent/child relationship between the two
processes. You make a call to this API after you call the CreateProcess API, but before you call the StartProcess API for the detail process.

ItemStatus(itemtype in varchar2,itemkey in varchar2,status out varchar2,result out varchar2);
Returns the status and result for the root process of the specified item instance. Possible values returned for the status are: ACTIVE, COMPLETE, ERROR, or SUSPENDED. If the root process does not exist, then the item key does not exist and will thus cause the procedure to raise an exception.

Workflow core APIs:

PL/SQL procedures called by function activities can use a set of core Oracle Workflow APIs to raise and catch errors.

When a PL/SQL procedure called by a function activity either raises an unhandled exception, or returns a result beginning with ‘ERROR:’, the Workflow Engine sets the function activity’s status to ERROR and sets
the columns ERROR_NAME, ERROR_MESSAGE, and ERROR_STACK in the table WF_ITEM_ACTIVITY_STATUSES to reflect the error.

The columns ERROR_NAME and ERROR_MESSAGE get set to either the values returned by a call to WF_CORE.RAISE( ), or to the SQL error name and message if no call to RAISE( ) is found. The column
ERROR_STACK gets set to the contents set by a call to WF_CORE.CONTEXT( ), regardless of the error source.

CLEAR Clears the error buffers.

GET_ERROR(err_name out varchar2,err_message out varchar2 err_stack out varchar2);
Returns the name of a current error message and the token substituted error message. Also clears the error stack. Returns null if there is no current error.

declare
errname varchar2(30);
errmsg varchar2(2000);
errstack varchar2(32000);
begin
…
Wf_Engine.CompleteActivity(itemtype, itemkey, activity,
result_code);
…
exception
when others then
wf_core.get_error(err_name, err_msg, err_stack);
if (err_name is not null) then
wf_core.clear;
— Wf error occurred. Signal error as appropriate.
else
— not a wf error. Handle otherwise.
end if;
end;

TOKEN (token_name in varchar2,token_value in varchar2);
Defines an error token and substitutes it with a value. Calls to TOKEN( ) and RAISE( ) raise predefined errors for Oracle Workflow that are stored in the WF_RESOURCES table. The error messages contain tokens that need to be replaced with relevant values when the error message is raised. This is an alternative to raising PL/SQL standard exceptions or custom–defined exceptions.

RAISE (name in varchar2);
Raises an exception to the caller by supplying a correct error number and token substituted message for the name of the error message provided.

Calls to TOKEN( ) and RAISE( ) raise predefined errors for Oracle Workflow that are stored in the WF_RESOURCES table. The error messages contain tokens that need to be replaced with relevant values
when the error message is raised. This is an alternative to raising PL/SQL standard exceptions or custom defined exceptions.

CONTEXT (pkg_name IN VARCHAR2,proc_name IN VARCHAR2,arg1 IN VARCHAR2 DEFAULT ‘*none*’,arg2 IN VARCHAR2 DEFAULT ‘*none*’,arg3 IN VARCHAR2 DEFAULT ‘*none*’,arg4 IN VARCHAR2 DEFAULT ‘*none*’,arg5 IN VARCHAR2 DEFAULT ‘*none*’);

Adds an entry to the error stack to provide context information that helps locate the source of an error. Use this procedure with predefined errors raised by calls to TOKEN( ) and RAISE( ), with custom–defined
exceptions, or even without exceptions whenever an error condition is detected.

TRANSLATE (tkn_name IN VARCHAR2) return VARCHAR2;

Translates the string value of a token by returning the value for the token as defined in WF_RESOURCES for your language setting.

Workflow Directory Service APIs

GetRoleUsers(role in varchar2,users out UserTable);
Returns a table of users for a given role.

GetUserRoles(user in varchar2,roles out RoleTable);
Returns a table of roles that a given user is assigned to.

GetRoleInfo(Role in varchar2,Display_Name out varchar2,Email_Address out varchar2,Notification_Preference out varchar2,Language out varchar2,Territory out varchar2);

Returns the following information about a role:
Display name
Email address
Notification Preference (‘QUERY’, ‘MAILTEXT’, ‘MAILHTML’,’MAILATTH’, ‘SUMMARY’)
Language
Territory

GetRoleInfo2(Role in varchar2,Role_Info_Tbl out wf_directory.wf_local_roles_tbl_type);

Returns the following information about a role in a SQL table:
Display name
Description
Notification Preference (‘QUERY’, ‘MAILTEXT’, ‘MAILHTML’,’SUMMARY’)
Language
Territory
Email address
FAX
Status
Expiration Date

IsPerformer (user in varchar2,role in varchar2);
Returns true or false to identify whether a user is a performer of a role.

CurrentUser return varchar2;

Returns the current Application Object Library username. This function is useful only for the version of Oracle Workflow embedded in Oracle Applications.

UserActive (username in varchar2) return boolean;
Determines if a user is currently active and available to participate in a workflow. Returns TRUE if the user is active, otherwise it returns FALSE.

GetUserName (p_orig_system in varchar2,p_orig_system_id in varchar2, p_name out varchar2, p_display_name out varchar2);

Returns a Workflow display name and username for a user given the system information from the original user and roles repository.

GetRoleName (p_orig_system in varchar2,p_orig_system_id in varchar2,p_name out varchar2,p_display_name out varchar2);

Returns a Workflow display name and role name for a role given the system information from the original user and roles repository.

GetRoleDisplayName (p_role_name in varchar2) return varchar2;

pragma restrict_references(GetRoleDisplayName, WNDS, WNPS); Returns a Workflow role’s display name given the role’s internal name.

SetAdHocUserStatus (user_name in varchar2,status in varchar2 default ‘ACTIVE’);

Sets the status of an ad hoc user as ‘ACTIVE’ or ‘INACTIVE’.

SetAdHocRoleStatus (role_name in varchar2,status in varchar2 default ‘ACTIVE’);

Sets the status of an ad hoc role as ‘ACTIVE’ or ‘INACTIVE’.

CreateAdHocUser (name in out varchar2,display_name in out varchar2,language in varchar2 default null,territory in varchar2 default null,description in varchar2 default null,notification_preference in varchar2 default ‘MAILHTML’,email_address in varchar2 default null,fax in varchar2 default null,status in varchar2 default ‘ACTIVE’,expiration_date in date default sysdate);

Creates a user at runtime by creating a value in the WF_LOCAL_USERS table. This is referred to as an ad hoc user.

CreateAdHocRole (role_name in out varchar2,role_display_name in out varchar2,language in varchar2 default null,territory in varchar2 default null,role_description in varchar2 default null,notification_preference in varchar2 default’MAILHTML’,role_users in varchar2 default null,email_address in varchar2 default null,fax in varchar2 default null,status in varchar2 default ‘ACTIVE’,expiration_date in date default sysdate);

Creates a role at runtime by creating a value in the WF_LOCAL_ROLES table. This is referred to as an ad hoc role.

AddUsersToAdHocRole (role_name in varchar2,role_users in varchar2);

Adds users to a existing ad hoc role.

SetAdHocUserExpiration (user_name in varchar2,expiration_date in date default sysdate);

Updates the expiration date for an ad hoc user.

SetAdHocRoleExpiration (role_name in varchar2,expiration_date in date default sysdate);

Updates the expiration date for an ad hoc role.

SetAdHocUserAttr (user_name in varchar2,display_name in varchar2 default null,notification_preference in varchar2 default null, language in varchar2 default null, territory in varchar2 default null, email_address in varchar2 default null,fax in varchar2 default null);

Updates the attributes for an ad hoc user.

SetAdHocRoleAttr (role_name in varchar2,display_name in varchar2 default null,notification_preference in varchar2 default null,language in varchar2 default null,territory in varchar2 default null,email_address in varchar2 default null,fax in varchar2 default null);

Updates the attributes for an ad hoc role.

RemoveUsersFromAdHocRole (role_name in varchar2,role_users in varchar2 default null);

Removes users from an existing ad hoc role.

Workflow Preferences API

get_pref (p_user_name in varchar2,p_preference_name in varchar2) return varchar2;

Retrieves the value of the specified preference for the specified user.

Workflow Monitor APIs

function GetDiagramURL(x_agent in varchar2,x_item_type in varchar2,x_item_key in varchar2,x_admin_mode in varchar2 default ‘NO’) return varchar2;

Can be called by an application to return a URL that allows access to the Workflow Monitor with an attached access key password. The URL displays the diagram for a specific instance of a workflow process
in the Workflow Monitor operating in either ‘ADMIN’ or ‘USER’ mode.

function GetEnvelopeURL(x_agent in varchar2,x_item_type in varchar2, x_item_key in varchar2,x_admin_mode in varchar2 default ‘NO’) return varchar2;

Can be called by an application to return a URL that allows access to the Workflow Monitor Notifications List with an attached access key password. The URL displays the Notifications List for a specific instance of a workflow process in the Workflow Monitor.

function GetAdvancedEnvelopeURL (x_agent in varchar2,x_item_type in varchar2,x_item_key in varchar2, x_admin_mode in varchar2 default ‘NO’, x_options in varchar2 default null) return varchar2;

Can be called by an application to return a URL that displays the Workflow Monitor Activities List with an attached access key password. The URL displays the Activities List for a specific instance of a workflow process in the Workflow Monitor. The Activities List allows you to apply advanced filtering options in displaying the list of activities for a process instance.

Oracle Workflow Views

WF_ITEM_ACTIVITY_STATUSES_V
This view contains denormalized information about a workflow process and its activities’ statuses. Use this view to create custom queries and reports on the status of a particular item or process. The column descriptions of the view are as follows:

WF_NOTIFICATION_ATTR_RESP_V
This view contains information about the Respond message attributes for a notification group. If you plan to create a custom “voting” activity, use this view to create the function that tallies the responses from the users in the notification group. See: Voting Activity: page

WF_RUNNABLE_PROCESSES_V
This view contains a list of all runnable workflow processes in the ACTIVITIES table.

WF_ITEMS_V
This view is a select only version of the WF_ITEMS table.

Document Management APIs

get_launch_document_url(username in varchar2,document_identifier in varchar2,display_icon in oolean, launch_document_url out varchar2);

Returns an anchor URL that launches a new browser window containing the DM integration screen that displays the specified document. The screen is a frame set of two frames. The upper frame contains a customizable company logo and a toolbar of Oracle Workflow–integrated document management functions. The lower frame displays the specified document.

get_launch_attach_url (username in varchar2,callback_function in varchar2,display_icon in Boolean,launch_attach_url out varchar2);

Returns an anchor URL that launches a new browser window containing a DM integration screen that allows you to attach a document. The screen is a frame set of two frames. The upper frame contains a customizable company logo and a toolbar of Oracle Workflow–integrated document management functions. The lower frame displays the search screen of the default document management system.

get_open_dm_display_window
Returns a javascript function that displays an attached document from the current UI. The javascript function is used by all the document management functions that the user can perform on an attached
document. Each DM function also gives the current DM integration screen a name so that the Document Transport Window can call back to the javascript function in the current window.

get_open_dm_attach_window
Returns a javascript function to open a Document Transport Window when a user tries to attach a document in the current UI. The javascript function is used by all the document management functions
that the user can perform to attach a document. Each DM function also gives the current DM integration screen a name so that the Document Transport Window can call back to the javascript function in the
current window.

set_document_id_html (frame_name in varchar2,form_name in varchar2, document_id_field_name in varchar2 document_name_field_name in varchar2, callback_url out varchar2);

Returns a callback URL that gets executed when a user selects a document from the DM system. Use this procedure to set the document that is selected from the document management Search function to the specified destination field of an HTML page. The destination field is the field from which the user launches the DM integration screen to attach a document. Pass the returned callback URL as an argument to the get_launch_attach_url API.

Notification Model

A notification activity in a workflow process consist of a design–time message and a list of message attributes. In addition, there may be a number of runtime named values called item type attributes from
which the message attributes draw their values. The Workflow Engine moves through the workflow process, evaluating each activity in turn. Once it encounters a notification activity, the engine makes a call to the Notification System Send( ) or SendGroup( ) API to send the notification.

Notification APIs

The following APIs can be called by a notification agent to manage notifications for a notification activity. The APIs are stored in the PL./SQL package called WF_NOTIFICATION.

function SEND (role in varchar2,msg_type in varchar2,msg_name in varchar2,
due_date in date default null,callback in varchar2 default null,context in varchar2 default null,send_comment in varchar2 default null priority in number default null) return number;

This function sends the specified message to a role, returning a notification ID if successful. The notification ID must be used in all future references to the notification.

function SendGroup (role in varchar2,msg_type in varchar2,msg_name in varchar2,due_date in date default null,callback in varchar2 default null,context in varchar2 default null,send_comment in varchar2 default null priority in number default null) return number;

This function sends a separate notification to all the users assigned to a specific role and returns a number called a notification group ID, if successful. The notification group ID identifies that group of users and the notification they each received.

procedure FORWARD (nid in number,new_role in varchar2,forward_comment in varchar2 default null);

This procedure delegates a notification to a new role to perform work, even though the original role recipient still maintains ownership of the notification activity. Also implicitly calls the Callback function specified in the Send or SendGroup function with FORWARD mode.

procedure TRANSFER(nid in number,new_role in varchar2,forward_comment in varchar2 default null);

This procedure forwards a notification to a new role and transfers ownership of the notification to the new role. It also implicitly calls the Callback function specified in the Send or SendGroup function with
TRANSFER mode.

procedure CANCEL(nid in number,cancel_comment in varchar2 default null);

This procedure may be invoked by the sender or administrator to cancel a notification. The notification status is then changed to ‘CANCELED’ but the row is not removed from the WF_NOTIFICATIONS table until a purge operation is performed. If the notification was delivered via e–mail and expects a response, a
‘Canceled’ e–mail is sent to the original recipient as a warning that the notification is no longer valid.

procedure CancelGroup (gid in number,cancel_comment in varchar2 default null);

This procedure may be invoked by the sender or administrator to cancel the individual copies of a specific notification sent to all users in a notification group. The notifications are identified by the notification
group ID (gid). The notification status is then changed to ‘CANCELED’ but the rows are not removed from the WF_NOTIFICATIONS table until a purge operation is performed. If the notification was delivered via e–mail and expects a response, a ‘Canceled’ e–mail is sent to the original recipient as a warning that the
notification is no longer valid. Generally, this function is called only if a notification activity has
‘Expanded Roles’ checked in its properties page. If Expanded Roles is not checked, then the Cancel( ) function is called instead.

procedure RESPOND (nid in number,respond_comment in varchar2 default null,responder in varchar2 default null);

This procedure may be invoked by the notification agent (Notification Viewer, Notification Web page, or E–mail agent) when the performer completes the response to the notification. The procedure marks the
notification as ‘CLOSED’ and communicates RESPOND attributes back to the database via the callback function (if supplied). This procedure also accepts the name of the individual that actually responded to the notification. This may be useful to know especially if the notification is assigned to a multi–user role. The information is stored in the RESPONDER column of the WF_NOTIFICATIONS table. The value stored in this column depends on how the user responds to the notification.

function RESPONDER (nid in number) returns varchar2;
This function returns the responder of a closed notification.

procedure VoteCount(gid in number,ResultCode in varchar2,ResultCount out number,PercentOfTotalPop out number,PercentOfVotes out number);

Counts the number of responses for a specified result code. Use this procedure only if you are writing your own custom Voting activity

function OpenNotificationsExist(gid in number) return boolean;

This function returns ‘TRUE’ if any notification associated with the specified notification group ID is ‘OPEN’, otherwise it returns ‘FALSE’. Use this procedure only if you are writing your own custom Voting
activity.

procedure Close (nid in number,responder in varchar2 default null);
This procedure Closes a notification.

procedure AddAttr (nid in number,aname in varchar2);
Adds a new runtime notification attribute.

procedure SetAttrText (nid in number,aname in varchar2,avalue in varchar2);
procedure SetAttrNumber(nid in number,aname in varchar2,avalue in number);
procedure SetAttrDate (nid in number,aname in varchar2,avalue in date);

Used at both send and respond time to set the value of notification attributes. The notification agent (sender) may set the value of SEND attributes. The performer (responder) may set the value of RESPOND
attributes.

procedure GetAttrInfo (nid in number,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);

Returns information about a notification attribute, such as its type, subtype, and format, if any is specified. The subtype is always SEND or RESPOND to indicate the attribute’s source.

procedure GetInfo (nid in number,role out varchar2,message_type out varchar2,message_name out varchar2,priority out number,due_date out date,status out varchar2);

Returns the role that the notification is sent to, the item type of the message, the name of the message, the notification priority, the due date and the status for the specified notification.

function GetText(some_text in varchar2,nid in number,disptype in varchar2 default ”) return varchar2;

function GetAttrText(nid in number,aname in varchar2) return varchar2;
function GetAttrNumber(nid in number,aname in varchar2) return number;
function GetAttrDate(nid in number,aname in varchar2) return date;

Returns the value of the specified message attribute.

function GetSubject(nid in number) return varchar2
Returns the subject line for the notification message. Any message attribute in the subject is token substituted with the value of the corresponding message attribute.

function GetBody(nid in number,disptype in varchar2 default ”)
return varchar2;

Returns the HTML or plain text message body for the notification, depending on the message body type specified. Any message attribute in the body is token substituted with the value of the corresponding
notification attribute. This function may return up to 32K characters. You cannot use this function in a view definition or in an Oracle Applications form. For views and forms, use GetShortBody( ) which truncates values at 1950 characters.

function TestContext (nid in number) return boolean;

Tests if the current context is correct by calling the Item Type Selector/Callback function. This function returns TRUE if the context check is OK, or if no Selector/Callback function is implemented. It returns FALSE if the context check fails.

function AccessCheck (access_str in varchar2) return varchar2;

Returns a username if the notification access string is valid and the notification is open, otherwise it returns null. The access string is automatically generated by the Notification Mailer and is used to verify the authenticity of both text and HTML versions of E–mail notifications.

function WorkCount (username in varchar2) return number;
Returns the number of open notifications assigned to a role.

/by
, , , ,

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like ‘c_sgoud’ — User name of the terminal ( may be your windows login name )

Alter system kill session ‘146,46619’

select sid, serial# from v$session where username = ‘USER’;

alter system kill session ‘SID,SERIAL#’;

you need to find first lock
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;
by this query you can find lock

then you can kill
col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
— b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
and b.status=’ACTIVE’
–and b.module like ‘%blbn%’
–and b.username=’ENBAPP23′
order by program ,OS_USER

 How to Unlock the Objects

The following query could be useful :

Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))

Get the Process id from the query. Login as unix user and run the following command to kill the process.

/by
, , , ,

Pl-Sql: Null Values in Comparisons and Conditional Statements

Lot of time we got this type of senerio When working with nulls, you can avoid some common mistakes by keeping in mind the following rules:

Comparisons involving nulls always yield NULL

Applying the logical operator NOT to a null yields NULL

In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

If the expression in a simple CASE statement or CASE expression yields NULL, it cannot be matched by using WHEN NULL. In this case, you would need to use the searched case syntax and test WHEN expression IS NULL.

In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
IF x != y THEN — yields NULL, not TRUE
dbms_output.put_line(‘x != y’); — not executed
ELSIF x = y THEN — also yields NULL
dbms_output.put_line(‘x = y’);
ELSE
dbms_output.put_line(‘Can”t tell if x and y are equal or not…’);
END IF;
END;
/
In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

DECLARE
a NUMBER := NULL;
b NUMBER := NULL;
BEGIN
IF a = b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a = b’); — not executed
ELSIF a != b THEN — yields NULL, not TRUE
dbms_output.put_line(‘a != b’); — not executed
ELSE
dbms_output.put_line(‘Can”t tell if two NULLs are equal’);
END IF;
END;
/
NULLs and the NOT Operator
Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:

IF x > y THEN | IF NOT x > y THEN
high := x; | high := y;
ELSE | ELSE
high := y; | high := x;
END IF; | END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. If neither x nor y is null, both IF statements assign the same value to high. However, if either x or y is null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high.

NULLs and Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:

DECLARE
null_string VARCHAR2(80) := TO_CHAR(”);
address VARCHAR2(80);
zip_code VARCHAR2(80) := SUBSTR(address, 25, 0);
name VARCHAR2(80);
valid BOOLEAN := (name != ”);
BEGIN
NULL;
END;
/
Use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN …

NULLs and the Concatenation Operator

The concatenation operator ignores null operands. For example, the expression

‘apple’ || NULL || NULL || ‘sauce’

returns the following value:

‘applesauce’

NULLs as Arguments to Built-In Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

DECLARE
the_manager VARCHAR2(40);
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to DECODE. In this case, manager_id is null
— and the DECODE function returns ‘nobody’.
SELECT DECODE(manager_id, NULL, ‘nobody’, ‘somebody’), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by ‘ || the_manager);
END;
/
The function NVL returns the value of its second argument if its first argument is null. In the following example, if the column specified in the query is null, the function returns the value -1 to signify a non-existent employee in the output:

DECLARE
the_manager employees.manager_id%TYPE;
name employees.last_name%TYPE;
BEGIN
— NULL is a valid argument to NVL. In this case, manager_id is null
— and the NVL function returns -1.
SELECT NVL(manager_id, -1), last_name
INTO the_manager, name FROM employees WHERE employee_id = 100;
dbms_output.put_line(name || ‘ is managed by employee #’ || the_manager);
END;
/
The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For example, the following call to REPLACE does not make any change to the value of OLD_STRING:

DECLARE
string_type VARCHAR2(60);
old_string string_type%TYPE := ‘Apples and oranges’;
my_string string_type%TYPE := ‘more apples’;
— NULL is a valid argument to REPLACE, but does not match
— anything so no replacement is done.
new_string string_type%TYPE := REPLACE(old_string, NULL, my_string);
BEGIN
dbms_output.put_line(‘Old string = ‘ || old_string);
dbms_output.put_line(‘New string = ‘ || new_string);
END;
/
If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, the following call to REPLACE removes all the dashes from DASHED_STRING, instead of changing them to another character:

DECLARE
string_type VARCHAR2(60);
dashed string_type%TYPE := ‘Gold-i-locks’;
— When the substitution text for REPLACE is NULL,
— the text being replaced is deleted.
name string_type%TYPE := REPLACE(dashed, ‘-‘, NULL);
BEGIN
dbms_output.put_line(‘Dashed name = ‘ || dashed);
dbms_output.put_line(‘Dashes removed = ‘ || name);
END;
/
If its second and third arguments are null, REPLACE just returns its first argument.
Posted by Sairamgoud at 3:05 PM 0 comments
PL/SQL -Advantages
Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

Support for SQL
Support for object-oriented programming
Better performance
Higher productivity
Full portability
Tight integration with Oracle
Tight security
Tight Integration with SQL

The PL/SQL language is tightly integrated with SQL. You do not have to translate between SQL and PL/SQL datatypes: a NUMBER or VARCHAR2 column in the database is stored in a NUMBER or VARCHAR2 variable in PL/SQL. This integration saves you both learning time and processing time. Special PL/SQL language features let you work with table columns and rows without specifying the datatypes, saving on maintenance work when the table definitions change.

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages.

Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation.

Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as SELECT, INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. This extensive SQL support lets you manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes, reducing the need to convert data passed between your applications and the database.

PL/SQL also supports dynamic SQL, a programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements at run time, without knowing details such as table names and WHERE clauses in advance.

Better Performance
Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead.

With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application. PL/SQL even has language features to further speed up SQL statements that are issued inside a loop.

PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient. Because stored procedures execute in the database server, a single call over the network can start a large job. This division of work reduces network traffic and improves response times. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

Higher Productivity
PL/SQL extends tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits.

PL/SQL is the same in all environments. Once you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.

Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement.

Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.

Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.

In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. This direct mapping helps your programs better reflect the world they are trying to simulate.

/by
, , , , ,

What is Difference between Decode and Case?

Decode
1.It’s a function
2.can compare only discrete vaules
3.Pl/Sql standards
4.cannot process null

Case
1.It’s an Expression
2.can hande range values
3.ANSI Standards
4.Processing time is faster when compared to Decode
5.can process null

Decode
The default operator for comparison is ‘=’,
for e.g select deptno,decode
(deptno,10,’dept10′,20,dept20,default)

so if the deptno=10 then the value is replaced by dept10
and so on,

whereas in Case:

we can use any operator other than ‘=’,
for e.g
select deptno,
case when deptno=10 and sal>1000 then ‘dept10’
else
‘dept20’

Decode: using for reporting purpose. and it is implemented
by if stmt.
Ex: select decode(job,’CLERK’,’executive’,job) from emp;

Case : implemented by if & elsif condition. it is using
pl/sql equality conditionality.
Ex : select case job=’CLERK’ then ‘executive’ else ‘no’ end
a from emp;

it is also used for multipul colms & multi conditions.

in above stmt a is reffered alias name.

There is one more Important difference between CASE and DECODE

DECODE can be used Only inside SQL statement….
But CASE can be used any where even as a paramtre of a function/procedure

Eg:-

Code: [Select all] [Show/ hide]SQL> create or replace procedure pro_01(n number) is
2 begin
3 dbms_output.put_line(‘ The number = ‘||n);
4 End;
5 /

Procedure created.

SQL> set serverout on
SQL> var a varchar2(5);
SQL> Begin
2 :a := ‘ONE’;
3 End;
4 /

PL/SQL procedure successfully completed.

SQL> Begin
2 pro_01(Decode(:a,’ONE’,1,0));
3 End;
4 /
pro_01(Decode(:a,’ONE’,1,0));
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00204: function or pseudo-column ‘DECODE’ may be used inside a SQL
statement only
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

SQL> Begin
2 pro_01(case :a when ‘ONE’ then 1 else 0 end);
3 End;
4 /
The number = 1

PL/SQL procedure successfully completed.

/by