oracle - Java: Send array to a PL-SQL function -

oracle - Java: Send array to a PL-SQL function -

i need pass array of objects pl-sql function

my oracle code:

create or replace type uu.itemtab table of itemrec; / create or replace type uu.itemrec object (id number, name varchar2(30)) /

java class

public class itemrec { private int id; private string name; public itemrec(int id, string name) { super(); id = id; name = name; } public int getid() { homecoming id; } public void setid(int id) { id = id; } public string getname() { homecoming name; } public void setname(string name) { name = name; } }

java code:

import java.sql.callablestatement; import java.sql.connection; import java.sql.drivermanager; import java.sql.sqlexception; import java.sql.types; import oracle.sql.array; import oracle.sql.arraydescriptor; public class testna { public static void main(string args[]) throws java.io.ioexception, sqlexception, classnotfoundexception { itemrec[] mya = new itemrec[1]; mya[0] = new itemrec(1, "bob"); class.forname("oracle.jdbc.driver.oracledriver"); connection con=drivermanager.getconnection("jdbc:oracle:thin:@localhost:1521:orcl","uu","uu"); arraydescriptor desc= arraydescriptor.createdescriptor("itemtab", con); array array = new array(desc, con, mya); system.out.println("connection created.............."); string phone call = "{ ? = phone call mainpackage.fgettext(?) }"; callablestatement cstmt = con.preparecall(call); cstmt.setquerytimeout(1800); cstmt.registeroutparameter(1, types.varchar); cstmt.setarray(2, array); cstmt.executeupdate(); string val = cstmt.getstring(1); cstmt.close(); con.close(); system.out.println(val); } }

note: here have 1 object, test.

the new error at:

array array = new array(desc, con, mya); exception in thread "main" java.sql.sqlexception: fail convert internal representation:

updated: have updated code,

thanx @codo

it's more complicated that. first, need array type in oracle. must global, i.e. not defined pl/sql package.

create or replace type num_array table of number;

then need create oracle specific array in java:

int intarray[] = { 1,2,3,4,5,6 }; arraydescriptor desc= arraydescriptor.createdescriptor("num_array", con); array array = new array(desc, con, intarray);

this can passed stored procedure:

oraclepreparedstatement stmt = (oraclepreparedstatement)conn.preparestatement("begin pkg.proc(:x); end;"); ps.setarray( 1, array_to_pass ); ps.execute();

i'm not quite sure how of import utilize oracle specific classes oracle.jdbc package. it's not possible work pure jdbc.

if provide more info array type (both on java , oracle side) signature of pl/sql procedure, give more specific advice.

updated:

obviously, you're not trying pass array table. i've never done , don't know whether it's possible or not.

java oracle stored-procedures