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

Question

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

gabriel119435 · Accepted Answer · 2023-05-25 05:10:09Z

0

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

answered May 25, 2023 at 5:10

gabriel119435

6,9325 gold badges38 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related