使用 CallableStatement 从 Oracle DB 的自定义对象类型的关联数组中检索数据 [英] Retrieving data from associative array of custom object type from Oracle DB using CallableStatement

查看:71
本文介绍了使用 CallableStatement 从 Oracle DB 的自定义对象类型的关联数组中检索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在从 Spring-boot 应用程序调用 Oracle 12c 数据库中的存储过程 (SP).我正在使用 CallableStatement 来做到这一点.SP 的 OUT 参数之一是具有自定义对象类型元素的关联数组.我无法以正确的方法检索数据.

I am calling a stored procedure(SP) in an Oracle 12c database from a Spring-boot application. I am using CallableStatement to do this. One of the OUT parameters of the SP is an associative array with elements of a custom object type. I am having trouble retrieving the data in a proper method.

TYPE trans_list IS TABLE OF T_RPT_TXN_DTLS_OBJ INDEX BY PLS_INTEGER;
TYPE T_RPT_TXN_DTLS_OBJ AS OBJECT( id VARCHAR2(20), amount NUMBER(10,2), desc VARCHAR2(100))

Connection connection = jdbcTemplate.getDataSource().getConnection();
OracleCallableStatement callableStatement = connection.prepareCall("{call SAMPLE_PROC(?,?)}").unwrap(OracleCallableStatement.class);
callableStatement.setString(1, ID); 

--Here I don't know what to do--
callableStatement.registerIndexTableOutParameter(2, 500, OracleTypes.OTHER, 0);
--------------------------------

我尝试过的事情

我使用谷歌浏览了不同的网站.也通过了stackoverflow.上面的代码是我被困的地方.由于第三个参数是数组元素的数据类型,我输入了 OracleTypes.OTHER 但这会引发 SQLException: invalid column type 1111.
我在官方文档中读到,对于自定义对象类型,我们可以将它们映射到 Java 对象.我也不知道该怎么做.请参阅用户定义类型>https://docs.oracle.com/cd/B19306_01/java.102/b14355/datacc.htm#BHCGCBJC

Things I have tried

I looked through different websites using google. Went through stackoverflow as well. The above code is where I am stuck at. Since the 3rd argument is the datatype of the array elements, I put in OracleTypes.OTHER but that throws an SQLException: invalid column type 1111.
I read in the official docs that for custom object types, we can map them to Java objects. I have no idea how to do that either. Please refer to User_defined types in https://docs.oracle.com/cd/B19306_01/java.102/b14355/datacc.htm#BHCGCBJC

任何示例代码、详细文档、适合我的案例的在线示例链接.我特别需要知道 out 参数注册的 arg[2],以及如何从 callableStatement 映射到自定义 Java 对象(也许使用 .getObject(int),但我不知道映射).

Any sample code, detailed docmentation, links to online examples that fits my case. I specifically need to know arg[2] of the out parameter registration, and how I might go about mapping to a custom Java object from the callableStatement (perhaps using .getObject(int) but I don't know about the mapping).

推荐答案

参见这个答案.

你不能.

  1. 您需要在 SQL 范围内定义数据类型(使用 CREATE TYPE),而不是在 PL/SQL 范围内,因为 JDBC 只能使用 SQL 定义的数据类型.
  2. 第 1 点的结果是 JDBC 不支持关联数组,因为它们是仅 PL/SQL 的数据类型,并且您需要使用集合(与 C# 不同,C# 只支持关联数组而不支持集合).所以你需要从类型中删除 INDEX BY 子句.
  3. 您需要能够从数组中的类型映射到 Java 数据结构;一种方法是使用SQLData 接口.这个答案中有一个例子.
  1. You need to define the data type in the SQL scope (using CREATE TYPE) rather than in the PL/SQL scope, in a package as JDBC can only work with SQL defined data types.
  2. A consequence of point 1 is that JDBC does not support associative arrays, as they are a PL/SQL only data type, and you need to use collections (unlike C#, which only supports associative arrays and does not support collections). So you need to remove the INDEX BY clause from the type.
  3. You need to be able to map from the type in the array to a Java data structure; one way of doing this is to use the SQLData interface. An example is in this answer.

这篇关于使用 CallableStatement 从 Oracle DB 的自定义对象类型的关联数组中检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆