This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,512 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

handling error in the procedure that is executed in for loop

raghunadhan
raghunadhan Member Posts: 311 Red Ribbon
edited Mar 9, 2022 3:39PM in SQL Developer

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;

Tagged:

Answers