Java: Passing Array to Oracle Stored Procedure

mike says:

This is great article

sridhar says:

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 says:

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

Suthar J says:

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:

Anuj Parashar says:

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 says:

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

Joe says:

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

Bharat says:

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 says:

Nice article,,,

Rajesh says:

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 says:

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 says:

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

Prasad says:

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 says:

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 says:

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 says:

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.

dhanaraj says:

nice post

vidyasagar says:

use orai18n.jar to solve the issue of sending string array to pl/sql procedure

steller says:

If anyone wants to use the passed in array in the query try this. NOT IN(SELECT * FROM table(array_table));

anonymous says:

good article