Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

handling error in the procedure that is executed in for loop

raghunadhanMar 9 2022 — edited Mar 9 2022

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 PROC_AUTH_MAPPING@MFR003_ERMDATA(U.ID,S.ID,NULL,NULL,NULL,NULL,NULL,'GTI',:APP_USER); END ;
commit;
end loop;

Comments

Processing

Post Details

Added on Mar 9 2022
5 comments
157 views