Java: Passing Array to Oracle Stored Procedure

This tutorial guides us on how to pass Array objects from Java to stored procedures in Oracle and also, how to retrieve an array object in Java.

All PLSQL arrays can not be called from java. An array needs to be created as TYPE, at SCHEMA level in the database and then it can be used with ArrayDescriptor in Java, asoracle.sql.ArrayDescriptor class in Java can not access at package level.

Database Code

First, Create an array, at SCHEMA level. An example is shown below:

CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);-- Array of String

 

CREATE TYPE array_int AS TABLE OF NUMBER;         -- Array of integers

Next, Create a procedure which takes an array as an input parameter and returns an array as its OUT parameter.

An example of one such procedure is shown below, which has 2 parameters -

1. an array of String as its IN parameter – p_array
2. an array of Integers as OUT parameter – p_arr_int

CREATE OR REPLACE PROCEDURE SchemaName.proc1 (p_array     IN     array_table,                                              len            OUT NUMBER,                                              p_arr_int      OUT array_int)AS   v_count   NUMBER;BEGIN   p_arr_int := NEW array_int ();   p_arr_int.EXTEND (10);   len := p_array.COUNT;   v_count := 0;    FOR i IN 1 .. p_array.COUNT   LOOP      DBMS_OUTPUT.put_line (p_array (i));      p_arr_int (i) := v_count;      v_count := v_count + 1;   END LOOP;END;/

After this, Execution permission would be required to execute the procedure created by you:

GRANT EXECUTE ON SchemaNAme.proc1 TO UserName;

Java Code

Create a java class which makes a call to the procedure proc1, created before.

Below is an example which contains the whole flow from creating a connection with the database, to making a call to the stored procedure, passing an array to Oracle procedure, retrieving an array from an Oracle procedure and displaying the result.

import java.math.BigDecimal;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.internal.OracleTypes;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor; public class TestDatabase {         public static void passArray()    {        try{                     Class.forName("oracle.jdbc.OracleDriver");                     Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","UserName","Password");;                         String array[] = {"one", "two", "three","four"};                         ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con);            ARRAY array_to_pass = new ARRAY(des,con,array);                         CallableStatement st = con.prepareCall("call SchemaName.proc1(?,?,?)");             // Passing an array to the procedure -            st.setArray(1, array_to_pass);             st.registerOutParameter(2, Types.INTEGER);            st.registerOutParameter(3,OracleTypes.ARRAY,"SchemaName.ARRAY_INT");            st.execute();                         System.out.println("size : "+st.getInt(2));             // Retrieving array from the resultset of the procedure after execution -            ARRAY arr = ((OracleCallableStatement)st).getARRAY(3);             BigDecimal[] recievedArray = (BigDecimal[])(arr.getArray());             for(int i=0;i<recievedArray.length;i++)                System.out.println("element" + i + ":" + recievedArray[i] + "\n");                     } catch(Exception e) {            System.out.println(e);        }    }     public static void main(String args[]){        passArray();    }}

补充：数组，对象等必须要在TYPE里面定义。

文章来自：http://viralpatel.net/blogs/java-passing-array-to-oracle-stored-procedure/