Re: Stored procedures (package functions) returning a row

[Jeff Butler]

There are two different issues here.

 

1. Oracle can't find your function.  I don't know how to resolve that one, but you have to figure that out first.  Maybe the function is not really in the package/schema you've specified?

 

2. Only use queryForObject if the function returns a result set.  If it is just returning a single value, then call it with syntax like this:

 

{? = call myFunction}

 

Register an output parameter - not a resultMap - then call it with the "update" method.

 

Unfortunatley, the iBATIS stored procedure support is hard to understand - it makes sense if you really understand JDBC, but probably doesn't otherwise.  The most important thing to remember is this - when iBATIS uses the term "result" (resultMap, resultClass, etc.) it ALWAYS refers to a result set.  A single value returned from a procedure or function is NOT a result in iBATIS - it is an output parameter.

 

Jeff Butler

 

On 10/3/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

The log generated by log4j confirms that the PreparedStatement is correctly prepared

DEBUG [main] - Created connection 22664464.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement: {call ut_Tipo_Dato.ut_setup}
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - Returned connection 22664464 to pool.
DEBUG [main] - Checked out connection 22664464 from pool.
DEBUG [main] - {conn-100002} Connection
DEBUG [main] - {pstm-100003} PreparedStatement: {call ut_Tipo_dato.get_key_2 }
DEBUG [main] - {pstm-100003} Parameters: []
DEBUG [main] - {pstm-100003} Types: []
DEBUG [main] - Returned connection 22664464 to pool.
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in it/finmatica/gpj/aa/frontebd/DizionarioTipiDatoTest.xml.
--- The error occurred while applying a parameter map.
--- Check the get_key_2-InlineParameterMap.
--- Check the statement (update procedure failed).
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'GET_KEY_2' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback (GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject (GeneralStatement.java:104)
[...]


Something is wrong on the stored code? It wouldn't seem.... A mini test harness written on-th-gfly runs correctly.
The Oracle diagnostic does not mention 'UT_TIPO_DATO.GET_KEY_2' (instead it reports 'GET_KEY_2' without mentioning its package)

Why does the diagnostic refer a parameterMap / the inline parameter of get_key_2?
--- The error occurred while applying a parameter map.
--- Check the get_key_2-InlineParameterMap.

The XML config file does NOT mention any parameter

<resultMap id="mapChiave" class="it.finmatica.gpj.aa.frontebd.ChiaveBean">
<result property="id" column="TIPO_DATO_ID"/>
</resultMap>

<procedure id="get_key_2" resultMap="mapChiave">
{call ut_Tipo_dato.get_key_2}
</procedure>

If, I've understood well the Jeff's suggestions
http://opensource.atlassian.com/confluence/oss/pages/diffpages.action?pageId=39&originalId=5835
a call to queryForObject is suitable when you get only one object as result.

What is wrong?
I'm confused.