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, 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 -

  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();
	}
}

Brief Explanations:

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

References



18 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

    Hi

    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.

  • 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?

  • 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

  • vnraju 11 March, 2014, 13:38

    Hi,

    I gone through your article and tried to execute but i couldn’t able to finish sucessfully….
    i am facing some problems like….
    1. At database level
    Permission issue…. which allows me to create synonyms…
    2. I am unable to invoke procedure from JAVA program mentioned above …. i am gettting following error :
    ORA-01775: looping chain of synonyms
    ORA-06512: at “SYS.DBMS_PICKLER”, line 18
    ORA-06512: at “SYS.DBMS_PICKLER”, line 58
    ORA-06512: at line 1

    Can you suggest some solution…. :-)

  • Techbuzz 25 June, 2014, 17:55

    Hi Viral,

    I’m using same as above mentioned in your example but i’m facing issue in Oracle 9i

    p_arr_int := NEW array_int (); — ORA-00103

    Can you please help with alternate solution to it.

    Actually from cursor i’m storing filtered data in PLSQL table which i’m returning is OUT Parameter.

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current day month ye@r *