In Java, arraylist is used to pass in the stored procedure with array as the parameter in Oracle.

During the writing and storage process, we often encounter converting the arraylist in Java into an array in Oracle. (The arraylist contains some Java objects.
For example, some people objects exist in arraylist.
1. First, you must create corresponding Java objects and arrays in the database,
For example:/* database objects corresponding to Java objects */

1. Create type peopleoracleobject as object
2. (
3. Peopleoracleid number (8 ),
4. Nameoracle varchar (50 ),
5. Ageoracle number (3)
6. )

/Array in the database

1. Create type people_oracle_list as varray (500) of peopleoracle;

/

2. Convert arraylist in Java

1. Private Static array getoraclearray (connection con, string oraclelist,
2. Arraylist objlist) throws exception {
3. Array list = NULL;
4. If (objlist! = NULL & objlist. Size ()> 0 ){
5. Structdescriptor structdesc = new structdescriptor (
6. "Peopleoracleobject", con );
7. Struct [] structs = new struct [objlist. Size ()];
8. Object [] result = new object [0];
9. For (INT I = 0; I <objlist. Size (); I ++ ){
10. Result = new object [2];
11. Result [0] = new long (people) (objlist. Get (I). getpeopleid ());
12. Result [1] = new long (people) (objlist. Get (I). getpeoplename ());
13. Result [2] = new long (people) (objlist. Get (I). getpeopleage ());
14. Structs [I] = new struct (structdesc, Con, result );
15. }
16. Arraydescriptor DESC = arraydescriptor. createdescriptor (oraclelist,
17. Con );
18. List = new array (DESC, Con, structs );
19. } Else {
20. Arraydescriptor DESC = arraydescriptor. createdescriptor (oraclelist,
21. Con );
22. Struct [] structs = new struct [0];
23. List = new array (DESC, Con, structs );
24. }
25. Return list;
26. } // Function

3./* Add the converted array to the stored procedure */

1. Public static int updateadinfo (arraylist peolelist, int ID ){
2. Connection con = NULL;
3. Callablestatement stmt = NULL;
4. Int backval;
5. Try {
6. Con = pool. getconnection ();
7. If (con! = NULL ){
8. Stmt = con. preparecall ("{call updatepeople (?,?,?)} ");
9. Array adarray = getoraclearray (con, "people_oracle_list ",
10. Peolelist );
11. (Oraclecallablestatement) stmt). setarray (1, adarray );
12. Stmt. setint (2, ID );
13. Stmt. registeroutparameter (3, java. SQL. types. integer );
14. Stmt.exe cute ();
15. } Else {
16. Backval = 1;
17. }
18. } Catch (exception e ){
19. E. printstacktrace ();
20. } Finally {
21. Pool. freedbresource (con, stmt, null );
22. }
23. Return backval;
24. }

4. Calling during stored procedures

1. Create or replace procedure nad_sp_createwebcosttoad (
2. P_peoplearray in people_oracle_list,
3. P_id in number,
4. P_out out varchar2
5. )
6. As
7. Vpeopleid number (8): = 0;
8. Vpeoplename varchar (50): = 0;
9. Vage number (3): = 0;
10. Begin
11. For I 1... p_peoplearray.count Loop
12. Leleobj: = p_peoplearray (I );
13. Vpeopleid: = peopleobj. peopleoracleid;
14. Vpeoplename: = leleobj. nameoracle;
15. Vage: = leleobj. ageoracle;
17. .................
19. End loop;
20. Commit;
21. Exception
22. When others then
23. P_out: = '-1' | sqlerrm;
24. Rollback;
25. End;
27. /
28. Show errors