Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> pl/sql to insert 1000 rows then commit
chung
post Sep 7 2017, 08:14 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 17-January 14
Member No.: 49,285



Hi
I am new to oracle,
I have a task to insert 65000 new data into a table using pl/sql and the pl/sql only commit after inserting 1000 rows.
for now, I will be able to search and modify a pl/sql to make it works to insert each row then commit. but don't know how to commit after 1000 rows.
Here is my procedure pl/sql :

create or replace procedure sp_updt
(
v_LCTN_new varchar2,
v_ZIP varchar2

)
as
begin

INSERT INTO LCTN
SELECT v_LCTN_new, v_ZIP FROM DUAL;
commit;

end;

===================
and it be called like this with different new data to insert one by one call and commit each one:

infpdata.sp_updt_jrsdctn('ABQ','87001');
infpdata.sp_updt_jrsdctn('ABB','87003');
infpdata.sp_updt_jrsdctn('ABA','87004');
infpdata.sp_updt_jrsdctn('ABS','87002');

...
....
... up to 65000 rows to inserted

but if I only commit each 1000 rows then I don't know how.
I 'm thinking about change it to function pl/sql as below:

create or replace function sp_updt
(
v_LCTN_new varchar2,
v_ZIP varchar2,
count IN OUT NUMBER
)
return v_cnt NUMBER;
as
begin
v_cnt := count;

INSERT INTO LCTN
SELECT v_LCTN_new, v_ZIP FROM DUAL;

v_cnt = v_cnt + 1;

if (v_cnt = 1000) then
v_cnt = 0;
commit;
end if;

return v_cnt;

end;


and copy all the calls in another procedure as below:

create or replace procedure insert_all
begin
count := infpdata.sp_updt_jrsdctn('ABQ','87001', 0);
count := infpdata.sp_updt_jrsdctn('ABB','87003', count);
count := infpdata.sp_updt_jrsdctn('ABA','87004', count);
count := infpdata.sp_updt_jrsdctn('ABS','87002', count);
....

end;
then call the insert_all.
is it doable? or do you have another way to do it?

Please help.
Thank you in advance
Regards
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 7 2017, 01:12 PM
Post #2


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Chung,

There is a better way to do it,

Instead of writing the procedure call 65,000 times, you can call it once and loop it for 65,000 times as like below,

CODE
for i in (/*select statement with your data*/)
infpdata.sp_updt_jrsdctn(i.col1,i.col2);
end loop i;


In the above code, you can have a counter to commit if the loop is at its every 1000th insert.

Write back if you need anything else.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
chung
post Sep 7 2017, 08:23 PM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 17-January 14
Member No.: 49,285



Hi Boobal,
Thank you very much for giving me the answer promptly. But using this loop won't work because the new data were not in any database table yet.
so the
for i in (select * from tablename) //won't work.



regards

Go to the top of the page
 
+Quote Post
DanS
post Sep 8 2017, 08:04 AM
Post #4


Newbie
*

Group: Members
Posts: 4
Joined: 7-September 17
Member No.: 52,153



Try this:

CODE
---------------------------------------------
-- Create test table based on another table
---------------------------------------------
CREATE TABLE test_table AS SELECT * FROM all_tables WHERE 1=2;
--
---------------------------------------------
-- Copy records from real table to test table
-- 1000 records at a time.
---------------------------------------------

DECLARE
  CURSOR c1 IS
    SELECT *
      FROM all_tables;
  --      
  TYPE tbl_type IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
  tbl       tbl_type;
  n_counter NUMBER := 0;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT
      INTO tbl LIMIT 1000;  -- This is where you only deal with 1,000 records at a time
    --
    EXIT WHEN tbl.count = 0;
    n_counter := n_counter + tbl.count; -- just a counter for display later
    --
    FORALL i IN tbl.first .. tbl.count
      INSERT INTO test_table
      VALUES tbl(i);
    COMMIT; -- commit every 1,000 records
  END LOOP;
  COMMIT;
  dbms_output.put_line(n_counter || ' Record(s) inserted.');
END;

------------------------------------
-- Check out the records
------------------------------------
SELECT * FROM test_table;

------------------------------------
-- Clean up
------------------------------------
DROP TABLE test_table;
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:10 PM