我正在使用一个Groovy
脚本在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
Error:
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 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”)
********************************************************** **********************************
Select
from CURSOR_TEST
回报1
and 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
.
我究竟做错了什么?
下面的代码可以帮助你获取变量SYS_REFCURSOR
来自 Oracle 匿名块。
我们应该关注几个关键细节:
- Class
groovy.sql.Sql
没有对应的OutParameter
我们手动将其制作为CURSOR_PARAMETER
并将其传递给sql.call
method
- 考虑该块以
{call DECLARE
并以END }
END 后不加分号。否则我们会得到一个难以识别的SQLException
在脸上。
- The question marks
?
inside the sqlString
are places for parameter bindings. Bindings are made in the natural order. In this example:
- 首先
?
与第一个元素绑定parametersList
: "abc"
,将值视为IN
范围 ;
- 第二
?
与 CURSOR_PARAMETER 绑定,将值视为OUT
传递类型的参数;
- 之后只有一个进入关闭
sql.call
and 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")
}
};
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)