java nested,在java中使用nested_tables

How do I receive a nested table in java from pl/sql procedure's OUT parameter? Here is my example code.

Connection connection = utilities.getConnectionToDb();

CallableStatement callableStatement = connection.prepareCall("{call procedure_name(?,?)}");

callableStatement.setLong(1, 23456);

callableStatement.registerOutParameter(2, Types.ARRAY);

callableStatement.executeQuery();

But when I try executing it I get the error

PLS-00306: wrong number or types of arguments in call to 'procedure_name'

I am sure that the number of arguments are correct.

解决方案

The way a nested table is recieved from an OUT parameter depends on how the table is nested consider a simple array type

object type

CREATE OR REPLACE

TYPE HR.TNAME

AS

OBJECT( NO1 NUMBER,

NAME VARCHAR2(10)

);

table type

CREATE OR REPLACE

TYPE HR.ITYPE_CUSTOM

IS TABLE OF tname;

Procedure

CREATE OR REPLACE PROCEDURE HR.p_schema_level_out(IN1 IN varchar2,p_det OUT itype_custom)

AS

lc_var itype_custom := itype_custom();

BEGIN

lc_var.extend;

lc_var(1) := TNAME(NO1 => 1,NAME => 'TRAIL1');

lc_var(1).no1 := 1;

lc_var(1).name := 'qwe';

p_det:= lc_var;

END;

/

The above procedure which returns a custom array type can be handled by

try {

stmt = con.createStatement();

// -----------------------------------------------------

// Call PL/SQL Procedure

// -----------------------------------------------------

String s1 = "begin p_schema_level_out(?,?); end;";

cstmt = (OracleCallableStatement) con.prepareCall(s1);

cstmt.setString(1, "something");

cstmt.registerOutParameter(2, Types.ARRAY, "ITYPE_CUSTOM");

cstmt.execute();

Object[] data = (Object[]) ((Array) cstmt.getObject(2)).getArray();

for (Object tmp : data) {

STRUCT row = (STRUCT) tmp;

for (Object attribute : row.getAttributes()) {

System.out.println(attribute);

}

cstmt.close();

stmt.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

This works as a base on how you can recieve a table type i could still help you through if you can post your nested table type