Creating Custom Oracle Workflow using AME

Oracle Approvals Management (AME) had first published over ten years ago, and also big question is “Why isn’t it so popular”. I think this is all about supporting unnecessary extra extensibility which is not required for traditional approval processes.  AME includes many advantages for more flexibility/extensibility/functionality on approval processes. If we would like to check some of these advantages; you can create custom transaction types ,approval groups , approval types. These are definitions called rules, attributes, conditions, approval groups, action types.

Most featured functionality that AME provides us is action types like paralel or serial voting.  You can use approval types like consensus (all approvers have to approve to end approval process) , first responder wins (first approver finishes all process).  And you can define these approvers dynamically , It supports;

  • HR Organization Hierarchy
  • Roles
  • Users
  • Most important one: your custom approval processes with your custom rules

For further  information about AME you can check these document for AME implementation : Metalink Note 336901.1 and Note 282529.1

In this case, We created a simple custom WF and PL/SQL package for AME rules and notification process. Custom WF contains two processes a main and a subprocess;

ame_wf_1

ame_wf_2

In main Workflow process we call AME process with only a return type  approved or rejected. So! We are focussing only AME sub-process in this practice . Now check two notifcations and what function look like  ;

ame_wf_3

ame_wf_4

ame_wf_5

These three functions use following package ;

CREATE OR REPLACE package apps.xx_ame_approval_pkg is 
 
   procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number );
   procedure getNextApprover(itemtype        in varchar2,
                               itemkey         in varchar2,
                               actid           in number,
                               funcmode        in varchar2,
                               resultout       out NOCOPY varchar2) ;
    
   procedure updateAmeWithResponse(itemtype        in varchar2,
                            itemkey         in varchar2,
                            actid           in number,
                            funcmode        in varchar2,
                            resultout       out nocopy varchar2) ;
                            
  
END; 
/

 

CREATE OR REPLACE package body apps.xx_ame_approval_pkg is 

     procedure getAmeTransactionDetail (x_transaction_type in out varchar2 , x_application_id out number )
     is 
     
     cursor c1 is 
            select fnd_application_id as application_id, 
                   transaction_type_id as transaction_type
              from ame_transaction_types_v
             where transaction_type_id = x_transaction_type;
     
     begin
     
            for crec in c1 loop
            
                x_transaction_type:=crec.transaction_type;
                x_application_id := crec.application_id ; 
                
            end loop ; 
     
     end ; 
                   
     procedure getNextApprover(  itemtype        in varchar2,
                                 itemkey         in varchar2,
                                 actid           in number,
                                 funcmode        in varchar2,
                                 resultout       out NOCOPY varchar2) IS

      E_FAILURE                   EXCEPTION;
      l_transaction_id            number;
      l_next_approver             ame_util.approverRecord2;
      l_next_approvers            ame_util.approversTable2;
      l_next_approvers_count      number;
      l_approver_index            number;
      l_is_approval_complete      VARCHAR2(1);
      l_transaction_type          VARCHAR2(200);
      l_application_id            number;
      l_role_users  WF_DIRECTORY.UserTable;
      l_role_name                            VARCHAR2(320) ;
      l_role_display_name                    VARCHAR2(360)  ;

      l_all_approvers            ame_util.approversTable;

    cursor c1(p_user_name varchar2) is
           select papf.full_name from  fnd_user fu ,
                          per_all_people_f papf
                   where  fu.employee_id = papf.person_id
                     and  fu.user_name = p_user_name
                     and sysdate between papf.EFFECTIVE_START_DATE and nvl(papf.EFFECTIVE_end_DATE,sysdate+1)
                     and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1) ;

    begin

    if (funcmode = 'RUN') THEN

      -- l_transaction_id :=  TO_NUMBER(itemkey);
       
       l_transaction_id:= wf_engine.getItemAttrNumber( itemtype =>  itemtype,
                                                         itemkey  => itemkey,
                                                         aname    => 'AME_TRANSACTION_ID' );


       l_transaction_type :=  wf_engine.getItemAttrText( itemtype =>  itemtype,
                                                         itemkey  => itemkey,
                                                         aname    => 'AME_TRANSACTION_TYPE' );

       getAmeTransactionDetail  ( l_transaction_type,l_application_id );


       ame_api2.getNextApprovers4(applicationIdIn=>l_application_id,
                                transactionTypeIn=>l_transaction_type,
                                transactionIdIn=>l_transaction_id,
                                flagApproversAsNotifiedIn => ame_util.booleanTrue,
                                approvalProcessCompleteYNOut => l_is_approval_complete,
                                nextApproversOut=>l_next_approvers);

      l_next_approvers_count:=l_next_approvers.count ;

      if (l_is_approval_complete = ame_util.booleanTrue) then
        resultout:='COMPLETE:'||'APPROVAL_COMPLETE';
        return;

  --  Incase of consensus voting method, next approver count might be zero but there will be pending approvers
      elsif (l_next_approvers.Count = 0) then

        ame_api2.getPendingApprovers(applicationIdIn=>l_application_id,
                                    transactionTypeIn=>l_transaction_type,
                                    transactionIdIn=>l_transaction_id,
                                    approvalProcessCompleteYNOut => l_is_approval_complete,
                                    approversOut =>l_next_approvers);
      end if;

      l_next_approvers_count := l_next_approvers.Count;

      if (l_next_approvers_count = 0)  then
         resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
         return;
      end if;

      if (l_next_approvers_count > 0)  then
         resultout:='COMPLETE:'||'VALID_APPROVER';
         --return;
      end if;

      if (l_next_approvers_count = 1)  then

          l_next_approver:=l_next_approvers(l_next_approvers.first());
          wf_engine.SetItemAttrText( itemtype   => itemType,
                                  itemkey    => itemkey,
                                  aname      => 'APPROVER_USER_NAME' ,
                                  avalue     => l_next_approver.name);

           wf_engine.SetItemAttrText( itemtype   => itemType,
                                  itemkey    => itemkey,
                                  aname      => 'APPROVER_DISPLAY_NAME' ,
                                  avalue     => l_next_approver.display_name);

           /*role name is user name here */
           for crec in c1(l_next_approver.name) loop

               l_role_display_name:=crec.full_name ;

           end loop  ;
                        --  l_role_name:=

           resultout:='COMPLETE:'||'VALID_APPROVER';
           --return;

      end if;

      l_approver_index := l_next_approvers.first();

      while ( l_approver_index is not null ) loop

          l_role_users(l_approver_index):= l_next_approvers(l_approver_index).name ;

          l_approver_index := l_next_approvers.next(l_approver_index);


      end loop;


	 wf_directory.CreateAdHocRole2( role_name => l_role_name
								  ,role_display_name => l_role_display_name
								  ,language => null
								  ,territory => null
								  ,role_description => 'AME ROLE DESC'
								  ,notification_preference => null
								  ,role_users => l_role_users
								  ,email_address => null
								  ,fax => null
								  ,status => 'ACTIVE'
								  ,expiration_date => null
								  ,parent_orig_system => null
								  ,parent_orig_system_id => null
								  ,owner_tag => null
								  );

      wf_engine.setitemattrtext(itemtype => itemtype,
                                itemkey => itemkey,
                                aname => 'RECIPIENT_ROLE',
                                avalue => l_role_name
                                );
     return;

	end if; -- run

    exception
      when others then
        -- The line below records this function call in the error
        -- system in the case of an exception.
        wf_core.context('xx_ame_approval_pkg',
                        'getNextApprover',
                        itemtype,
                        itemkey,
                        to_char(actid),
                        funcmode);
        raise;
    end getNextApprover;

    
    procedure updateAmeWithResponse(itemtype        in varchar2,
                                    itemkey         in varchar2,
                                    actid           in number,
                                    funcmode        in varchar2,
                                    resultout       out nocopy varchar2) is
      e_failure                   exception;
      l_transaction_id            number;
      l_nid                       number;
      l_gid                       number;
      l_approver_name             varchar2(240);
      l_result                    varchar2(100);
      l_ame_status                varchar2(20);
      l_original_approver_name         varchar2(240);
      l_forwardeein  ame_util.approverrecord2;
      
      l_transaction_type varchar2 (200 byte);
      l_application_id   number ;

    begin
    
     l_transaction_type :=  wf_engine.getItemAttrText( itemtype =>  itemtype,
													   itemkey  => itemkey,
													   aname    => 'AME_TRANSACTION_TYPE');
    
     getAmeTransactionDetail (l_transaction_type,
                              l_application_id);
      
     l_transaction_id:= wf_engine.getItemAttrNumber( itemtype =>  itemtype,
                                                     itemkey  => itemkey,
                                                     aname    => 'AME_TRANSACTION_ID');
    
     if (funcmode = 'RUN') then

                      -- l_transaction_id :=  itemkey;
                       l_gid := wf_engine.context_nid;

                       select responder,notification_id
                              into  l_approver_name,l_nid
                         from wf_notifications
                        ---where group_id=l_gid
                        where group_id=l_gid
                          and status = 'CLOSED';

                       l_result := wf_notification.getattrtext(l_nid, 'RESULT');


                       if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
                         l_ame_status := ame_util.approvedstatus;
                       elsif (l_result = 'REJECTED') then
                         l_ame_status := ame_util.rejectstatus;
                       else -- reject for lack of information, conservative approach
                         l_ame_status := ame_util.rejectstatus;
                       end if;
                       --set approver as approved or rejected based on approver response
                       ame_api2.updateapprovalstatus2(  applicationidin=>l_application_id,
                                                        transactiontypein=>l_transaction_type,
                                                        transactionidin=>l_transaction_id,
                                                        approvalstatusin => l_ame_status,
                                                        approvernamein => l_approver_name);


     
     elsif  ( funcmode = 'TIMEOUT' ) then
     
           l_gid := wf_engine.context_nid;

           select responder,notification_id
             into l_approver_name,l_nid
             from wf_notifications
            where group_id=l_gid;
            --and status = 'CLOSED';

           l_result := wf_notification.getattrtext(l_nid, 'RESULT');

           if (l_result = 'APPROVED') then -- this may vary based on lookup type used for approval
              l_ame_status := ame_util.approvedstatus;
           elsif (l_result = 'REJECTED') then
             l_ame_status := ame_util.rejectstatus;
           else -- reject for lack of information, conservative approach
             l_ame_status := ame_util.rejectstatus;
           end if;
           --set approver as approved or rejected based on approver response
           ame_api2.updateapprovalstatus2(  applicationidin=>l_application_id,
                                            transactiontypein=>l_transaction_type,
                                            transactionidin=>l_transaction_id,
                                            approvalstatusin => l_ame_status,
                                            approvernamein => l_approver_name);     
     
     elsif  ( funcmode = 'TRANSFER' ) then

            --l_transaction_id :=  itemkey;
            l_forwardeein.name :=wf_engine.context_new_role;
            l_original_approver_name:= wf_engine.context_original_recipient;


              ame_api2.updateapprovalstatus2(applicationidin=>l_application_id ,
                        transactiontypein=>l_transaction_type,
                        transactionidin=>l_transaction_id,
                        approvalstatusin => 'FORWARD',
                        approvernamein => l_original_approver_name,
          forwardeein => l_forwardeein );

     end if; -- run

     resultout:= wf_engine.eng_completed || ':' || l_result;

    exception
      when others then
        wf_core.context('xx_ame_approval_pkg',
                        'updateAmeWithResponse',
                        itemtype,
                        itemkey,
                        to_char(actid),
                        funcmode);
        
        raise;
    end updateAmeWithResponse;    
      

END; 
/

What we had done in package above is simple ;

in getNextApprover : we used AME engine to find existing situation of our notification and approvers. Then we created a role for the current approver then set it as notification performer .

in updateAmeWithResponse : we updated current notification status using AME engine(ame_api2 package) and control if it is in timeout period or not , also if it is final approver of AME approver group.

Now we begin to start this WF process from this following package below for Orders which have holds (startApprovalWF procedure) .  We can log this process (insertLog procedure) . We set document type attribute to set a custom OAF page for notification content. We also have custom business rules like timeout for notification process just in case our approver doesn’t give a response to it . (getTimeoutDay function). In this case our AME transaction type has the same name as Workflow item type . Package ;

CREATE OR REPLACE package xx_ame_start_appr_pkg is

       procedure startApprovalWF(errbuff out varchar2,
                                 retcode out number,
                                 p_order_number in number,
                                 p_hold_type in varchar2 ,
                                 p_comment in varchar2
                                ) ;
       function getTimeoutDay (p_header_id in number) return number ;
       procedure insertLog (itemtype IN VARCHAR2,
                            itemkey IN VARCHAR2,
                            p_log in varchar2
                            ) ;
       
end ;
/

 

CREATE OR REPLACE package apps.xx_ame_start_appr_pkg is 

	/*This procedure starts our Approval WF via setting AME transaction types and
	required information (this can be also called as Concurrent Program)*/
	procedure startApprovalWF(errbuff out varchar2,
							  retcode out number,
							  p_order_number in number,  
							  p_hold_type in varchar2, 
							  p_comment in varchar2
							 ) is
	 cursor c1 is
		 select oh.header_id ,
				oh.order_number 
		   from oe_order_holds_all ha ,
				oe_order_headers_all oh
		 where ha.header_id = oh.header_id 
		   and oh.ORDER_NUMBER= p_order_number
		   and ha.released_flag = 'N';

	 itemkey varchar2(240);
	 itemtype varchar2(8);
	 created_by_user_name varchar2(250);
	 l_seq number ;
	 is_entered boolean := false ;

	 begin

		 select xxxl.xxxl_wf_item_key.nextval into l_seq from dual ;
		 itemkey := l_seq||'-'||p_hold_type;
		 itemtype :='YOUR CUSTOM WF NAME'; 
		 
		 for crec in c1 loop

			 insertLog (itemtype ,
						itemkey ,
						'startApprovalWF procedure has been called');

			 wf_engine.createprocess (itemtype, 
			 itemkey ,
			 'YOUR MAIN PROCESS NAME'); 

			 select user_name into created_by_user_name
		   	   from fnd_user
			  where user_id = fnd_profile.value('USER_ID');

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'CREATED_BY_USER_NAME',
			 avalue=>created_by_user_name) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'HOLD_TYPE',
			 avalue=>p_hold_type) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'AME_TRANSACTION_TYPE',
			 avalue=>'YOUR CUSTOM WF NAME') ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'AME_TRANSACTION_ID',
			 avalue=>crec.header_id) ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'ORDER_HEADER_ID',
			 avalue=>crec.header_id
			 ) ;

			 wf_engine.setItemAttrNumber(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'ORDER_NUMBER',
			 avalue=>crec.order_number
			 ) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'START_COMMENT',
			 avalue=>p_comment) ;

			 wf_engine.setItemAttrText(itemtype =>itemtype,
			 itemkey =>itemkey,
			 aname =>'NUMBER_OF_WAIT_DAYS',
			 avalue=>getTimeoutDay(crec.header_id )) ;

			 wf_engine.setitemattrtext (itemtype,  
			 itemkey,
			 'NTF_HOLD_CONTENT_DOC',
			 'JSP:/OA_HTML/OA.jsp?page=/xxxl/oracle/apps/xxxl/ame/webui/XxxlAmeNtfRN&xx_order_header_id='||crec.header_id
			 ||'&xx_ntf_type='||p_hold_type
			 ||'&xx_item_type='||itemtype
			 ||'&xx_item_key='||itemkey);

			 wf_engine.startprocess (itemtype, 
			 itemkey);

			 is_entered := true ;

		 exit;
		 end loop;

		 if (not is_entered) then

			 insertLog (itemtype ,
			 itemkey ,
			 'startApprovalWF no record has been found' );
			 fnd_file.put_line (fnd_file.LOG,'startApprovalWF no record has been found');
			 retcode := 2; --error
			 RETURN;

		 end if ;

	end ;
	
	/*This function defines our custom rules for timeout function in our AME WF*/
	function getTimeoutDay (p_header_id in number) return number is
    l_control number;
    begin

        begin

            select 1 into l_control from oe_transaction_types_tl ott,
                                         oe_order_headers_all ooh
                                   where language='US'
                                     and ott.name like 'Your Custom Transaction Type'
                                     and ooh.header_id=p_header_id
                                     and ott.transaction_type_id= ooh.order_type_id;

            /*Your Custom Transaction Type Order if they wouldn't order until next day at 5:00pm approval process cancel*/
            return  round( ((24-to_char(sysdate,'HH24') ))+ 17 )*60;  --dakika
          --round( ((24-to_char(sysdate,'HH24') )/24)+ 17/24 , 2)  ;

        exception when others then
		
			/*If it is not approved in 5 days it will be cancelled */
            return 5*24*60  ;  --dakika

        end ;


    end ;
	
	procedure insertLog (itemtype IN VARCHAR2,
                         itemkey IN VARCHAR2,
                         p_log in varchar2
                        ) is

    PRAGMA AUTONOMOUS_TRANSACTION;
    l_rec xxxl.xxxl_wf_log%rowtype ;
    begin

       l_rec.item_type:=itemtype  ;
       l_rec.item_key:=itemkey ;
       l_rec.xlog:=p_log  ;
       l_rec.object_name:='xx_ame_start_appr_pkg' ;
       l_rec.trnx_Date :=sysdate ;
       insert into xxxl.xxxl_wf_log VALUES l_rec ;
       commit;

    end ; 

END ;

I hope this will help to increase in demand for this productive Oracle Engine . We finished Creating Custom Oracle Workflow using AME process ! Thanks.

 

 

 

Please like & share:

Oracle Inventory Reservation Transfer between Different Sources

Creating reservation for inventory items is one of most common actions regarding business requirements in Oracle E-Business Suite platform. In this example, we transfer existing reservation quantity between different transaction source types/organizations/uom’s in R12 version of EBS.

For each three item-organization-transaction source type , there should be only one unique row in INV.MTL_RESERVATIONS table. So following loop is for a better code review and look. (using cursor depends on you).  We use standart Oracle Inventory API’s and table/record types in this procedure. Here is the PL/SQL script;

procedure transfer_reservation  (  p_rsv_rec in  inv_reservation_global.mtl_reservation_rec_type,
								   x_msg_count out nocopy number,
								   x_msg_data  out nocopy varchar2,
								   x_return_status out varchar2,
								   x_reservation_id out number ) is

x_serial_number   inv_reservation_global.serial_number_tbl_type;
v_rsv_from_rec    inv_reservation_global.mtl_reservation_rec_type ;
v_rsv_to_rec      inv_reservation_global.mtl_reservation_rec_type ;
ln_total_res_qty number:=0 ;

cursor c_res(p_inv_item_id number , p_organization_id number , p_uom varchar2)  is
		select	re.reservation_id,
		        /*you can use a your conversion functions if your source 
				and destination for different UOM's*/
				xxln_inv_pkg.UOM_CONVERSION(p_inventory_item_id =>p_inv_item_id,
					  			       pcur_qty =>re.reservation_quantity,
								       pcur_uom =>re.reservation_uom_code,
								       pnew_uom =>p_uom) as reservation_quantity,
				re.reservation_uom_code,
				inventory_item_id,organization_id
		   from mtl_reservations re
		  where organization_id = p_organization_id
			and inventory_item_id = p_inv_item_id
			and demand_source_type_id = (select transaction_source_type_id
										   from mtl_txn_source_types														
									      where transaction_source_type_name = 'SOME-TRANSACTION-SOURCE')
	   order by re.creation_date;

begin
		v_rsv_to_rec.demand_source_type_id  := p_rsv_rec.demand_source_type_id; 
		v_rsv_to_rec.demand_source_name     := to_char(sysdate);

		for crec in c_res(p_rsv_rec.inventory_item_id,p_rsv_rec.organization_id, p_rsv_rec.reservation_uom_code)  loop

			v_rsv_from_rec.reservation_id  := crec.reservation_id;
			
			/*Here I use each remaining quantity from source until it equals to demand amount.
			 After process , we could have still remaining amount in our quota*/
			if( p_rsv_rec.reservation_quantity-ln_total_res_qty<crec.reservation_quantity ) then

				v_rsv_to_rec.reservation_quantity :=p_rsv_rec.reservation_quantity-ln_total_res_qty;
				v_rsv_to_rec.reservation_uom_code :=p_rsv_rec.reservation_uom_code;
				ln_total_res_qty:=ln_total_res_qty+v_rsv_to_rec.reservation_quantity ;

			else

				v_rsv_to_rec.reservation_quantity := crec.reservation_quantity;
				v_rsv_to_rec.reservation_uom_code := p_rsv_rec.reservation_uom_code;
				ln_total_res_qty:=ln_total_res_qty+v_rsv_to_rec.reservation_quantity;

			end if;

			inv_reservation_pub.transfer_reservation(p_api_version_number        => 1.0,
													 p_init_msg_lst              => FND_API.G_TRUE,
													 x_return_status             => x_return_status,
													 x_msg_count                 => x_msg_count,
													 x_msg_data                  => x_msg_data,
												   --p_is_transfer_supply       IN  VARCHAR2 DEFAULT fnd_api.g_true
													 p_original_rsv_rec          =>  v_rsv_from_rec,
													 p_to_rsv_rec                =>  v_rsv_to_rec ,
													 p_original_serial_number    =>  x_serial_number ,
													 p_to_serial_number          =>  x_serial_number ,
												   --p_validation_flag          IN  VARCHAR2 DEFAULT fnd_api.g_true
		   										   --p_over_reservation_flag    IN  NUMBER DEFAULT 0
													 x_to_reservation_id         =>x_reservation_id);


			if (x_return_status<>'S') then

				return  ;

			end if ;

		end loop ;

end ;

That’s it !

Please like & share:

Data Migration via Advanced PL/SQL – Using Bulk Processing

Data Migration/Manuplation operations are common in projects, in this case we will use “Bulk Processing” for fastest UPDATE operations in Oracle Databases. We have two staging tables; xxln.xxln_bom_operational_routings xbor and xxln.xxln_bom_operation_sequences . We are moving old sequence values on our production tables in E-Business Suite BOM (Bill of materials) schema .

This PL/SQL code shows how it works ;

declare 

cursor c1 is 
      select /*+ FIRST_ROWS
             */ 
             bos.rowid as main_comp_rowid, 
             xbos.routing_sequence_id,
             xbos.operation_sequence_id
            from xxln.xxln_bom_operational_routings xbor, 
                 xxln.xxln_bom_operation_sequences xbos ,   
                 mtl_system_items_b msib, 
                 bom_operational_routings bor,
                 bom_operation_sequences bos ,
                 mtl_parameters mp  
            where xbor.assembly_item_code = msib.segment1
              and xbor.organization_code = mp.organization_code
              and bor.organization_id=mp.organization_id
              and msib.organization_id = mp.organization_id
              and bor.assembly_item_id = msib.inventory_item_id
              and bor.organization_id = mp.organization_id
              and bos.routing_sequence_id = bor.routing_sequence_id
              and xbos.routing_sequence_id= xbor.routing_Sequence_id
              and nvl(bor.alternate_routing_designator ,'-99') = nvl(xbor.alternate_routing_designator,'-99')
              and nvl(bos.disable_date,sysdate-1000) = nvl(xbos.disable_date,sysdate-1000)  
              and bos.operation_seq_num = xbos.operation_seq_num ;              
              
type crec_type is table of c1%rowtype index by binary_integer; 
crec crec_type ; 

begin  

        open c1 ; 
        loop 
        fetch c1 bulk collect into crec limit 2000000 ; 
            forall i in crec.first..crec.last 
                     update bom_operation_sequences set 
                            attribute14 = crec(i).routing_sequence_id,
                            attribute15 = crec(i).operation_sequence_id,
                            last_update_date=sysdate,
                            last_updated_by=-1
                            where rowid =crec(i).main_comp_rowid ; 
        commit; 
        exit when c1%notfound ; 
        end loop ; 
          
end ;

We use main tables rowid on UPDATE operation , FIRST_ROWS hint and combine it fetching data with BULK COLLECT INTO and FORALL LOOP for best result. You can also divide the query by adding a condition and execute it in different times , this helps cleaning rollback segments (caused by commit) if you are working on VLDB ‘s .

Thanks!

Please like & share: