I'm trying to call a procedure with MyBatis.
This is the procedure signature:
PROCEDURE pr_start(io_calc IN OUT TYPE_CALC,
in_restart BOOLEAN DEFAULT TRUE,
in_user VARCHAR2 DEFAULT NULL);
It's in a package named PACKAGE_PP
.
This is how TYPE_CALC
is declared:
CREATE OR REPLACE TYPE TYPE_CALC AS OBJECT (
modelField VARCHAR2(5 CHAR),
sysField VARCHAR2(5 CHAR),
hexField VARCHAR2(5 CHAR)
);
This is my XML mapping:
<select id="pr_start"
statementType="CALLABLE" parameterType="map">
{
exec PACKAGE_PP.pr_start(
#{io_calc,mode=INOUT,jdbcType=STRUCT,jdbcTypeName=TYPE_CALC},
#{in_restart,mode=IN,jdbcType=BOOLEAN,jdbcTypeName=BOOLEAN},
#{in_user,mode=IN,jdbcType=VARCHAR,jdbcTypeName=VARCHAR2}
)
}
</select>
(p.s. I also tried with call
instead of exec
, it produces the same error)
My java mapper:
public interface PackagePPMapper {
Object pr_start(Map<String, Object> param);
}
This is how I call it:
Object[] typeCalcArr = new Object[]{"A", "s", "0xD"};
WebSphereNativeJdbcExtractor wsn = new WebSphereNativeJdbcExtractor();
Connection connection = wsn.getNativeConnection(session.getConnection()); //session is a SqlSession (properly initialized)
StructDescriptor descriptor = StructDescriptor.createDescriptor("TYPE_CALC", connection);
STRUCT typeCalc = new STRUCT(descriptor, connection, typeCalcArr);
Map<String, Object> parametersMap = new HashMap<String, Object>();
parametersMap.put("io_calc", typeCalc);
parametersMap.put("in_restart", restart);
parametersMap.put("in_user", user);
getMapper(session).pr_start(parametersMap);
StackTrace:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLException: Non supported SQL92 token at position: 6
### The error may exist in com/jsfproj/mybatis/xmlmapper/PackagePPMapper.xml
### The error may involve com.jsfproj.mybatis.PackagePPMapper.pr_start-Inline
### The error occurred while setting parameters
### SQL: { exec PACKAGE_PP.pr_start( ?, ?, ? ) }
### Cause: java.sql.SQLException: Non supported SQL92 token at position: 6
What am I missing? Should procedures be called in other ways?
Best Answer
Remove surrounding braces to stop JDBC driver from trying to interpret query as SQL92-compatible query:
Also it's possible to use native Oracle syntax: