PL/SQL- How to insert into a table using all columns of a cursor

Question

I am using cursors to insert data in a table because if a record fails I only want that record to be discarded and keep inserting the rest.

So I am using a cursor to retrieve the information.

Is there any way to insert all columns of a cursor at once and not selecting them one by one?

 cursor c1 is 
select a,b,c,d,e from ab where a = 'something';

begin
for var_c1 in c1  loop 
begin

insert into ba (a,b,c,d,e) 
values (var_c1.all);

-- instead of values (var_c1.a, var_c1.b, var_c1.c,var_c1.d, var_c1.e) 
exception when others then continue;
end;

end;

I would suggest you to do some reading and that would certainly help you solve your probelm yourself. Read this oracle.com/technetwork/issue-archive/2012/12-sep/… — XING
– XING, Commented Oct 20, 2017 at 8:00

J. Chomel · Accepted Answer · 2017-10-20 07:45:29Z

For performance you should put all your records into a collection, then you can use BULK INSERT... SAVE EXCEPTION like below:

DECLARE
  TYPE t_tab IS TABLE OF ba%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.  ***
  -- l_tab  <--- ??
  -- *** next question is to make l_tab fill with the result of your cursor    
  --
  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO ba
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

Hope it helps

Collectives™ on Stack Overflow

PL/SQL- How to insert into a table using all columns of a cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related