BULK COLLECT EXCEPTION HANDLING

As we all know how to use BULK COLLECT to handle performance issues while working with large data.
In this BLOG I help you understand how to handle exceptions while using BULK COLLECT.


DECLARE
lt_projects_tbl  CURSOR_PROJ%ROWTYPE;
BEGIN



OPEN  cursor_PROJ;

FETCH CURSOR_PROJ BULK COLLECT INTO lt_projects_tbl ;



IF lt_projects_tbl.COUNT<>0

THEN --IF OF PROJECT LOOP


   BEGIN



   FORALL i IN 1..lt_projects_tbl.COUNT SAVE EXCEPTIONS

  DELETE FROM custom_table_tb

WHERE  unique_id =lt_projects_tbl(i).unique_id;



EXCEPTION

  WHEN ge_ex_bulk_errors

  THEN



  gn_bulk_error_count  :=  SQL%BULK_EXCEPTIONS.COUNT;

  gc_error_message  :=  SUBSTR ( TO_CHAR ( gn_bulk_error_count ) || ' Errors while deleting custom table.' , 1, 2000 );


  FOR l_projects_stg_ex_bulk_indx IN 1 .. gn_bulk_error_count

  LOOP



-- Resetting for the next iteration of the loop

gn_bulk_error_code           :=  NULL;

gn_bulk_error_index          :=  NULL;

l_bulk_error_project_stg_id  :=  NULL;

gc_bulk_error_messsage       :=  NULL;



gn_bulk_error_code            :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_code;

gn_bulk_error_index           :=  SQL%BULK_EXCEPTIONS ( l_projects_stg_ex_bulk_indx ).error_index;

l_bulk_error_project_stg_id   :=   lt_projects_tbl( gn_bulk_error_index ).project_stg_id;

gc_bulk_error_messsage        :=  'Unable to delete data from projects staging table Error: '|| SQLERRM ( -gn_bulk_error_code );




UPDATE custom_table_tb  XPS

   SET XPS.status                  =  'ERROR'

, XPS.status_description      =  SUBSTR ( XPS.status_description || CHR(10)  || gc_bulk_error_messsage, 1, 4000 )

     , XPS.program_update_date     =  SYSDATE

, XPS.last_update_date        =  SYSDATE

WHERE unique_id                    =  l_bulk_error_unique_id;



  END LOOP; 



END ;

CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT

CONNECT_BY_PRIOR CLAUSE

-----------------------------------------------

As we know we that the CONNECT_BY_PRIOR clause is used to get all the employees working under particular manager.

START WITH clause is used to start with all the managers.

Now we will learn about how to use CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT.

This commands are especially used in the hierarchical trees where a child can be parent for another child. We have multiple parent child relations ships .

If we take the Project Accounting module.

The projects will have tasks and these tasks can be Hierarchical.

A child task can have a parent where the child task in turn is the parent for another child task.

Example:

We have a project - 'Test Project'

We have tasks 

10000 Task Reference 1                        -Top Root Task and Parent Task

  10000 Task Reference 2                      --Child task and parent task

     Test Subtask                                     -- child task and parent task

       Subtask2                                         --only child task

  10000 Task Reference 3                     --only child task

Over Head Allocations                          --task

In the above Tree structure , we have multiple parents like '10000 Task Reference 1' and '10000 Task Reference 2 ', but the top parent for all the sub tasks is '10000 Task Reference 1'.

In order to identfy the top task we should use CONNECT_BY_ROOT and in the above structure it returns value '10000 Task Reference 1'.

If we want find all the multiple child tasks which are leaf nodes, that means they do not have any further children then we use CONNECT_BY_ISLEAF.

In the above query if we use CONNECT_BY_ISLEAF it returns value 1 if the current row is leaf node else it returns value 0.

That means the query will return value 1 for nodes like "Subtask2" and "10000 Task Reference 3" and "Over HEad Allocations"as they are leaf nodes they do not have further children.

For rest of the tasks it returns value 0.

Query :

SELECT child_task
       ,parent_task
       ,CONNECT_BY_ISLEAF IS_LOWEST_LEVEL 
       ,CONNECT_BY_ROOT (parent_task)

 FROM

(SELECT pt_child.task_name child_task,

     pt_parent.task_name parent_task

FROM pa_tasks pt_parent,

      pa_tasks pt_child,

        pa_projects_all     PPA

 WHERE pt_parent.project_id(+) = pt_child.project_id

 AND   ppa.project_id =pt_child.project_id

 AND   PPA.project_id =

 AND pt_parent.task_id(+)=pt_child.parent_task_id) inner_query

 CONNECT BY PRIOR inner_query.child_Task=inner_query.parent_task


 START WITH inner_query.parent_Task IS NULL