This discussion is archived
6 Replies Latest reply: Jan 22, 2013 8:40 PM by Purvesh K RSS

Procedure for Insert to BULK COLLECT

AK111 Newbie
Currently Being Moderated
hi,
I have 2 questions-

1) Say I have below code. I want to call an insert procedure insead of INSERT INTO. If I do would it give any performance issue?

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

FORALL i IN 1..l_data.COUNT
INSERT INTO t1 VALUES l_data(i);

EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END test_proc;
/

CREATE OR REPLACE PROCEDURE insert_proc ( col1 table.col1%Type,
col2 table.col2%Type,
.
.
.
col20 table.col20%Type)
BEGIN
INSERT INTO HistoryTable (col1, col2, ...col20)
VALUES(val1, val2, ...val 20);
END;

END;

2) Is there any clean method to create insert procedure which has 20 columns which I can call in other proc to do bulk insert?
  • 1. Re: Procedure for Insert to BULK COLLECT
    sb92075 Guru
    Currently Being Moderated
    AK111 wrote:
    hi,
    I have 2 questions-

    1) Say I have below code. I want to call an insert procedure insead of INSERT INTO. If I do would it give any performance issue?
    yes, the PL/SQL code will run slower than a single INSERT INTO SELECT * FROM statement
  • 2. Re: Procedure for Insert to BULK COLLECT
    AK111 Newbie
    Currently Being Moderated
    I have 7 procedures where I have to insert into this history table. I would like to use bulk collect as well as modularize the code by creating insert procedure to insert into History table. Do you have any other suggestion?
  • 3. Re: Procedure for Insert to BULK COLLECT
    rp0428 Guru
    Currently Being Moderated
    >
    I have 7 procedures where I have to insert into this history table. I would like to use bulk collect as well as modularize the code by creating insert procedure to insert into History table. Do you have any other suggestion?
    >
    Just use the suggestion SB already provided: a single INSERT INTO SELECT * FROM statement'

    In your procedure just use a static SQL statement like that rather than bulk dynamic sql.

    You haven't shown anything that can't be done with plain SQL.
  • 4. Re: Procedure for Insert to BULK COLLECT
    sb92075 Guru
    Currently Being Moderated
    AK111 wrote:
    I have 7 procedures where I have to insert into this history table.
    Some, many, most well designed applications have a single procedure that does DML against single table.
    I would like to use bulk collect as well as modularize the code by creating insert procedure to insert into History table. Do you have any other suggestion?
    Some, many, most well designed applications use TRIGGER to populate History table
    & bulk collect is wrong tool for the task.
  • 5. Re: Procedure for Insert to BULK COLLECT
    AK111 Newbie
    Currently Being Moderated
    I agee. As per best practice, history table is populated by trigger. I am glad we have such code in place which works absolutely fine.

    What I am trying to accomplish is bit different here. I have to store some balances (as in intrim balances) before aggregating and sending it to destination table.

    I am trying to achive something similar to - transfering money from checking to savings account.

    1) Read balance from Checking Account (Source table) (eg $500 amount to be transfer)
    2) Insert those records into TransferHistory table (Interim table) (- $500 entry in the intrim table) - source amounts can be changed in future therefore we want to save intrim records (point in time information)
    3) Aggregate balances per business rule and store final amount with negative sign in Checking (Source table) ( - $500 entry in source table)
    4) Aggregate balances per business rule and store final amount in Savings account (Destination table) ( + $500 entry in destination table)

    There are 7 such tables I have to deal the amounts in (with multiple amounts columns). Person can either transfer full, percentage, or custom amounts. So there is lot of business logic to run before I put the info in history table. Reports will be run on this table.

    Simple and straightforward way to implement is-
    1) create one procedure - which has INSERT for History table
    2) create a procedure which will use cursor and read the amount from source table
    3) call the Insert proc which inserts into source, history(intrim) and destination tables

    There will be lot of records users can select and transfer therefore I am trying to develop a solution which would not be a performance hit.

    Please suggest if you have any efficient way to recommend.
  • 6. Re: Procedure for Insert to BULK COLLECT
    Purvesh K Guru
    Currently Being Moderated
    It is good that you explained your requirements, but you did not give us some data to see with and work with.

    If you could, help us with below details, it might be possible to help you:
    1. Create table statements for your Tables (eg. Checking, Savings and history)
    2. Insert Into statements for Sample data for your Tables.
    3. validations that you need to perform
    4. Expected output based on the Sample data provided in step 2.


    Please do not forget to post your version number
    select * from v$version;
    Also, use {noformat}
    {noformat} tags, before and after SQL Statements, Expected Output to preserve spaces and make the post more readable.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points