Sql – Error occurred while setting parameters

callablemybatisoracleplsqlsql

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:

<select id="pr_start" statementType="CALLABLE" parameterType="map">
  call 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>

Also it's possible to use native Oracle syntax:

<select id="pr_start" statementType="CALLABLE" parameterType="map">
  begin
    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}
    );
  end;
</select>