如何使用Groovy从Oracle获取光标? [英] How to get cursor from Oracle using Groovy?
问题描述
我正在Mule ESB
中使用Groovy
脚本从Oracle
存储过程(包括游标)获取输出参数并获取异常.
I'm using a Groovy
script in Mule ESB
to get output parameters from Oracle
stored procedure (including cursor) and getting an exception.
最小示例:
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.module.scripting.transformer.ScriptTransformer:39( http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/transformer/TransformerException.html ) -------------------------------------------------- ------------------------------ 根异常堆栈跟踪: java.sql.SQLException:封闭语句 在oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) 在oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) 在oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) + 3个以上(为所有内容设置调试级别日志记录或'-Dmule.verbose.exceptions = true') ****************************************************** **********************************
(org.mule.api.transformer.TransformerException) org.mule.module.scripting.transformer.ScriptTransformer:39 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/transformer/TransformerException.html) -------------------------------------------------------------------------------- Root Exception stack trace: java.sql.SQLException: Closed Statement 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 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything) ********************************************************************************
Oracle服务器版本: Oracle server version: M子版本: 我正在使用oracle客户端版本 I'm using 我在做什么错了? 以下代码可以帮助您从Oracle匿名块中获取 The following code can help you get variable of 我们应该关注一些关键细节: We should focus on a few key details:
Select
返回1
和2
.Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
.Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
.3.5.0
.11.1.0.7.0
中的jdbc\lib\ojdbc6.jar
.jdbc\lib\ojdbc6.jar
from oracle client version 11.1.0.7.0
.推荐答案
SYS_REFCURSOR
的变量.SYS_REFCURSOR
from Oracle anonymous block.
groovy.sql.Sql
没有相应的OutParameter
,我们将其手动设置为CURSOR_PARAMETER
并将其传递给sql.call
方法{call DECLARE
开头,以END }
结束,且在END之后没有分号.否则,我们的面部表情很难识别.sqlString
内的问号?
是用于参数绑定的位置.绑定是按自然顺序进行的.在此示例中:
?
与parametersList
中的第一个元素绑定:"abc"
,将值视为IN
参数; ?
与CURSOR_PARAMETER绑定,将值视为已传递类型的OUT
参数;
groovy.sql.Sql
doesn't have corresponding OutParameter
and we make it manually as CURSOR_PARAMETER
and pass it to sql.call
method{call DECLARE
and ends with END }
without semicolon after END. Otherwise we can get a poorly recognizable SQLException
in the face.?
inside the sqlString
are places for parameter bindings. Bindings are made in the natural order. In this example:
?
binds with the first element in parametersList
: "abc"
, treating the value as IN
parameter ;?
binds with CURSOR_PARAMETER treating the value as OUT
parameter of passed type;
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")
}
};
这篇关于如何使用Groovy从Oracle获取光标?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!