Bulk insert instead of loop in PLSQL?

Question

I am trying to solve this piece of code with a bulk insert or something else more effective but I am out of ideas. How would you approach this problem. Instead of looping so many times i would like to do it more effective in few calls. Please let me know how you would do? With code if possible! Thanx

LOOP
-- Fetch a row
IF DBMS_SQL.FETCH_ROWS(cursor_handle) > 0 THEN
    DBMS_SQL.column_value(cursor_handle, 9, cont_id); 
    DBMS_SQL.COLUMN_VALUE(cursor_handle, 3, proj_nr);  
    HTP.BOLD('ContractID: ' || cont_id || ' ProjectNR: ' ||  proj_nr);
    HTP.BR;
ELSE
    EXIT;
END IF;

-- delete the old list before saving a new one
IF sek_nr = 1 THEN
  EXECUTE IMMEDIATE 'DELETE FROM W_Contracts WHERE user_id = :n' USING CURRENTUSER;
END IF;

EXECUTE IMMEDIATE 'Insert into W_Contracts values(''' || currentUser || ''', '
                   || sek_nr || ', sysdate, ' || cont_id || ', '''
                   || proj_nr || ''')';

sek_nr := sek_nr + 1;
END LOOP;

Justin Cave · Accepted Answer · 2012-01-17 19:40:17Z

8

First off, it's not clear to me why you're using dynamic SQL rather than static SQL.

IF sek_nr = 1 
THEN
  DELETE FROM w_contracts
   WHERE user_id = currentUser;
END IF;

INSERT INTO w_contracts( <<list of columns>> )
  VALUES( currentUser, sek_nr, sysdate, cont_id, proj_nr );

Next, it's not obvious to me why you're potentially doing a DELETE and then an INSERT if the SEK_NR is 1. It would likely be more efficient to do an UPDATE in that case. And once you're doing an UPDATE and an INSERT, you can simplify that into a single MERGE statement.

Next, if you use the DEFINE_ARRAY method in DBMS_SQL, you can do bulk fetches of data from your cursor. Of course, without seeing your cursor definition, I would be suspicious that it too was using dynamic SQL unnecessarily and that you could use a much simpler approach.

answered Jan 17, 2012 at 19:40

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Haris Over a year ago

Thanx for your answer Justin. You are right about the dynamic sql part. I'll try to explain the rest of the code. Since there is no guarantee that sek_nr = 1 I check this every time in my loop which is not the best solution i guess. But if sek_nr is 1 I want to delete a entry from w_contracts table only once. Thereafter I continue with my insert which I know is a performance issue. It would be more effective to solve this in some other way, like bulk insert or something else. Do you have any idea on how to accomplish this ? Thanks for your help!

Collectives™ on Stack Overflow

Bulk insert instead of loop in PLSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related