Home » SQL & PL/SQL » SQL & PL/SQL » Getting erroneous record from Bulk insert
Getting erroneous record from Bulk insert [message #190782] Fri, 01 September 2006 03:49 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a cursor:
cursor cur is
  select TabA.* 
    from TabA, TabB
    where TabA.x = TabB.w;

Then insert the cursor's rows to TabC like:
  for cur_rec in cur
  loop
  
     begin
 
       insert into TabC
       (id, a, b, c)
       values 
       (my_seq.nextval, cur_rec.x, cur_rec.y, cur_rec.z);  
       commit;
 
     exception
       when others then 
          dbms_output.put_line('Error with record ' || cur_rec.x); 
          rollback;
     end;

  end loop;
  ...

This is taking a long time. Is it possible to have it in bulk:
       insert into TabC
       (id, a, b, c)
       select my_seq.nextval, x, y, z
         from TabA, TabB
        where TabA.x = TabB.w;

And when an error comes, I can still display 'which record' caused the error???

THanks in advance..

[Updated on: Fri, 01 September 2006 03:51]

Report message to a moderator

Re: Getting erroneous record from Bulk insert [message #190786 is a reply to message #190782] Fri, 01 September 2006 04:07 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look here, might clear some things out for you.
Dont know if it's going to solve your problem about taking a looong time. Might be outher isssues......

http://asktom.oracle.com/pls/ask/f?p=4950:8:9883910269054101775::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8912264456901
Re: Getting erroneous record from Bulk insert [message #190811 is a reply to message #190786] Fri, 01 September 2006 06:41 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, it was helpful. THe only thing is that there's a problem in the Bulk-in-Bind variable.
  
declare

  type a_tab is table of TabA%rowtype;
 
  my_tab a_tab;

begin
  ...
  select TabA.* 
    bulk collect into my_tab
    from TabA, TabB
    where TabA.x = TabB.w;
  ...
  forall i in 1..my_tab.count save exceptions

    insert into TabC
    values 
     (my_seq.nextval, my_tab(i));  
 
 ...

I can get the erroneous record using sql%bulk_exceptions. However, I am having problem with the sequence. How do I put the my_seq.nextval as part of the my_tab since the above syntax does not work.
Please help Smile
Re: Getting erroneous record from Bulk insert [message #190824 is a reply to message #190782] Fri, 01 September 2006 07:47 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hmm shouldnt be any problems, done a short example.
Someone is probably gonna complain about me not using the rowtype functionallity, but the question was about sequence, so you can easily produces this with nicer syntax.

Table A and B has 2 columns
id(number),name(varchar2)
Table C has 3 columns
id2(for the sequence),id(number),name(varchar2)


CREATE OR REPLACE PROCEDURE test8
IS
TYPE x_record IS RECORD (
ID DBMS_SQL.number_table,
NAME DBMS_SQL.varchar2_table );

p_rec x_record;

CURSOR c
IS SELECT a.ID AS ID, a.NAME AS NAME
FROM tablea a, tableb b
WHERE a.ID = b.ID;

l_done BOOLEAN;
BEGIN
OPEN c;

LOOP
FETCH c
BULK COLLECT INTO p_rec.ID, p_rec.NAME;

l_done := c%NOTFOUND;

FORALL i IN 1 .. p_rec.ID.COUNT SAVE EXCEPTIONS
INSERT INTO tablec
VALUES (my_seq.NEXTVAL, p_rec.ID (i), p_rec.NAME (i));
EXIT WHEN (l_done);
END LOOP;

CLOSE c;

END test8;
/


Best of Luck

Re: Getting erroneous record from Bulk insert [message #190861 is a reply to message #190824] Fri, 01 September 2006 10:40 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks! I should have thought more... Smile
Re: Getting erroneous record from Bulk insert [message #190973 is a reply to message #190861] Sun, 03 September 2006 12:30 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Best of luck m8 :=)
Previous Topic: How to find Open Cursors
Next Topic: Records/Bulk Collects Problem
Goto Forum:
  


Current Time: Fri Dec 02 20:28:53 CST 2016

Total time taken to generate the page: 0.07710 seconds