How database Adapter Deal with procedures that use Cursors

in this article i gonna take you in short tour to show the way that database adapter manipulate with procedures that use Cursors .

through this article i will explain small BPM example starting from Timer and call Procedure and based on outputs it will raise some human tasks .

first of all, please see the structure of BPM notation :

simply this notation start every cycle (one day in my case ) and call the procedure "GET_COOPS_NOTIFY_YST" to get the group of users that meet the condition spicified the procedure "the remaining days for their end of financial year is 30 days or less " below is the procedure that search for that condition :


create or replace PROCEDURE "GET_COOPS_NOTIFY_YST"
(
  Num_Days IN NUMBER
, prodCur OUT SYS_REFCURSOR
) AS
BEGIN
OPEN prodCur FOR SELECT * FROM USERS  where (to_date( sysdate,'DD-MM-YYYY') - to_date( Fiscal_year_date,'DD-MM-YYYY')) > Num_Days;
END;

the output of this procedure in the Service Activity appeared as 2-dimensional array : rows and columns

the table "users" assumed to be like this :

once record has been returned , simple count check will be fired to make sure that procedure has returned rows and if count >0 the next step will be start to take confirmation from manager to send the notifications to users and if he clicked "APPROVE" the notification will be sent.

to keep the loops moving on i have added 3 scripts :

1. increase Rows : responsible to increase the variable iRows by 1 and reset the value of icols to it initial value
2. increase Columns :responsible to increase the variable icols by 1
3. Set Username : responsible for set value of username to usercoop field which will be set later to FYI notification letter human task .

final check is responsible to exit from loops when irows value exceed count of rows to avoid the index out of range exception .

hope you find this article is useful for you !