I am working on a project where the lead wants every 5000 records of an insert into a new table logged into a logging table.
I originally did not know this was needed functionality, so all I did in my proc to load the new table was
--METHOD 1
INSERT INTO new_table (column1, column2, column3)
(SELECT col1, col2, col3
FROM old_table);
So now, I have this. This is just example code. Instead of outputting to screen in my real code, I am calling an inhouse logging procedure. I can't test it right now b/c my work oracle is down for some reason. I am guessing that the straight insert / select is faster than fetching the values into a cursor and then loading them into the new table.
I need a good way to argue that Method1 is better. I can't think of a way to check the insert count while it is in proces that way the logging per 5000 recs is captured as well.
/************* METHOD2 ************************/
DECLARE
TYPE r_cursor IS REF CURSOR;
c_example_tab r_cursor;
example_tab_rec example_tab%ROWTYPE;
v_count INTEGER := 0;
v_string VARCHAR2 (1500);
TYPE example_tab_aarray IS TABLE OF new_table%ROWTYPE
INDEX BY PLS_INTEGER;
new_table_tab example_tab_aarray;
BEGIN
v_string :=
' BEGIN INSERT INTO new_table(column1, column2, column3 )
VALUES (:c1,:c2, :c3); END;';
OPEN c_example_tab FOR
SELECT col1, col2, col3
FROM old_table;
LOOP
FETCH c_example_tab
BULK COLLECT INTO new_table_tab;
EXIT WHEN c_example_tab%NOTFOUND;
END LOOP;
FOR i IN 1 .. new_table_tab.COUNT
LOOP
EXECUTE IMMEDIATE v_string
USING new_table (i).column1,
new_table (i).column2,
new_table (i).column3;
IF MOD (i, 5000) = 0
THEN
DBMS_OUTPUT.put_line ('Next 5000 inserted');
END IF;
END LOOP;
COMMIT;
CLOSE c_example_tab;
END;
Start Free Trial
