Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to insert 300 data from associative array to backend table in PL/SQL

986043Jan 21 2013 — edited Jan 23 2013
HI 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 20 2013
Added on Jan 21 2013
11 comments
2,264 views