oracle Java：将数组发送到 PL-SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3819825/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java: Send array to a PL-SQL function
提问by Adnan
I need to pass an array of objects to a PL-SQL function
我需要将一组对象传递给 PL-SQL 函数
My ORACLE code:
我的甲骨文代码：
CREATE OR REPLACE
TYPE
uu.ITEMTAB AS TABLE OF ITEMREC;
/
CREATE OR REPLACE
TYPE
uu.ITEMREC AS OBJECT (ID NUMBER,
NAME VARCHAR2(30))
/
Java class
Java类
public class ITEMREC {
private int ID;
private String NAME;
public ITEMREC(int iD, String nAME) {
super();
ID = iD;
NAME = nAME;
}
public int getID() {
return ID;
}
public void setID(int iD) {
ID = iD;
}
public String getNAME() {
return 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 call = "{ ? = 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 I have only one object, as this is just for test.
注意：这里我只有一个对象，因为这只是为了测试。
The new error I get is at:
我得到的新错误是：
ARRAY array = new ARRAY(desc, con, myA);
Exception in thread "main" java.sql.SQLException: Fail to convert to internal representation:
UPDATED: I have updated my code,
更新：我已经更新了我的代码，
Thanx @Codo
谢谢@Codo
回答by Codo
It's slightly more complicated than that. First, you need an array type in Oracle. It must be global, i.e. not defined with an PL/SQL package.
它比那稍微复杂一些。首先，您需要在 Oracle 中使用数组类型。它必须是全局的，即不是用 PL/SQL 包定义的。
create or replace type NUM_ARRAY as table of number;
Then you need to create an Oracle specific array in Java:
然后你需要在 Java 中创建一个 Oracle 特定的数组：
int intArray[] = { 1,2,3,4,5,6 };
ArrayDescriptor desc= ArrayDescriptor.createDescriptor("NUM_ARRAY", con);
ARRAY array = new ARRAY(desc, con, intArray);
This can now be passed to the 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 important it is to use the Oracle specific classes from the oracle.jdbcpackage. But it's certainly not possible to work with pure JDBC.
我不太确定使用oracle.jdbc包中的 Oracle 特定类有多重要。但是使用纯 JDBC 肯定是不可能的。
If you provide more information about your array type (both on the Java and the Oracle side) as well as the signature of the PL/SQL procedure, I could give you more specific advice.
如果您提供有关数组类型（Java 和 Oracle 端）以及 PL/SQL 过程签名的更多信息，我可以为您提供更具体的建议。
Updated:
更新：
Obviously, you're not trying to pass an array but a table. I've never done that and I don't know whether it's possible or not.
显然，您不是要传递数组，而是要传递表。我从来没有这样做过，我不知道这是否可能。
