在java中使用nested_tables [英] using nested_tables in java

问题描述

如何从pl / sql过程的OUT参数中接收java中的嵌套表？这是我的示例代码。

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

但是当我尝试执行它时，我收到错误
PLS-00306：错误的数字或调用'procedure_name'的参数类型

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.

推荐答案

从OUT参数接收嵌套表的方式取决于表嵌套的方式考虑一个简单的数组类型

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

对象类型

CREATE OR REPLACE
TYPE HR.TNAME 
AS
OBJECT( NO1 NUMBER,
NAME VARCHAR2(10)
);

表格类型

CREATE OR REPLACE
TYPE HR.ITYPE_CUSTOM
IS TABLE OF tname;  

程序

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

这篇关于在java中使用nested_tables的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！