从Java访问包内部定义的类型 [英] Accessing types defined inside package from java

问题描述

我在oracle中的过程是在一个程序包下定义的，该程序包具有记录表的入和出参数. 我可以使用java中的callable语句，使用packagename.procname来调用该过程.但是，要访问out参数，我需要定义一个ARRAY类型的变量.但是oracle中的记录类型和记录表是在包中定义的.因此，无法使用ARRAYDESCRIPTOR从Java中访问这些类型.

My procedure in oracle is defined under a package, which has in and out parameters of table of records. I can call the procedure using packagename.procname using the callable statement from java. However to access the out parameter, I need to define a variable of type ARRAY. But the types of record and table of records in oracle are defined inside the package. Thus those types are not accessible from java using the ARRAYDESCRIPTOR.

proc和类型定义如下:

The proc and types are defined as below:

CREATE OR REPLACE PACKAGE mypackage IS
TYPE TY_Pos IS RECORD
(        cust_id     VARCHAR2(9)

,        balance NUMBER
);

TYPE TY_TBL IS TABLE OF TY_Pos INDEX BY PLS_INTEGER;

PROCEDURE myproc(inTable IN OUT     TY_TBL,
                                      count IN NUMBER,
                                      outTable         IN OUT     TY_TBL
                                      );

CREATE OR REPLACE PACKAGE BODY mypackage AS

PROCEDURE myproc(inTable IN OUT     TY_TBL,
                                      count IN NUMBER,
                                      outTable         IN OUT     TY_TBL
                                      ) as 
--proc body

要访问proc的out变量(它是一个记录表)，我将创建数组描述符为

For accessing the out variable of proc which is a table of records, I am creating array descriptor as

ArrayDescriptor myDescp = ArrayDescriptor.createDescriptor ("TY_TBL", l_con);   

但是由于TY_TBL是在包内部定义的，因此会引发错误.请帮助我如何从我的Java代码访问此类型.

But since the TY_TBL is defined inside the package thus it throws error. Please help me how can I access this type from my java code.

推荐答案

这不是一个很干净的解决方案，但它可以正常工作

it's not a very clean solution, but it works

        StringBuilder sql = new StringBuilder("DECLARE\n\tarr OTHERPACKAGE.TYPE;\n");
    sql.append("BEGIN\n");
    for (int i = 0; i < tmpArray.length; i++) {
        sql.append("\tarr(").append(i).append(") := '").append(tmpArray[i]).append("';\n");
    }
    sql.append("\tSCHEMA.PACKAGE.PROC(arr, ?);\n");
    sql.append("END;");

    Connection conn;
    CallableStatement callableStatement = conn.prepareCall(sql.toString());
    callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
    callableStatement.execute();

这篇关于从Java访问包内部定义的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！