Stored Function - Sending/Receiving Boolean - BD

问题:

Found the solution, see bellow*

I'm trying to execute a stored function, via SimpleJdbcCall (using java + jpa) but I can't execute, it shows:

[Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: 
CallableStatementCallback; uncategorized SQLException for SQL [{? = call PK_BACKOFFICE.SET_PROFESSIONAL(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; 
SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type] with root cause
java.sql.SQLException: Invalid column type

Here's my code:

    dataSource = this.getDataSource();
    SimpleJdbcCall caller = new SimpleJdbcCall(dataSource)
            .withCatalogName("pk_backoffice_api_ui")
            .withFunctionName("set_professional_main")
            .declareParameters(
                    new SqlOutParameter("result",OracleTypes.BOOLEAN),
                    new SqlParameter("i_id_institution",
                            OracleTypes.NUMERIC),
                    new SqlParameter("i_id_prof", OracleTypes.NUMERIC),
                    new SqlParameter("i_first_name", OracleTypes.VARCHAR),
                    new SqlParameter("i_nick_name", OracleTypes.VARCHAR),
                    new SqlParameter("i_gender", OracleTypes.VARCHAR),
                    new SqlParameter("i_id_category", OracleTypes.NUMERIC), 
                    new SqlParameter("i_id_lang", OracleTypes.NUMERIC),
                    new SqlParameter("i_flg_state", OracleTypes.VARCHAR),
                    new SqlParameter("i_commit_at_end", OracleTypes.BOOLEAN),
                    new SqlOutParameter("o_id_prof", OracleTypes.INTEGER),
                    new SqlOutParameter("o_error", OracleTypes.STRUCT, "T_ERROR_OUT")); 

    MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("i_id_institution", 2790)
            .addValue("i_id_prof", 7020000363724L)
            .addValue("i_first_name", "teste")
            .addValue("i_nick_name", "nicknameteste")
            .addValue("i_gender", "f").addValue("i_id_category", 20)
            .addValue("i_id_lang", 1).addValue("i_flg_state", "A")
            .addValue("i_commit_at_end", true);


    Integer res = caller.executeFunction(Integer.class, params);

    Map out = caller.execute(params);
    int idprof = (int) out.get("o_id_prof"); //getting out parameter?

    // para obter o erro - caso haja
    // (com.alert.core.plsql.types.TErrorOutType)__sJT_st.getORAData(35,com.alert.core.plsql.types.TErrorOutType.getORADataFactory());

    System.out.println(res);
    return res;
}

I realized the problem is sending/receiving Booleans to the function.. I also found out this is a problem of jdbc drivers... There isn't the option to edit the type in the BD, so I really need to find a way to send/receive booleans.

..Any suggestions?

回答1:

The solution was to treat the boolean in the call of the function:

Which means sending the value instead of ?/variable and treating the return (also boolean) in the call*

@Resource(name = "myJdbcTemplate")
public JdbcTemplate simpleJdbc;

private Connection getConnection() throws SQLException{
    return simpleJdbc.getDataSource().getConnection();
}

    @Override
    public boolean setProfessionalBD(Professional professional,
    Category category, BigDecimal id_language, BigDecimal id_institution)
            throws SQLException {

    CallableStatement proc_strm = null;

    proc_strm = getConnection().prepareCall(
            "BEGIN ? := "
                    + "     CASE PK_BACKOFFICE.SET_PROFESSIONAL"
                    + "         (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,TRUE,?,?)"
                    + "     WHEN true " + "     THEN 1 " + "        ELSE 0 "
                    + "     END; " + "  END;");

    proc_strm.registerOutParameter(1, Types.INTEGER);
    proc_strm.setBigDecimal(2, id_language);
    proc_strm.setBigDecimal(3, id_institution);
    (...)
    proc_strm.execute();