CODEWITHSUNDEEP
oracle-databaseplsqlmybatis
Hernan Diaz
Hernan Diaz

Reputation: 566

Calling pl/sql function with mybatis 3

I have a function that returns a boolean value in pl/sql. I have tried to get directly that boolean value without success, so now I'm trying to convert it to string (I do not want to modify the database):

<parameterMap id="publicObject"   type="map">
<parameter javaType="java.lang.Object" jdbcType="VARCHAR" mode="OUT" property="result" /> 
<parameter javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" property="id" /> 
</parameterMap>     

<select id="isPublicObject" parameterMap="publicObject" statementType="CALLABLE">

   <![CDATA[
    {
    declare
    v_bool BOOLEAN := TRUE;
    begin
    v_bool := PACKNAME.STF$IS_PUBLIC_OBJECT(#{id});
    #{result} := CASE WHEN v_bool THEN 'TRUE' ELSE 'FALSE' END;
    end;
    }
    ]]>

</select>

Then I get this exception: "Error querying database. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #2 with JdbcType OTHER. Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111 ####

This code works correctly in the database:

declare
    v_bool BOOLEAN := TRUE;
    v_str  VARCHAR2(5);
begin
   v_bool := PACKNAME.STF$IS_PUBLIC_OBJECT('000000');
   v_str := CASE WHEN v_bool THEN 'TRUE' ELSE 'FALSE' END;
   dbms_output.put_line('result:');
   dbms_output.put_line(v_str); 
end;

Upvotes: 6

Views: 19368

Answers (2)

Jarandinor
Jarandinor

Reputation: 1866

I wrote parameterType & Map example. It works on my test data.

XML:

<update id="isPublicObject" parameterType="map" statementType="CALLABLE">
    declare
        v_bool BOOLEAN := TRUE;
    begin
        v_bool := PACKNAME.STF$IS_PUBLIC_OBJECT(#{id});
        #{result,jdbcType=VARCHAR,mode=OUT} := CASE WHEN v_bool THEN 'TRUE' ELSE 'FALSE' END;
    end;
</update>

Mapper:

public interface PLSQLMapper {
    public void isPublicObject(Map<String, Object> parameterMap);
}

Main: 

PLSQLMapper mapper = session.getMapper(PLSQLMapper.class);

Map<String, Object> parameterMap = new HashMap<String, Object>();
parameterMap.put("id", 1);
mapper.isPublicObject(parameterMap);
System.out.println("result: " + parameterMap.get("result"));

Upvotes: 11

Seeta Somagani
Seeta Somagani

Reputation: 787

The #{} syntax can only be used for parameter substitution. It is not a variable that gets set after the execution of the PL/SQL. It can only be set before the prepared statement is built.

One way of going forward would be to use stored procedure and use an OUT param to get your result out.

Upvotes: -1

Related Questions