database - How can I copy records between tables only if they are valid according to check constraints in Oracle? -

database - How can I copy records between tables only if they are valid according to check constraints in Oracle? -

i don't know if possible, want re-create bunch of records temp table normal table. problem records may violate check constraints want insert possible , generate error logs somewhere else invalid records.

if execute:

insert normal_table select ... temp_table

nothing inserted if record violates constraint. create loop , manually insert 1 one, think performance lower.

ps: if possible, i'd solution works oracle 9

from oracle 10gr2, can utilize log errors clause:

execute dbms_errlog.create_error_log('normal_table'); insert normal_table select ... temp_table log errors reject limit unlimited;

in simplest form. can see errors got:

select ora_err_mesg$ err$_normal_table;

more on create_error_log step here.

i think approach works 9i, don't have instance available test on, run on 11gr2 update: tested , tweaked (to avoid pls-00436) in 9i:

declare type t_temp_table table of temp_table%rowtype; l_temp_table t_temp_table; l_err_code err_table.err_code%type; l_err_msg err_table.err_msg%type; l_id err_table.id%type; cursor c select * temp_table; error_array exception; pragma exception_init(error_array, -24381); begin open c; loop fetch c mass collect l_temp_table limit 100; exit when l_temp_table.count = 0; begin forall in 1..l_temp_table.count save exceptions insert normal_table values l_temp_table(i); exception when error_array j in 1..sql%bulk_exceptions.count loop l_id := l_temp_table(sql%bulk_exceptions(j).error_index).id; l_err_code := sql%bulk_exceptions(j).error_code; l_err_msg := sqlerrm(-1 * sql%bulk_exceptions(j).error_code); insert err_table(id, err_code, err_msg) values (l_id, l_err_code, l_err_msg); end loop; end; end loop; end; /

with real columns instead of id, i've done demo purposes:

create table normal_table(id number primary key); create table temp_table(id number); create table err_table(id number, err_code number, err_msg varchar2(2000)); insert temp_table values(42); insert temp_table values(42);

then run anonymous block above...

select * normal_table; id ---------- 42 column err_msg format a50 select * err_table; id err_code err_msg ---------- ---------- -------------------------------------------------- 42 1 ora-00001: unique constraint (.) violated

this less satisfactory on few levels - more coding, slower if have lot of exceptions (because of individual inserts those), doesn't show constraint violated (or other error details), , won't retain errors if rollback - though phone call autonomous transaction log if issue, uncertainty here.

if have little plenty volume of info not want worry limit clause can simplify bit:

declare type t_temp_table table of temp_table%rowtype; l_temp_table t_temp_table; l_err_code err_table.err_code%type; l_err_msg err_table.err_msg%type; l_id err_table.id%type; error_array exception; pragma exception_init(error_array, -24381); begin select * mass collect l_temp_table temp_table; forall in 1..l_temp_table.count save exceptions insert normal_table values l_temp_table(i); exception when error_array j in 1..sql%bulk_exceptions.count loop l_id := l_temp_table(sql%bulk_exceptions(j).error_index).id; l_err_code := sql%bulk_exceptions(j).error_code; l_err_msg := sqlerrm(-1 * sql%bulk_exceptions(j).error_code); insert err_table(id, err_code, err_msg) values (l_id, l_err_code, l_err_msg); end loop; end; /

the 9i documentation doesn't seem online more, in new-features document, , lots of people have written - it's been asked here before too.

database oracle check-constraints