Groovy SQL Oracle Array Function/Procedure Out Parameters Registration

Question

I'm unable to determine how to describe / register array out parameters when using stored function/procedures. I have a requirement to pass multiple array out parameters to utilize a api within an oracle database. Sending array in parameters to stored function/procedures works as well as selecting a single column of type array into a result set.

I believe that the Array out parameter is required to be defined with the database nested table type, NUMBERS_T, in this case. But I cannot locate how to define this relationship using the Groovy Sql class.

Please review the code below which contains required setup and then performs operations that work and areas commented out where failures occur with the corresponding error messages. Any help will be appreciated.

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
***/

score 1 · Accepted Answer · edited May 23 '17 at 12:08

I've just faced the same issue and now have a solution. Basically there are two problems. The first is that Oracle requires you to tell it the name of the array type when the output parameter is registered. The second is how to persuade Groovy to let you do that. Fortunately, it appears that the Groovy designers thought of this and allow you to sub-class groovy.sql.Sql to hook into the parameter setting.

I'll use the example type and stored procedure in this answer to a similar question at the JDBC level:

SQL> CREATE TYPE t_type AS OBJECT (val VARCHAR(4));
2  /
Type created

SQL> CREATE TYPE t_table AS TABLE OF t_type;
2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE p_sql_type (p_out OUT t_table) IS
2  BEGIN
3     p_out := t_table(t_type('a'), t_type('b'));
4  END;
5  /
Procedure created

Now we need a couple of new Groovy classes:

import groovy.sql.*
import java.sql.CallableStatement
import java.sql.PreparedStatement
import java.sql.SQLException
import oracle.jdbc.driver.*

class OracleArrayOutParameter implements OutParameter {
    String typeName

    int getType() {
        OracleTypes.ARRAY
    }
}

class OracleArrayAwareSql extends Sql {

    OracleArrayAwareSql(Sql parent) {
        super(parent)
    }

    void setObject(PreparedStatement statement, int i, Object value) throws SQLException {
        if (value instanceof OracleArrayOutParameter) {
            try {
                OracleArrayOutParameter out = (OracleArrayOutParameter) value;
                ((CallableStatement) statement).registerOutParameter(i, out.getType(), out.typeName);
            } catch (ClassCastException e) {
                throw new SQLException("Cannot register out parameter.");
            }
        }
        else {
            super.setObject(statement, i, value)
        }
    }
}

The usage of these is pretty straightforward. You'll probably want the Oracle documentation on arrays to understand the resulting data structures.

// First create a "normal" groovysqlSql instance, using whatever method you like

def parent = Sql.newInstance("jdbc:oracle:thin:@host:port:sid", "user", "password", "oracle.jdbc.OracleDriver")

// Then create an OracleArrayAwareSql instance giving that parent instance as a parameter

def sql = new OracleArrayAwareSql(parent)

// Now define an OracleArrayOutParameter naming the array type

def tTableParam = new OracleArrayOutParameter(typeName: 'T_TABLE')

// And make a stored procedure call as usual

sql.call("{call p_sql_type(${tTableParam})}") { out ->

    // The returned parameter is of type oracle.sql.ARRAY

    out.array.each { struct ->
        println struct.attributes
    }
}

Groovy SQL Oracle Array Function/Procedure Out Parameters Registration

1 Answers1

Linked