How to insert 300 data from associative array to backend table in PL/SQL
986043Jan 21 2013 — edited Jan 23 2013HI ALL,
I'm posting my code here:
Creating back end table:
Create table orlando
( id number(20),
calltype number(12),
gateway_name varchar2(25),
accounting_id varchar2(18),
start_time_system_ticks number(11),
node_time_zone varchar2(25),
start_date varchar2(10),
start_time varchar2(10),
softswitch_response number(11),
alerting number(11)
);
Creating package:
CREATE OR REPLACE PACKAGE r IS
type apollo_rec is record(
id number(20),
calltype number(12),
gateway_name varchar2(25),
accounting_id varchar2(18),
start_time_system_ticks number(11),
node_time_zone varchar2(25),
start_date varchar2(10),
start_time varchar2(10),
softswitch_response number(11),
alerting number(11)
);
TYPE bin_array IS TABLE OF apollo_rec INDEX BY BINARY_INTEGER;
PROCEDURE rr (state_array bin_array);
END ;
SET SERVEROUT ON
CREATE OR REPLACE PACKAGE BODY r IS
PROCEDURE rr (state_array bin_array) IS
BEGIN
FOR i IN 1 .. state_array.COUNT LOOP
INSERT INTO orlando(id,calltype,gateway_name,accounting_id,start_time_system_ticks)VALUES(state_array(i).id,state_array(i).calltype,state_array(i).gateway_name,
state_array(i).accounting_id,state_array(i).start_time_system_ticks);
COMMIT;
END LOOP;
END ;
END ;
/
I've run this code in i*SQL PLUS.But when I run this code for 5 entries there is no error but when I modify the insert statement for 300 entries(300 identifiers in the insert statement)
it gives me error:
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY R:
LINE/COL ERROR
7/2 PL/SQL: SQL Statement ignored
7/14 PL/SQL: ORA-00913: too many values
Is there any feature in PL/SQL to decrease the entries in insert statement and make the insert statement along with the program small and increase the program performance.
Edited by: 983040 on Jan 20, 2013 11:11 PM