oracle11goracle-sqldeveloperspring-mybatis

how to wrap a boolean oracle function on a java integer using mybatis?


so this is pack_name.func_name working at sql developer: enter image description here

using spring and mybatis, i have the issue to deal with oracle's BOOLEAN type with an old jdbc driver that i cannot update. trying this idea, i have at my something-mapper.xml

<mapper namespace="java.packs.SomethingMapper">
<select id="approvePO" parameterType="Map" statementType="PREPARED" resultType="Integer">
        begin
            #{oReturn,jdbcType=INTEGER,mode=OUT} := case when pack_name.func_name(
            #{I_po_num,jdbcType=INTEGER,mode=IN},
            #{I_pgm_id,jdbcType=INTEGER,mode=IN},
            #{O_err_msg,jdbcType=VARCHAR,mode=OUT},
            #{I_user,jdbcType=VARCHAR,mode=IN}
            ) then 1
            else 0
            end;
        end;
</select>

where the method is declared as void approvePO(final Map<String, Object> map); but got this error:

### The error occurred while setting parameters
### SQL: begin                 ? := case when pack_name.func_name(                 ?,                 ?,                 ?,                 ?                 ) then 1                 else 0                 end;             end;
### Cause: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

and here's the input map debbuged:

enter image description here

but i cannot understand what i should change to make this work


Solution

  • i was able to run my boolean function with this mapper:

        <select id="approvePO" parameterType="Map" statementType="CALLABLE" resultType="Integer">
            { call begin
                    #{oReturn,jdbcType=INTEGER,mode=OUT} := case when pack_name.func_name(
                    #{I_po_num,jdbcType=INTEGER,mode=IN},
                    #{I_pgm_id,jdbcType=INTEGER,mode=IN},
                    #{O_err_msg,jdbcType=VARCHAR,mode=OUT},
                    #{I_user,jdbcType=VARCHAR,mode=IN}
                    ) then 1
                    else 0
                    end;
                end
            }
        </select>
    

    changed statementType to callable, resultType to integer and started with { call begin