so this is pack_name.func_name working at sql developer:
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:
but i cannot understand what i should change to make this work
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