iBatis and Stored Oracle procedures/functions

This one took me a while to get it right the first time. I won’t go into details of configuring iBatis datasources and such, and will go straight to putting some queries in the sqlMap file. Just let me note that I’m using iBatis 2.3 for these examples. I’ll start off with a procedure call.

<procedure id="getUserRoles" parameterMap="myParamMap">
    { call SCHEMA.GET_USERS_ROLES(?, ?) }
</procedure>

This one is pretty self-explanatory, just defining a procedure to be called. Notice the questionmarks in the SQL, don’t put the usual #variable# style annotation here. Also instead of parameterClass I use parameterMap here, which means I’ll have to define a parameter map for this query or it won’t work.

<parameterMap id="myParamMap" class="java.util.Map">
    <parameter property="username" mode="IN" />
    <parameter property="roles" jdbcType="ORACLECURSOR" mode="OUT" />
</parameterMap>

Here I explain to iBatis what kind of parameters I want to pass to the query and whether IN, OUT or INOUT mode should be used. It is important to specify correct jdbcType and javaType for the parameters otherwise when calling the procedure iBatis will spit out a very annoying Exception. Now for the JAVA part.

HashMap params = new HashMap();
params.put("username", username);
getSqlMapClient().queryForObject("getUserRoles", params);

Object o = params.get("roles");
if(o instanceof ResultSet)
{
    ResultSet res = (ResultSet) o;
    while(res.next())
        System.out.println(res.getInt("ROLE_NR"));
    res.close();
}

As you can see the procedure call is pretty much like any other SELECT query, but you don’t need to check the return value. All the OUT parameters will be stored in the Map object that you passed. In my case I defined a roles parameter as ORACLECURSOR which translates into ResultSet object. It’s not shown in this code snippet, but don’t forget to use startTransaction() and endTransaction() if you’re managing your own transactions. Also if the stored procedure is modifying the data you’ll most likely need commitTransaction() too, but iBatis transaction management is a different story.

And now for some stored function calling.

<procedure id="exampleFunction" parameterMap="funcMap">
    { ? = call SCHEMA.MY_FUNCTION(?, ?) }
</procedure>

The first thing you might notice is that it still says , but it’s no a typo, iBatis doesn’t have a tag (at least to my knowledge). Note how because it’s a function it has a questionmark at the beginning of the query to specify the return variable.

<parameterMap id="funcMap" class="java.util.Map">
  <parameter property="result" jdbcType="NUMERIC" mode="OUT"/>
  <parameter property="P1" />
  <parameter property="P2" javaType="java.util.Date" jdbcType="DATE" mode="OUT" />
</parameterMap>

Here I defined a parameter map which is pretty much like the one used for the procedure call except for the extra parameter result that is used to get the return value, but formally registered as an OUT parameter and not a return value.

HashMap params = new HashMap();
params.put("P1", param);
getSqlMapClient().queryForObject("exampleFunction", params);

Object o = params.get("result");
if(o instanceof Long)
{
    long res = (Long)o;
    System.out.println(res);
}

The actual call to the stored function is exactly the same as in the case of procedure. You just have to correctly configure the parameter mapping so that the return value will be available as an extra output parameter. In fact correctly configuring the parameter map was the place where I spent most of my time trying to figure out how to do it. In my case when using Oracle and retrieving an integer you can not jdbcType it INTEGER, NUMBER or whatever, it only worked when I put NUMERIC as a parameter JDBC type. That’s why I put so much stress on the parameter configuration. I wasted quite some time getting the annoying “java.sql.SQLException: Invalid column type” error. So, I guess that’s about it.

Discussion

11 Comments

  • What is ORACLECURSOR in the SQL Map of the first example. Neither java.sql.Types nor oracle.jdbc.OracleTypes defines such a jdbc type.

  • You are right about ORACLECURSOR not being defined in Types or OracleTypes, but that’s why I wrote about my experience. I just couldn’t get it working by using the plain CURSOR type. And through trial and error I found out about this method of using the ORACLECURSOR. You can read more here – http://opensource.atlassian.com/confluence/oss/display/IBATIS/Oracle+REF+CURSOR+Solutions

  • Hi,
    I have a query regarding the same.

    Map DataMap = new HashMap()
    DataMap.put(“X”,X);
    DataMap.put(“Y”,Y);
    DataMap.put(“Z”,Z);

    {call FP_Details(?,?,?)}

    How do i write the Ibatis call for the above code construct in java ???

    getSqlMapClient().queryForObject(“getDetails”, DataMap);– will this work in Ibatis……

    Please reply immediately…………

  • Hey, nice post, really well written. You should write more about this.

  • [...] iBatis and Stored Oracle procedures/functions Published 2008-09-01 java 2 Comments Article moved here. [...]

  • Above example is fine, but where is the procedure definition, can any one post the complete working example, as i am not able to run by this way, might be my procedure is wrong. as my procedure is receiving one parameter, and we are passing two placeholder while making the call the procedure.

    my procedure—
    create or replace
    FUNCTION show_cont(IN_ID IN INTEGER)
    RETURN types.ref_cursor
    AS
    con_cursor types.ref_cursor;
    BEGIN
    OPEN con_cursor FOR
    SELECT FIRST_NAME, LAST_NAME FROM CONTACT WHERE ID = IN_ID;
    RETURN con_cursor;
    END;
    ——
    rest of the java and ibatis is given in above example. please help me.
    Thanks.

  • Hey, I have Done in Same Process But its giving me an error

    Satement calling
    com.ibatis.common.jdbc.exception.NestedSQLException:
    — The error occurred in com/ibatis/mdo/dao/impl/IbatisSample.xml.
    — The error occurred while applying a parameter map.
    — Check the IbatisSample.resultMap.
    — Check the output parameters (retrieval of output parameters failed).
    — Cause: java.lang.NullPointerException
    Caused by: java.lang.NullPointerException
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
    at com.ibatis.mdo.dao.impl.IbatisSampleDao.getResultSet(IbatisSampleDao.java:22)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
    at $Proxy0.getResultSet(Unknown Source)
    at TestIbatis.main(TestIbatis.java:28)
    Caused by: java.lang.NullPointerException
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlExecutor.java:354)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:301)
    at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
    … 13 more

    Caused by:
    java.lang.NullPointerException
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlExecutor.java:354)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:301)
    at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
    at com.ibatis.mdo.dao.impl.IbatisSampleDao.getResultSet(IbatisSampleDao.java:22)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
    at $Proxy0.getResultSet(Unknown Source)
    at TestIbatis.main(TestIbatis.java:28)

    Caused by:
    java.lang.NullPointerException
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.retrieveOutputParameters(SqlExecutor.java:354)
    at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:301)
    at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
    at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
    at com.ibatis.mdo.dao.impl.IbatisSampleDao.getResultSet(IbatisSampleDao.java:22)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
    at $Proxy0.getResultSet(Unknown Source)
    at TestIbatis.main(TestIbatis.java:28)
    Exception in thread “main” java.lang.NullPointerException
    at TestIbatis.main(TestIbatis.java:29)

  • Hi M preetham,

    please take a look at your class TestIbatis first and check for uninitialized variables or calls that potentially return a null value. According to your stack trace it seems that NPE is caused by something on line 28 or 29.

  • My class Test Ibatis is to display the result from Dao but in Dao it is returning Null that is why i am getting NPE at 29 and 28 but Actually the problem is with the parameterMap see line 1 to 5 Could you any chance Share your StoredProcedure i think the way i am using my procedure is wrong
    Thanks
    M Preetham

  • create or replace PROCEDURE getDBUSERByUserId
    ( p_Cursor Out types.ref_cursor)
    IS
    BEGIN
    open p_Cursor for
    SELECT *
    FROM employee ;
    END;

    Hey,Could you pls look into this stored proc suggest if any changes required.
    Thanks Preetham

  • Hi M preetham,

    I am facing the exact same problem that you had, do you remember how did you solve it?

    Thanks,
    Javier


*