Pages

Wednesday, November 17, 2010

Ibatis - Calling Oracle functions

Configuring XML file: -
<parameterMap id="functionXYZMap" class="java.util.Map">
        <parameter property="outParam" mode="OUT" javaType="java.lang.String" jdbcType="VARCHAR"/>  ---> LINE # 1
        <parameter property="inParam" mode="IN" javaType="java.lang.String" jdbcType="VARCHAR"/>
    </parameterMap>
   
    <procedure id="functionXYZ" parameterMap="functionXYZMap">       
            {? = call get_function_data(?) }
    </procedure>

Java Code:-

 public String getSpecieDescr(String specieCode) {
        Map<String, Object> inputMap = new HashMap<String, Object>();
        inputMap.put("specieCode", specieCode);

        sqlMapClientTemplate.queryForObject(retrieveSpecieDescrQuery, inputMap);
       
        return (String) inputMap.get("specieDescr");  // I was returning the result comes from the above line, it was returning null.

    }

Difficulties I faced when writing the above function:

1. I was not adding the output parameter in LINE # 1 which was giving the exception --> --- Cause: java.sql.SQLException: Missing IN or OUT parameter at index:: 2]], dirtiesContext [false].

2. I added wrong jdbcType as VARCHAR2-->  --- Cause: java.sql.SQLException: Invalid column type]], dirtiesContext [false].

3. I removed ? (output ? =) exception i got is --> PLS-00221: 'GET_SPECIE' is not a procedure or is undefined

4. I havent put (= after my ?) --> --- Cause: java.sql.SQLException: Malformed SQL92 string at position: 3. Expecting "=" got "c"

5. I added wrong javaType as "java.lang.StringBuffer" --> --- Check the output parameters (retrieval of output parameters failed). 
--- Cause: java.lang.NullPointerException]], dirtiesContext [false].  (When I was browsing for the function invocation in one site somebody mentioned that when you use INOUT parameter then you should not use String as javaType because Strings are immutable the values cannot be override, It mislead me such that i thought it will applicable for OUT parameters also, but it is only for INOUT params).

No comments:

Post a Comment