iBatis mapping for oracle cursor

Question

I have the following iBatis mapping for an Oracle Stored Procedure that returns a true/false value.

  <resultMap id="isAuthorizedResult" class="java.lang.Boolean">
    <result property="isAuthorized" column="isAuthorized"/>
  </resultMap>
  <parameterMap id="isAuthorizedCall" class="map">
    <parameter property="prgType" jdbcType="String" javaType="java.lang.String" mode="IN"/>
    <parameter property="parCode" jdbcType="String" javaType="java.lang.String" mode="IN"/>
    <parameter property="userId" jdbcType="String" javaType="java.lang.String" mode="IN"/>
    <parameter property="Result0" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="isAuthorizedResult"/>
  </parameterMap>
<procedure id="isAuthorized" parameterMap="isAuthorizedCall">{call chk_user_ocpncy (?,?,?,?) }</procedure>

I call the mapping from my Java code like this:

getSqlMapClientTemplate().queryForObject("reexamination.isAuthorized", paramMap);

However, I get the following error...

Fail to convert to internal representation; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

What am I doing wrong? can we not store a boolean value directly into the cursor?

Answer 1

Returning a Boolean type is not supported by Oracle JDBC. Or more specifically, it can't be used in any result set in Oracle (there is a Boolean that can be used in PL/SQL, but you can't return it in a ref cursor or declare a column of being type 'Boolean'.

It sounds like you are saying that your ref cursor contains boolean? If yes, you will need to return 'Y' or 'N' or something similar. Please consider posting the source/signature of the stored procedure - that will help with the answer.

http://www.oracle.com/technology/tech/java/sqlj%5Fjdbc/htdocs/jdbc%5Ffaq.html#34%5F05

Tom Kyte's traditionally cheeky response: You Asked

Here's a real short one for you Tom:

Why doesn't Oracle RDBMS have a boolean datatype?

and we said...

since ..., flag char(1) check (flag in ( 'Y', 'N' )), ...,

serves the same purpose, requires the same amount of space and does the same thing - I guess we feel this is a feature we can let them have that we really don't need.

I mean - what do you get back from a column in "access" that is a boolean? TRUE / FALSE. We'll give you Y/N -- if you would like TRUE/FALSE, we can accomplish that easily with DECODE(flag,'Y','TRUE','N','FALSE')