Home » SQL & PL/SQL » SQL & PL/SQL » skipping errors with insert select
skipping errors with insert select [message #262915] Tue, 28 August 2007 08:43 Go to next message
123soleil
Messages: 35
Registered: July 2006
Member
Hello,

I'm trying to write a script that might give errors, but I dont want to raise an exception because then no data will be inserted.

What I need is for the script to insert all entries it can and to tell me which ones have failed (for example in a log table)

begin
INSERT INTO k.migr (i_key, i_key_id, i_key_val) 
        SELECT i_key,
            case 
                when i_obj_key='IK' then '88' 
                when i_obj_key='SNM' then '5503' 
                when i_obj_key='SWI' then '73' 
                else -- Here i need to write some sort of error message... how?
            end case,
            i_key_val from k.migr_ld;
end;


Thanks!
Re: skipping errors with insert select [message #262919 is a reply to message #262915] Tue, 28 August 2007 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I dont want to raise an exception because then no data will be inserted.

There is no error because you insert nothing as this is not an error.
SQL> create table t (val integer);

Table created.

SQL> begin
  2    insert into t (val) select val from t;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Quote:
What I need is for the script to insert all entries it can and to tell me which ones have failed

Have a look at error loggin clause of INSERT statement.

Regards
Michel
Re: skipping errors with insert select [message #262925 is a reply to message #262915] Tue, 28 August 2007 09:02 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Adding to Michel's post.

I didn't understand why do you want to try to insert the rows, for which you already know it will error out.
Quote:
when i_obj_key='IK' then '88'
when i_obj_key='SNM' then '5503'
when i_obj_key='SWI' then '73'
Why don't you put a filter on them. Right?

By
Vamsi
Re: skipping errors with insert select [message #262942 is a reply to message #262915] Tue, 28 August 2007 10:04 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
Thank you for your replies. I don't think I expressed myself correctly sorry.

What I am trying to do is to map data. I get codes in a string format and I wish to map them in a numerical format (IK becomes 88, SNM -> 5503, etc...). These codes will then be inserted in another table.

Now let's say I get an unexpected value (for example PKY). I would like to have an error message "Error: unexpected value PKY" (This would ideally be done in the "else" case). If I raise a custom error, it will do a rollback which is exactly what i would like to avoid... It should just skip that entry and continue.

Thanks

[Updated on: Tue, 28 August 2007 10:07]

Report message to a moderator

Re: skipping errors with insert select [message #262962 is a reply to message #262942] Tue, 28 August 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (val integer);

Table created.

SQL> begin
  2    insert into t (val) select 1/0 from dual;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 2

Use "1/0" or any invalid operation in "else", log error clause, and then loop on the errors and display message for those rows.

Regards
Michel
Re: skipping errors with insert select [message #263139 is a reply to message #262962] Wed, 29 August 2007 03:34 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
great, that seems to work thank you!

But it doesn't seem to reference the line where error occured?

Another nice-to-have feature would be to have a custom error message instead of the "divisor is equal to zero" message... is there a possibility?

thanks again
Re: skipping errors with insert select [message #263157 is a reply to message #263139] Wed, 29 August 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace function f (msg in varchar2) return varchar2 is
  2  begin
  3    raise_application_error (-20000, msg);
  4    return msg;
  5  end;
  6  /

Function created.

SQL> begin 
  2    insert into t select f('my message') from dual;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-20000: my message
ORA-06512: at "MICHEL.F", line 3
ORA-06512: at line 2

Regards
Michel
Re: skipping errors with insert select [message #263182 is a reply to message #263157] Wed, 29 August 2007 05:24 Go to previous message
123soleil
Messages: 35
Registered: July 2006
Member
fantastic, thanks!
Previous Topic: Adding array values to an array
Next Topic: no of weeks
Goto Forum:
  


Current Time: Thu Dec 08 02:07:50 CST 2016

Total time taken to generate the page: 0.10658 seconds