How to modify collection type variables in Oracle.

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