handling error in the procedure that is executed in for loop

raghunadhan

Hi All,

I had a procedure that is called multiple times in a for loop.

In that procedure there is insert statements.And if any error is raised that should be logged in a table and iterate to next loop without exiting the procedure.

Below is the code.

create or replace PROCEDURE PROC_AUTH_MAPPING(P_EMAIL VARCHAR2,P_SEGMENT_CD VARCHAR2,

P_MAINCHANNEL_CD VARCHAR2,P_LCL_FUNCTIONALAREA_CD VARCHAR2,P_KEYCATEGORYCLUSTER_CD VARCHAR2,P_APP_CODE VARCHAR2,P_REPORTINUNIT_CODE VARCHAR2,P_REQUEST_TYPE VARCHAR2,P_SUBMITTED_USER VARCHAR2) IS

V_USER_LIST varchar2(1000);

v_sql VARCHAR2(4000);

ERRM_MSG CLOB;

BEGIN

--GTI INSERTINT INTO GTT TABLE

--I INSERTING THE ACCESS DETAILS TO TABLES

--L INSERTINT INTO LOG TABLE.

IF P_REQUEST_TYPE='GTI' THEN

  IF P_SEGMENT_CD IS NOT NULL THEN

  INSERT INTO MFR_AUTH_GTT(email,segment_cd,submitted_code) VALUES(P_EMAIL,P_SEGMENT_CD,P_SUBMITTED_USER);

  commit;

  END IF;

  IF P_MAINCHANNEL_CD IS NOT NULL THEN

  -- update MFR_AUTH_GTT set MAINCHANNEL_CD=P_MAINCHANNEL_CD where upper(email)=upper(P_EMAIL);

  INSERT INTO MFR_AUTH_GTT(email,MAINCHANNEL_CD,submitted_code) VALUES(P_EMAIL,P_MAINCHANNEL_CD,P_SUBMITTED_USER);

  commit;

  END IF;

  IF P_LCL_FUNCTIONALAREA_CD IS NOT NULL THEN

  -- update MFR_AUTH_GTT set LCL_FUNCTIONALAREA_CD=P_LCL_FUNCTIONALAREA_CD where upper(email)=upper(P_EMAIL);

  INSERT INTO MFR_AUTH_GTT(email,LCL_FUNCTIONALAREA_CD,submitted_code) VALUES(P_EMAIL,P_LCL_FUNCTIONALAREA_CD,P_SUBMITTED_USER);

   commit;

  END IF;

  IF P_KEYCATEGORYCLUSTER_CD IS NOT NULL THEN

  -- update MFR_AUTH_GTT set KEYCATEGORYCLUSTER_CD=P_KEYCATEGORYCLUSTER_CD where upper(email)=upper(P_EMAIL);

   INSERT INTO MFR_AUTH_GTT(email,KEYCATEGORYCLUSTER_CD,submitted_code) VALUES(P_EMAIL,P_KEYCATEGORYCLUSTER_CD,P_SUBMITTED_USER);

   commit;

  END IF;

  IF P_REPORTINUNIT_CODE IS NOT NULL THEN

    -- update MFR_AUTH_GTT set REPORTINGUNIT_CD=P_REPORTINUNIT_CODE where upper(email)=upper(P_EMAIL);

     INSERT INTO MFR_AUTH_GTT(email,REPORTINGUNIT_CD,submitted_code) VALUES(P_EMAIL,P_REPORTINUNIT_CODE,P_SUBMITTED_USER);

     commit;

  END IF;

  IF P_APP_CODE IS NOT NULL THEN

  -- update MFR_AUTH_GTT set app_cd=P_APP_CODE where upper(email)=upper(P_EMAIL);

   INSERT INTO MFR_AUTH_GTT(email,app_cd,submitted_code) VALUES(P_EMAIL,P_APP_CODE,P_SUBMITTED_USER);

  commit;

  END IF;

END IF;   

IF P_REQUEST_TYPE='L' THEN

select LISTAGG(DISTINCT t.email, '~') into V_USER_LIST 

 from (

select distinct email from (

 SELECT email from MFR_AUTH_SEGMENT 

 UNION ALL 

 SELECT email from MFR_AUTH_MAINCHANNEL  

 UNION ALL 

 SELECT EMAIL FROM MFR_AUTH_LCL_FUNCTIONALAREA

 UNION ALL

SELECT EMAIL FROM MFR_AUTH_KEYCATEGORYCLUSTER 

UNION ALL 

SELECT EMAIL FROM MFR_AUTH_APP

UNION ALL 

SELECT EMAIL FROM MFR_AUTH_RPH)) T inner join MFR_AUTH_GTT gtt on T.email=gtt.EMAIL; 

INSERT INTO mfr_auth_log 

(EMAIL,SEGMENT_CD,MAINCHANNEL_CD,LCL_FUNCTIONALAREA_CD,KEYCATEGORYCLUSTER_CD,APP_CD,REPORTINGUNIT_CD,auth_type,submitted_code,submitted_ts)

    SELECT gtt.EMAIL, 

LISTAGG(DISTINCT segment_cd,','),

LISTAGG(DISTINCT MAINCHANNEL_CD,','),

LISTAGG(DISTINCT LCL_FUNCTIONALAREA_CD,','),

LISTAGG(DISTINCT KEYCATEGORYCLUSTER_CD,','),

LISTAGG(DISTINCT app_cd,','),

        LISTAGG(DISTINCT REPORTINGUNIT_CD,','),

CASE WHEN V_USER_LIST LIKE '%'||email||'%' THEN 'MODIFY' ELSE 'ADD' END AS auth_type,

submitted_code as submitted_code, 

SYSTIMESTAMP as submitted_ts

from MFR_AUTH_GTT gtt

       GROUP BY EMAIL, CASE WHEN V_USER_LIST LIKE '%'||email||'%' THEN 'MODIFY' ELSE 'ADD' END, submitted_code;

   commit;

  DELETE FROM MFR_AUTH_SEGMENT WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

  DELETE FROM MFR_AUTH_MAINCHANNEL WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

  DELETE FROM MFR_AUTH_LCL_FUNCTIONALAREA WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

  DELETE FROM MFR_AUTH_KEYCATEGORYCLUSTER WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

  DELETE FROM MFR_AUTH_APP WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

   DELETE FROM MFR_AUTH_RPH WHERE EMAIL IN(SELECT DISTINCT EMAIL from MFR_AUTH_GTT);

COMMIT;

END IF;

IF P_REQUEST_TYPE ='I' THEN

  IF P_SEGMENT_CD IS NOT NULL THEN

   INSERT INTO MFR_AUTH_SEGMENT(EMAIL,SEGMENT_CD) VALUES(P_EMAIL,P_SEGMENT_CD);

   commit;

  END IF;

  IF P_MAINCHANNEL_CD IS NOT NULL THEN

  INSERT INTO MFR_AUTH_MAINCHANNEL(EMAIL,MAINCHANNEL_CD) VALUES(P_EMAIL,P_MAINCHANNEL_CD);

  commit;

  END IF;

  IF P_LCL_FUNCTIONALAREA_CD IS NOT NULL THEN

  INSERT INTO MFR_AUTH_LCL_FUNCTIONALAREA(EMAIL,LCL_FUNCTIONALAREA_CD) VALUES(P_EMAIL,P_LCL_FUNCTIONALAREA_CD);

   commit;

  END IF;

  IF P_REPORTINUNIT_CODE IS NOT NULL THEN

  v_sql:='INSERT INTO MFR_AUTH_RPH(EMAIL,REPORTINGUNIT_CD,country_cd)select email, REPORTINGUNIT_CD, dim_rph.country_cd from mfr_auth_gtt gtt inner join 

  (SELECT NULL AS totalcompany_cd, dim_rph_sbm.submarketcluster_cd, dim_rph_sbm.submarket_cd,dim_rph_zne.zone_cd, 

dim_rph_cty.country_cd FROM dim_rph_cty dim_rph_cty 

JOIN dim_rph_zne  dim_rph_zne ON dim_rph_cty.zone_cd = dim_rph_zne.zone_cd

JOIN dim_rph_sbm  dim_rph_sbm on dim_rph_zne.submarket_cd = dim_rph_sbm.submarket_cd

UNION ALL

SELECT '' G001AR'' AS totalcompany_cd, NULL AS submarketcluster_cd, NULL AS submarket_cd, 

NULL AS zone_cd, ''ALL'' AS country_cd FROM dual) dim_rph

ON gtt.REPORTINGUNIT_CD = dim_rph.totalcompany_cd

OR gtt.REPORTINGUNIT_CD = dim_rph.submarketcluster_cd

OR gtt.REPORTINGUNIT_CD = dim_rph.submarket_cd

OR gtt.REPORTINGUNIT_CD = dim_rph.zone_cd

OR gtt.REPORTINGUNIT_CD = dim_rph.country_cd

WHERE REPORTINGUNIT_CD <> ''ALL'' and REPORTINGUNIT_CD ='''|| P_REPORTINUNIT_CODE||''' and gtt.email='''||P_EMAIL ||'''';

  EXECUTE IMMEDIATE V_SQL;

   commit;

  END IF;

   IF P_KEYCATEGORYCLUSTER_CD IS NOT NULL THEN

  INSERT INTO MFR_AUTH_KEYCATEGORYCLUSTER(EMAIL,KEYCATEGORYCLUSTER_CD) VALUES(P_EMAIL,P_KEYCATEGORYCLUSTER_CD);

   commit;

  END IF;

  IF P_APP_CODE IS NOT NULL THEN

  INSERT INTO MFR_AUTH_APP(EMAIL,APP_CD) VALUES(P_EMAIL,P_APP_CODE);

  commit;

  END IF;

END IF;

IF P_REQUEST_TYPE='D' THEN

  DELETE FROM MFR_AUTH_GTT;

  COMMIT;

END IF;

END;

Executing

for s in segment_values 

   loop

      BEGIN [email protected]_ERMDATA(U.ID,S.ID,NULL,NULL,NULL,NULL,NULL,'GTI',:APP_USER); END ;

     commit;      

  end loop;

L. Fernigrini

You can use a separate procedure using AUTONOMOUS TRANSACTION to log the error:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/AUTONOMOUS_TRANSACTION-pragma.html

The logic would be to have an exception section on the existing procedure where you call the logging procedure (that does a commit internally but does not affect the caller transaction) and then rollback current transaction and return control to the main loop.

Consider reading this great blog article:

http://stevenfeuersteinonplsql.blogspot.com/2017/02/now-not-to-handle-exceptions.html

L. Fernigrini

Consider reading this article:

http://stevenfeuersteinonplsql.blogspot.com/2017/02/now-not-to-handle-exceptions.html

You may need to use a SP with autonomous transaction to log the error into a table, the article explains how to create a simple one, and you can read on the basics of Autonomous Transactions here:

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/AUTONOMOUS_TRANSACTION-pragma.html

raghunadhan

When I use sp for logging errors it logs the error comes out from that loop.I need to log the error and without exiting the loop it need to go to next iteration of loop

raghunadhan

Can anyone help on this?

L. Fernigrini

You need to create a block with the exception handling inside, handle it and leave the block without re-raising the exception.

There are plenty of examples both here and in other forums, like this one:

https://community.oracle.com/tech/developers/discussion/2397836/for-loop-exception-handling