| Log errors reject limit unlimited [message #687998] |
Tue, 08 August 2023 04:14  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a huge query consists of subqueries including union all. It inserts into a table.
For e.g.
INSERT INTO target_table
SELECT * FROM (subqueries having union all);
The problem I am facing is the entire INSERT fails for any error record. I would like to have the error record copied to error table without affecting other records for which I though of using "log errors reject limit unlimited" - I can't use record by record processing using for loop due to performance issues.
The challenge I am facing with "log errors reject limit unlimited" is that it failed for subqueries having multiple joins.
Below is just an illustration of the problem.
drop table t_sr_part;
drop table t_sr_part_tgt;
drop table ERR$_T_SR_PART_tgt;
truncate table ERR$_T_SR_PART_tgt;
--
create table t_sr_part(id number, name varchar2(200), upd_dt date);
begin
insert into t_sr_part values(1, 's',to_date('12/12/2022','mm/dd/yyyy'));
insert into t_sr_part values(2, 's',to_date('12/12/2012','mm/dd/yyyy'));
insert into t_sr_part values(3, 's',to_date('12/12/2021','mm/dd/yyyy'));
insert into t_sr_part values(4, 's',to_date('12/12/2020','mm/dd/yyyy'));
commit;
end;
/
CREATE TABLE t_sr_part_tgt (id number, name varchar2(200), upd_dt date);
-- create error log table
exec dbms_errlog.create_error_log('t_sr_part_tgt');
-- A function to raise error
create or replace function f_sr_test (p_in number)
return number
as
e_bad_date_format EXCEPTION;
PRAGMA EXCEPTION_INIT (e_bad_date_format, -01841);
BEGIN
if (p_in = 1)
then
raise e_bad_date_format;
end if;
return p_in;
END;
/
-- This works
begin
insert into t_sr_part_tgt
select f_sr_test(id), name, upd_dt from (select id, name, upd_dt from t_sr_part)
log errors reject limit unlimited;
end;
/
-- The table is inserted with the error record and rest of the records are inserted into target table
select * from ERR$_T_SR_PART_TGT;
-- It fails
begin
insert into t_sr_part_tgt
select f_sr_test(t1.id1), t1.name, t2.upd_dt from (select f_sr_test(id) id1, name, upd_dt from t_sr_part) t1, t_sr_part t2 where t1.id1 = t2.id
log errors reject limit unlimited;
end;
/
Regards,
Pointers
|
|
|
|
| Re: Log errors reject limit unlimited [message #688000 is a reply to message #687998] |
Tue, 08 August 2023 07:34  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Technically both should raise error. However for INSERT SELECT optimizer does the following:
1. SELECT phase identifies SELECTed rows without calculating selecct list expressions.
2. INSERT phase calculates select list expressions and inserts them into target table. So exception raised duting INSERT phase are considered insert errors and are subjecct to LOG ERRORS.
First query raises exception while calculating select list expression f_sr_test(id), therefore log errors bypasses insert errors. Second query also raises exception ccalculating f_sr_test(id) but is is raised during SELECT phase when performing join condition before INSERT phase starts, so log errors which is insert related clause has no effect.
SY.
|
|
|
|
| Re: Log errors reject limit unlimited [message #688004 is a reply to message #688000] |
Tue, 08 August 2023 08:37 |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Thank you Solomon for your prompt response.
Although the above one is a sample example. Is there an alternate way to capture the errors raised during SELECT phase using LOG ERRORS so that the rest of the records are inserted without any issue.
Regards,
Pointers
|
|
|
|
| Re: Log errors reject limit unlimited [message #688005 is a reply to message #688004] |
Tue, 08 August 2023 11:27 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
It should be looked at on case by case basis depending on requirements. For example, you could create "wrapper" function:
create or replace
function f_sr_test_for_select(
p_in number
)
return number
is
begin
return f_sr_test(
p_in
);
exception
when others
then
-- log error
return null;
end;
/
begin
insert
into t_sr_part_tgt
select f_sr_test(t1.id1),
t1.name,
t2.upd_dt
from (
select f_sr_test_for_select(id) id1,
name,
upd_dt
from t_sr_part
) t1,
t_sr_part t2
where t1.id1 = t2.id
log errors reject limit unlimited;
end;
/
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
