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;