Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
handling error in the procedure that is executed in for loop

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;
Answers
-
You can use a separate procedure using AUTONOMOUS TRANSACTION to log the error:
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
-
Consider reading this article:
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:
-
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
-
Can anyone help on this?
-
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: