如何在 Java 中映射 TYPE TABLE OF VARCHAR2(5)? [英] How to map TYPE TABLE OF VARCHAR2(5) in java?

问题描述

我有一个带有以下签名的程序.

I have a procedure with the signature below.

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPACK
AS
PROCEDURE GETBOX (DSSO_BoxNumber      IN     VARCHAR2,
CreateDateTime         OUT tCreateDateTime,
                          ReceiptDateTime        OUT tReceiptDateTime,
                          CSCBoxNumber           OUT tCSCBoxNumber,
                          DSSOBoxNumber          OUT tDSSOBoxNumber,
                          PackID                 OUT tPackID,
                          RequestID              OUT tRequestID,
                          ExceptionID            OUT tExceptionID,
                          Name                   OUT tName,
                          FolderID               OUT tFolderID,
                          ClosedDateTime         OUT tClosedDateTime,
                          OpenStatus             OUT tOpenStatus,
                          RequestOpenStatus      OUT tRequestOpenStatus,
                          RETURNED               OUT tRETURNED)
...

自定义类型定义如下.

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPACK
  AS
      TYPE tCreateDateTime is TABLE of  VARCHAR2(15)
      INDEX BY BINARY_INTEGER;
      TYPE tReceiptDateTime is TABLE of VARCHAR2(15)
      INDEX BY BINARY_INTEGER;
      TYPE tCSCBoxNumber is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tDSSOBoxNumber is TABLE of  VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tPackID is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tRequestID is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tExceptionID is TABLE of  VARCHAR2(20)
...

谁能帮忙，如何在java中注册输出参数?

Can anyone please help, how to register the out parameters in java?

我尝试了以下方法，但没有成功.

I have tried the following, but no luck.

cs.setString(1, "XYZ123");
cs.registerOutParameter(2, Types.ARRAY,"MYSCHEMA.MYPACK.tCreateDateTime");
...

出现以下错误.

java.sql.SQLException: invalid name pattern: MYSCHEMA.MYPACK.tCreateDateTime
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:272)
...

我们已检查架构是否可以访问过程，并且它是在主架构中定义的.我们还尝试为此包创建公共同义词.还是不行..

We have checked that the schema has access to the procedure and it is defined in the master schema. We also have tried by creating public synonym for this package. Still not working..

推荐答案

您的问题标题具有误导性.表和表 的索引是两种截然不同的数据类型.第一个称为嵌套表，第二个称为关联数组，在 Oracle (PL/)SQL 术语中.

The title of your question is misleading. table of <TYPE> and table of <TYPE> index by <TYPE> are two very different data types. First is called nested table and second is called associative array in Oracle (PL/)SQL parlance.

主要问题是:

• 向 Java 显示的 PL/SQL 接口中使用的集合类型必须是 SQL 类型，而不是 PL/SQL 类型
• 关联数组不是 SQL 类型而是 PL/SQL 类型

解决了第一个问题，例如在如何将数组从Java返回到PL/SQL?(即使调用方向不同，问题也相同).

The first issue is addressed e.g. in How to return an array from Java to PL/SQL? (the issue is the same even the call direction is different).

进一步阅读:

• 数据库 SQL 语言参考 创建类型.
• 数据库 PL/SQL 语言参考 创建类型 语句
• 数据库 PL/SQL 语言参考 PL/SQL集合和记录

这篇关于如何在 Java 中映射 TYPE TABLE OF VARCHAR2(5)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！