我无法确定在使用存储的函数/过程时如何描述/注册数组输出参数。我需要传递多个数组输出参数以利用 Oracle 数据库中的 api。将参数中的数组发送到存储的函数/过程以及选择数组类型的单列到结果集中都有效。
我认为在这种情况下，需要使用数据库嵌套表类型 NUMBERS_T 来定义 Array out 参数。但我无法找到如何使用 Groovy Sql 类定义这种关系。
请查看下面的代码，其中包含所需的设置，然后执行有效的操作以及注释掉发生故障的区域以及相应的错误消息。任何帮助将不胜感激。
import groovy.sql.Sql // groovy v2.4.5
import oracle.jdbc.pool.OracleDataSource // ojdbc6.jar v11.2.0.4
def dataSource = new oracle.jdbc.pool.OracleDataSource (URL: 'jdbc:oracle:thin:@myServer:1521/myService', user: 'myAccount', password: 'myPassword')
def sql = new Sql (dataSource)
/*** begin array test setup ...
sql.execute """\
create type numbers_t as table of number;
"""
sql.execute """\
create table numbers_tab (
value number
)
"""
sql.execute """\
create or replace procedure insert_numbers_proc (p_numbers numbers_t) is
begin
for iterator in 1 .. p_numbers.count () loop
insert into numbers_tab (value) values (p_numbers (iterator));
end loop;
commit;
end insert_numbers_proc;
"""
sql.execute """\
create or replace function select_numbers_func return numbers_t is
l_result numbers_t;
begin
select value bulk collect into l_result from numbers_tab;
return l_result;
end select_numbers_func;
"""
sql.execute """\
create or replace procedure select_numbers_proc (p_numbers out numbers_t) is
begin
select value bulk collect into p_numbers from numbers_tab;
end select_numbers_proc;
"""
... end of array test setup ***/
def numbers = [3, 1, 4, 1, 5, 9, 2, 6, 5] as Object[]
def pArray = sql.dataSource.connection.createARRAY 'NUMBERS_T', numbers
sql.call "{call insert_numbers_proc ($pArray)}"
def sqlRow_1 = sql.firstRow "select select_numbers_func () as select_results from dual"
println "Class: ${sqlRow_1.select_results.getClass ().name}, Values: ${sqlRow_1.select_results.array}"
//output= Class: oracle.sql.ARRAY, Values: [3, 1, 4, 1, 5, 9, 2, 6, 5]
def sqlRow_2 = sql.firstRow "select cast (multiset (select value from numbers_tab) as numbers_t) as select_results from dual"
println "Class: ${sqlRow_2.select_results.getClass ().name}, Values: ${sqlRow_2.select_results.array}"
//output= Class: oracle.sql.ARRAY, Values: [3, 1, 4, 1, 5, 9, 2, 6, 5]
/*** all of these failt ...
sql.call ("{call ${Sql.ARRAY} := select_numbers_func ()}") { result_1 -> println result_1.getClass ().name }
groovy.sql.Sql callWithRows
WARNING: Failed to execute: {call ? = select_numbers_func ()}
because: ORA-03115: unsupported network datatype or representation
sql.call ("{call select_numbers_proc (${Sql.ARRAY})}") { result_2 -> println result_2.getClass ().name }
groovy.sql.Sql callWithRows
WARNING: Failed to execute: {call select_numbers_proc (?)}
because: ORA-03115: unsupported network datatype or representation
sql.call ("{call ${oracle.sql.ARRAY} := select_numbers_func ()}") { result_3 -> println result_3.getClass ().name }
groovy.sql.Sql callWithRows
WARNING: Failed to execute: {call ? = select_numbers_func ()}
because: Invalid column type
sql.call ("{call select_numbers_proc (${oracle.sql.ARRAY})}") { result_4 -> println result_4.getClass ().name }
groovy.sql.Sql callWithRows
WARNING: Failed to execute: {call select_numbers_proc (?)}
because: Invalid column type
***/
