InHow to use struct in a Java program to transmit multiple data records to Oracle at a timeThis section describes how to import multiple pieces of data to Oracle at a time by defining struct and array.
Step 1: define the object type.
Create type department_type as object (
DNO number (10 ),
Name varchar2 (50 ),
Location varchar2 (50)
);
Step 2: Define an array object of the object type.
Create type dept_array as table of department_type;
Step 3: Define a stored procedure to insert data.
Create or replace package objecttype
Procedure insert_object (d dept_array );
End objecttype;
Create or replace package body objecttype
As
Procedure insert_object (d dept_array)
As
Begin
For I in D. First... D. Last
Loop
Insert into department_teststruct
Values (d (I). DNO, d (I). Name, d (I). Location );
End loop;
End insert_object;
End objecttype;
If we need to update the dept_array type D, the following statement system will prompt an error.
Create or replace package objecttype
Procedure insert_object (d dept_array );
End objecttype;
Create or replace package body objecttype
As
Procedure insert_object (d dept_array)
As
Begin
For I in D. First... D. Last
Loop
D (I). Location: = 'new loc '| I;
Insert into department_teststruct
Values (d (I). DNO, d (I). Name, d (I). Location );
End loop;
End insert_object;
End objecttype;
Error message: PLS-00363: expression 'd. Location 'could not be used as an assignment target
The correct method is:
Create or replace package body objecttype
As
Procedure insert_object (d
In
OutDept_array)
Is
Begin
For I in 1 .. D. Count Loop
-- Same as for I in D. First... D. LastD (I). Location: = 'new loc '| I;
Insert into department_teststruct
Values (d (I). DNO, d (I). Name, d (I). Location );
End loop;
End insert_object;
End objecttype;
The key issue is that D must be of the output type (marked in red and bold in the Code ).
Reference: http://forums.oracle.com/forums/thread.jspa? Messageid = 2208830 & #2208830