Java: Passing Array to Oracle Stored Procedure

By Vibhati on August 29, 2012

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, as oracle.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 -

an array of String as its IN parameter – p_array
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();
	}
}

Brief Explanations:

Class.forName() – Returns the Class object associated with the class or interface with the given string name.
DriverManager.getConnection() – Attempts to establish a connection to the given database URL.
oracle.sql.ArrayDescriptor – Describes an array class
ArrayDescriptor.createDescriptor() – Descriptor factory. Lookup the name in the database, and determine the characteristics of this array.
oracle.sql.ARRAY – An Oracle implementation for generic JDBC Array interface.
CallableStatement – The interface used to execute SQL stored procedures.

References

Get our Articles via Email. Enter your email address.

16 Comments

mike 20 November, 2012, 16:40

This is great article

sridhar 1 December, 2012, 10:12

Hi this is sridhar, I executed the above program and stored procedure but I am getting the malformed sql 92 exception I am not getting aware of where the error is whether it is from java side or oracle zide could you please help me thanks for all

Thanks & regards
Sridhar

sridhar 30 December, 2012, 12:19

Hi Viral,
I couldn’t find any reply from you, please help me regarding the above issue.

Suthar J 7 January, 2013, 19:35

I’m Try to execute the above code
i getting error while creating procedure:
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;

———————————
Error report:
ORA-01435: user does not exist
01435. 00000 – “user does not exist”
*Cause:
*Action:

kdd 6 February, 2013, 22:18
Try to remove “SchemaName.” from the script, this is only a placeholder.

Reply

Anuj Parashar 1 February, 2013, 15:09

Very nice article, Vibhati.
However, for this case, I won’t extend the nested table by 10. It should either be extended by p_array.COUNT or each time by 1 inside the loop. It would avoid returning null values.

Bharat 5 May, 2013, 1:30

Hi Thanx,
nice post,
I have tried it but it is showing null or blank. it never show correct data.

Joe 5 May, 2013, 20:51

Your example use array, can you give an example use ArrayList as input?

Viral Patel 10 May, 2013, 15:16
I’m afraid you cannot do that. You need to convert arraylist to array and then pass the array to stored procedure.

Reply

Bharat 6 May, 2013, 18:59

Hi Joe,
Object –

CREATE OR REPLACE type obj_employee AS  object  (
    EmpNo   INTEGER,
    EmpName VARCHAR(50),
    datet DATE);

Array -

create or replace  type Arr_Emp as Table of obj_employee;

Java class.

  public void ArrayListToOracleArray() {
        logger.info(dbSchemaName);
        try {
            ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("ARR_EMP", conn);
            obj_employee obj1 = new obj_employee("OBJ_EMPLOYEE", 1, "Bharat", new Date(new java.util.Date("03-MAY-13").getTime()));
            obj_employee obj2 = new obj_employee("OBJ_EMPLOYEE", 2, "Singh", new Date(new java.util.Date().getTime()));
            ArrayList ob = new ArrayList();
            ob.add(obj1);
            ob.add(obj2);
            ARRAY array = new ARRAY(arraydesc, conn, ob.toArray());
            CallableStatement cstm = conn.prepareCall("{ call SP_TEST_RESULT_ENT_SAVEDATA_V1(?,?) }");
            ((OracleCallableStatement) cstm).setARRAY(1, array);
            cstm.registerOutParameter(2, java.sql.Types.VARCHAR);
            cstm.execute();
            System.out.println(" Test " + cstm.getString(2));
            System.out.println("Please check database");
        } catch (Exception e) {
            System.err.println("dothis method exception: " + e.getMessage());
        }
    }

Employee java class

public class obj_employee implements SQLData, Serializable {

    private String sql_type;
    private int EmpNo;
    private String EmpName;
    private Date date;

    public obj_employee(String sql_type, int EmpNo, String EmpName, Date date) {
        this.sql_type = sql_type;
        this.EmpNo = EmpNo;
        this.EmpName = EmpName;
        this.date = date;
    }

    public String getSQLTypeName() throws SQLException {
        return sql_type;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.sql_type = typeName;
        this.EmpNo = stream.readInt();
        this.EmpName = stream.readString();
        this.date = stream.readDate();
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeInt(EmpNo);
        //stream.writeBinaryStream(new BufferedInputStream(new ByteArrayInputStream("bharat".getBytes())));
        stream.writeString("Bhaart");
        stream.writeDate((java.sql.Date) this.date);
        System.out.println(" This Name : " + this.EmpName);
    }

.i am facing a problem. EmpName is showing blank in DB. but other field is woking fine.

maitrey 14 May, 2013, 17:36

Nice article,,,

Rajesh 8 July, 2013, 22:58

Hi,

Is there any way to bind package level associative arrays in java code with hash map.

Please help.
I am trying to do the following.
create or replace
package hr_pkg
as
type charArray is table of varchar2(255) index by varchar2(10);
end;

–Java–
Map test = new HashMap();
test.put(“PLAN”, “2012″);
test.put(“ORDER”, “9999″);
ArrayDescriptor des = ArrayDescriptor.createDescriptor(“hr_pkg.charArray”, con);
ARRAY array_to_pass = new ARRAY(des,con,test);

When i execute this, i recieve

java.sql.SQLException: invalid name pattern: hr_pkg.charArray.

Any work around for this?

Thanks

Tomas 13 September, 2013, 14:14

Thanks for this post, I’m trying to make this work, but I’m not able to succeed

here is my example code – modified from your article:

public static void passArray()
    {
        try{
         
            Class.forName("oracle.jdbc.OracleDriver");         
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@IP:PORT:SYS","USER","PASS");
             
            String array1[] = {"one", "two", "three","four"};
            String array2[] = {"four", "three", "two","one"};

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("MY_SCHEME.STRING_ARRAY", con);
            ARRAY array1_to_pass = new ARRAY(des,con,array1);
            ARRAY array2_to_pass = new ARRAY(des,con,array2);
            
            System.out.println(array1_to_pass.dump());
            System.out.println(array2_to_pass.dump());

            CallableStatement st = con.prepareCall("call MY_SCHEME.FORMCREATE(?, ?, ?, ?, ?, ?, ?, ?, ?)");
 
            String p1 = "1";
            String p3 = "2";
            String p4 = "3";            
            
            st.setObject(1, p1);
            st.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
            st.setObject(3, p3);
            st.setObject(4, p4);
            st.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);            
            st.setObject(5, array1_to_pass);
            st.setObject(6, array2_to_pass);
            st.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
            st.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);
            st.registerOutParameter(9, oracle.jdbc.OracleTypes.NUMBER);
            st.execute();

        } catch(Exception e) {
            System.out.println(e);
        }
    }

oracle type definition is like this:

create or replace type MY_SCHEME.STRING_ARRAY is TABLE of VARCHAR2(200);

oracle proedure:

create or replace 
PROCEDURE       formcreate(
  id                 IN VARCHAR2,
  pid              OUT VARCHAR2,
  lid                IN VARCHAR2,
  fid                IN OUT VARCHAR2,
  iid                IN MY_SCHEME.STRING_ARRAY,
  values        IN MY_SCHEME.STRING_ARRAY,
  items         OUT SYS_REFCURSOR, 
  scode        OUT NUMBER,
  ecode        OUT NUMBER
  )
IS
--...
BEGIN
  ecode := 100;
  scode:= 1;
  pid:=10;
  
  FOR i IN 1 .. iid.COUNT
   LOOP
      log.p_error('A' || iid(i)); //package "log", function INSERTs into log table...
   END LOOP;
   
   FOR i IN 1 .. values.COUNT
   LOOP
      log.p_error('B'|| values(i));//package "log", function INSERTs into log table...
   END LOOP;

  COMMIT;
END;

my problem:

application output looks like this:

name = MY_SCHEME.STRING_ARRAY
max length = 0
length = 4
element[0] = ???
element[1] = ???
element[2] = ???
element[3] = ???

name = MY_SCHEME.STRING_ARRAY
max length = 0
length = 4
element[0] = ???
element[1] = ???
element[2] = ???
element[3] = ???

database log table contains 8 new rows:

A
A
A
A
B
B
B
B

I really don’t understand – when I change String array to int[], (and database type to “table of number”), everything works just fine (log sql table contains A1, A2, A3, A4, B1, B2, B3, B4)… but it is impossible to for me to make it work with string

any ideas why is it so?

my oracle driver is ojdbc6-11.2.0.4.0, database version Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production… every example I have found on the internet claims it works just fine – I really don’t understand

Tomas 16 September, 2013, 20:31

reply to my previous comment: including orai18n.jar should solve the issue – it helped in my case…

Prasad 5 October, 2013, 13:05

Hi Viral,

I have a requirement to pass a null Array to a stored procedure in java using callableStatement.setArray(int, ARRAY). Could you please let me know if it is possible?

- Prasad B

jerome 9 October, 2013, 7:40

hi. i have a trouble when accessing the array in the stored procedure. i keep getting null values.
Any workaround on this bug. thank you

Java: Passing Array to Oracle Stored Procedure

Database Code

Java Code

Brief Explanations:

References

16 Comments

Leave a Reply Cancel reply

Subscribe

Sponsors

Links

Categories

Site Pages

Java: Passing Array to Oracle Stored Procedure

Database Code

Java Code

Brief Explanations:

References

Related Posts

16 Comments

Leave a Reply Cancel reply

Subscribe

Sponsors

Links

Categories

Site Pages