Home » SQL & PL/SQL » SQL & PL/SQL » exception DUP_VAL_ON_INDEX
exception DUP_VAL_ON_INDEX [message #291658] Sat, 05 January 2008 12:57 Go to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Hallo everybody,
I have problem wirth exception DUP_VAL_ON_INDEX. I explain my problem for sample : I have following procedure:

Procedure tomek()

begin

insert into name_table
(id --id is PK
,surname)
values (ID
,SURNAME
)
select ID, SURNAME from any_table ;


exception

when DUP_VAL_ON_INDEX then

dbms_output.put_line ('value ID=' || || 'value SURNAME=' || );

end tomek;

and now my question. In exception I would like get value ID and SURNAME . Is it possible in this sample ?.
Please , help me

best regards
Tomek
Re: exception DUP_VAL_ON_INDEX [message #291659 is a reply to message #291658] Sat, 05 January 2008 13:06 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You can always put "id" and "surname" into variables before the INSERT statement.
Re: exception DUP_VAL_ON_INDEX [message #291660 is a reply to message #291659] Sat, 05 January 2008 13:55 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Thanks but I don't undestand.

Can you show me for my sample ?


Re: exception DUP_VAL_ON_INDEX [message #291661 is a reply to message #291658] Sat, 05 January 2008 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use LOG ERRORS clause and exception table.
See http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB and example below.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #291664 is a reply to message #291661] Sat, 05 January 2008 14:56 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
However, as older Oracle versions don't know about logging errors as 10g does, you'll have to do it in an oldfashioned way - record by record.

In order to do that, create a loop and either log exceptions into a table (you'll have to create it), or show it on the screen. The first approach is a little bit more complicated, but - at the end - you'll benefit from it as you'll have those records stored in a table. The second one is OK if there is a small number of incorrect records (only a few) because you'll have to write that somewhere to keep a record of it.

Here is a short example:
DECLARE
  l_err_code VARCHAR2(512);
BEGIN
  FOR cur_r IN (SELECT id, surname FROM some_table)
  LOOP
    BEGIN          --> note this inner begin-end block! It will make possible
                   --  to continue the loop even though an error appears
      INSERT INTO another_table (id, surname)
        VALUES (cur_r.id, cur_r.surname);
    EXCEPTION
      WHEN OTHERS THEN
        l_err_code := sqlerrm;
        INSERT INTO error_log_table (id, err) VALUES (cur_r.id, l_err_code);
    END;
  END LOOP;
END;
Re: exception DUP_VAL_ON_INDEX [message #291666 is a reply to message #291664] Sat, 05 January 2008 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case better use BULK COLLECT and FORALL loop.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #291672 is a reply to message #291666] Sat, 05 January 2008 16:25 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
HI

Can you show me my example with using BULK COLLECT and FORALL loop ?
Re: exception DUP_VAL_ON_INDEX [message #291673 is a reply to message #291658] Sat, 05 January 2008 16:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are BOTH the search button on this forum & GOOGLE broken for you?
Re: exception DUP_VAL_ON_INDEX [message #291692 is a reply to message #291672] Sun, 06 January 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/index.htm

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #291702 is a reply to message #291692] Sun, 06 January 2008 02:48 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Will this work:

SQL> CREATE TABLE name_table(id NUMBER PRIMARY KEY, surname VARCHAR2(30));

Table created.

SQL> INSERT INTO name_table VALUES(123, 'aligator1234');

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE tomek
  2  AS
  3    v_id      name_table.id%TYPE      := '123';
  4    v_surname name_table.surname%TYPE := 'aligator1234';
  5  BEGIN
  6    INSERT INTO name_table (id, surname) VALUES (v_id, v_surname);
  7
  8    SELECT id, surname INTO v_id, v_surname FROM name_table;
  9  EXCEPTION
 10    WHEN dup_val_on_index THEN
 11       dbms_output.put_line('value ID=' || v_id || 'value SURNAME=' || v_surname);
 12  END tomek;
 13  /

Procedure created.

SQL> exec tomek
value ID=123value SURNAME=aligator1234

PL/SQL procedure successfully completed.
Re: exception DUP_VAL_ON_INDEX [message #291706 is a reply to message #291702] Sun, 06 January 2008 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this works if you have to insert only one row and this one is given by the parameters of your procedure.
Maybe this is OP is searching for as he didn't post the actual code but one that even doesn't compile.
LittleFoot and I assumed that there is a row source but maybe we are wrong, maybe it was just a syntax error. Good catch!

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #291716 is a reply to message #291692] Sun, 06 January 2008 04:07 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Thank you very much for your help. Tommorow I try implementation BULK COLLECT

bye bye
Re: exception DUP_VAL_ON_INDEX [message #291718 is a reply to message #291716] Sun, 06 January 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And please, right or wrong, post the code.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #291777 is a reply to message #291718] Sun, 06 January 2008 14:25 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Hi again,
I have one idea regarding my problem.
What do you think about using RETURNING ?


best regards
Tomasz
Re: exception DUP_VAL_ON_INDEX [message #291779 is a reply to message #291777] Sun, 06 January 2008 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If INSERT raises an error there is no returned value.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #292840 is a reply to message #291666] Thu, 10 January 2008 00:37 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Hi again ,

One question regarding using BULK COLLECT.

I must exception DUP_VAL_ON_INDEX. Is it possible with using BULK COLLECT ?


Best regards
Tomek
Re: exception DUP_VAL_ON_INDEX [message #292852 is a reply to message #292840] Thu, 10 January 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does mean "I must exception DUP_VAL_ON_INDEX"?

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #292894 is a reply to message #292852] Thu, 10 January 2008 02:29 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
I must using exception DUP_VAL_ON_INDEX. Below I description my sample for clear situation.

My task is insert data into table from external_table ( table and external table is in the same database).

My procedure is following:

Remark: Primary key in Normal_table is company column.

Procedure insert_from_external_table
is
cursor c_rec is
select name, first_name from external_table;

r_rec c_rec%rowtype;

begin

open c_rec;
loop
begin
fetch c_rec into r_rec
exit when c_rec%notfound

if r_rec.name = 'M' then
insert into NORMAL_table(name, company)
values(r_rec.name, r_rec.company);
else
insert into ERROR_table (name, company)
values(r_rec.name, r_rec.company);
end if;


exception
when DUP_VAL_ON_INDEX
insert into ERROR_table

end;
end loop;
close c_rec;

end insert_from_external_table


execution time this procedure for 1 million rows is about 40 minutes. it is too much. I would like less than 10 minutes.

Any idea ?

best regards
Tomek
Re: exception DUP_VAL_ON_INDEX [message #292904 is a reply to message #292894] Thu, 10 January 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Edit your post and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

You don't need a cursor loop, use an INSERT ALL with LOG ERRORS clause. See http://www.orafaq.com/forum/t/95390/102589/

Regards
Michel

[Updated on: Thu, 10 January 2008 02:39]

Report message to a moderator

Re: exception DUP_VAL_ON_INDEX [message #292909 is a reply to message #292904] Thu, 10 January 2008 02:54 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
LOG_ERRORS is impossible because I working for ORACLE 9.

I must using cursor.

Can you show me for my samples how I can using BULK COLLECT ?

Best regards
Tomek


Re: exception DUP_VAL_ON_INDEX [message #292910 is a reply to message #292909] Thu, 10 January 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

LOG_ERRORS is impossible because I working for ORACLE 9.

5 days, 19 answers and this is only now that you say that.
We will continue without me, I hate wasting my time.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #292913 is a reply to message #292904] Thu, 10 January 2008 03:00 Go to previous messageGo to next message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Sorry, that I ask about ready samples but I don't quite undestand BULK COLLECT.

IF you show me for my sample using BULK COLLECT i will be thankful.

Best regards
Re: exception DUP_VAL_ON_INDEX [message #292917 is a reply to message #292913] Thu, 10 January 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just take PL/SQL User's Guide and Reference, there are examples.

Regards
Michel
Re: exception DUP_VAL_ON_INDEX [message #292918 is a reply to message #292910] Thu, 10 January 2008 03:05 Go to previous message
aligator1234
Messages: 10
Registered: January 2008
Junior Member
Sorry, but regarding requiments (oracle 9) I get word yesterday.

Regards
Tomek
Previous Topic: SQL Query Help
Next Topic: sequence in procedure
Goto Forum:
  


Current Time: Sun Dec 04 20:51:43 CST 2016

Total time taken to generate the page: 0.07796 seconds