Как получить курсор из Oracle с помощью Groovy?

Я использую Groovy script в Mule ESB для получения выходных параметров из Oracle хранимой процедуры (включая курсор) и получения исключения.

Минимальный пример:

import groovy.sql.Sql
import oracle.jdbc.pool.OracleDataSource
import oracle.jdbc.driver.OracleTypes

def ds = new OracleDataSource()
// setting data source parameters here

def sql = new Sql(ds)
def data = []

sql.call("""declare
result_table sys_refcursor;
begin

open result_table for select 1 as a from dual;

insert into CURSOR_TEST (ID) values (1);
commit;

${Sql.resultSet OracleTypes.CURSOR} := result_table;

insert into CURSOR_TEST (ID) values (2);
commit;

end;
"""
){ table ->

  throw new RuntimeException("Never getting this exception.")

  table.eachRow {
    data << it.toRowResult()
  }
}

sql.close()

return data

Ошибка:

Message               : java.sql.SQLException: Closed Statement (javax.script.ScriptException)
Code                  : MULE_ERROR--2
--------------------------------------------------------------------------------
Exception stack is:
1. Closed Statement(SQL Code: 17009, SQL State: + 99999) (java.sql.SQLException)
  oracle.jdbc.driver.SQLStateMapping:70 (null)
2. java.sql.SQLException: Closed Statement (javax.script.ScriptException)
  org.codehaus.groovy.jsr223.GroovyScriptEngineImpl:323 (http://java.sun.com/j2ee/sdk_1.3/techdocs/api/javax/script/ScriptException.html)
3. java.sql.SQLException: Closed Statement (javax.script.ScriptException)
(org.mule.api.transformer.TransformerException) org.mule.scripting.transformer.ScriptTransformer: 39 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/transformer/TransformerException.html) -------------------------------------------------- ------------------------------ Трассировка стека прерываний корня: java.sql.SQLException: Закрытое выражение at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) + Еще 3 (установить регистрацию уровня отладки или '-Dmule.verbose.exceptions = true' для всего) ************************************************** ******************************

Select from CURSOR_TEST возвращает 1 и 2.

Версия сервера Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

Версия мула: 3.5.0.

Я использую jdbc\lib\ojdbc6.jar из версии клиента oracle 11.1.0.7.0.

Что я делаю неправильно?

Ответ 1

Следующий код может помочь вам получить переменную SYS_REFCURSOR из анонимного блока Oracle.

Мы должны сосредоточиться на нескольких ключевых деталях:

Класс groovy.sql.Sql не имеет соответствующего OutParameter, и мы делаем его вручную как CURSOR_PARAMETER и передаем его методу sql.call
Обратите внимание, что блок начинается с {call DECLARE и заканчивается END } без точки с запятой после END. В противном случае мы можем получить плохо узнаваемый SQLException в лицо.
Вопросительные знаки ? внутри sqlString являются местами привязки параметров. Привязки выполняются в натуральном порядке. В этом примере:
- первый ? связывается с первым элементом в parametersList: "abc", обрабатывая значение как параметр IN;
- второй ? связывается с CURSOR_PARAMETER, обрабатывая значение как параметр OUT прошедшего типа;
За закрытием после sql.call и ResultSet rs допускается только одно замыкание строки курсора my_cur, объявленного в анонимном блоке.

import groovy.sql.OutParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes

import java.sql.ResultSet

def driver = 'oracle.jdbc.driver.OracleDriver'
def sql = Sql.newInstance('jdbc:oracle:thin:@MY-SERVER:1521:XXX', 'usr', 'psw', driver)

// special OutParameter for cursor type
OutParameter CURSOR_PARAMETER = new OutParameter() {
    public int getType() {
        return OracleTypes.CURSOR;
    }
};

// look at some ceremonial wrappers around anonymous block
String sqlString = """{call
    DECLARE
      my_cur SYS_REFCURSOR;
      x VARCHAR2(32767) := ?;
    BEGIN

        OPEN my_cur
        FOR
        SELECT x || level AS my_column FROM dual CONNECT BY level < 10;

        ? := my_cur;
    END
}
""";

// the order of elements matches the order of bindings
def parametersList = ["abc", CURSOR_PARAMETER];


// rs contains the result set of cursor my_cur
sql.call(sqlString, parametersList) { ResultSet rs ->
  while (rs.next()) {
      println rs.getString("my_column")
  }
};