RE: Stored procedures (package functions) returning a row

C . Zecca Tue, 03 Oct 2006 08:51:14 -0700

Thanks, Christopher!

I tried hard to check everything on the DB side. The ut_Tipo_dato package is in a good state (at least while it's observed via PLSQL Developer)
and compiles correctly. It is run successfuly by OUnit (I've performed the test just now)
I wrote, as well, a mini test harness that works fine

declare
-- Local variables here
primaryKey Tipo_Dato.t_PK;
i number(10);
begin
-- Test statements here

ut_Tipo_dato.ut_setup;
primaryKey := ut_Tipo_dato.get_key_2;
i := primaryKey.tipo_dato_ID;
dbms_output.put_line( 'key_2 i = ' || to_char( i ) );

ut_Tipo_dato.ut_teardown;
end;

I know that there are a number of details which, all together, at the same time, make the thing hard.
I'm approaching to iBatis without a good knowledge of JDBC:

Now I'm trrying to understand
1) when I have to call update() when I have to call queryForObject()
2) let's assume that working with an ouput ("result" ?) parameter is correct. How can I describe a "result parameter" that has to be mapped to a PL/SQL record type?

The examples I've found in this mailing list (e.g http://www.mail-archive.com/[email protected]/msg00998.html)
describe <parameter property="result .../> that are mapped onto SQL basic types

Is not the difficolty to retrieve ut_Tipo_dato.get_key_2 due to some other problem?
I gues that my "monkey user" approach to iBatis (a very fist beginner that has no knowledege of JDCB, too) implies a number of errors that, all together, make impossible to retrieve the stored package function (and perhaps event to run it).

Thank you for your help
(I hope that my naive approaching and the issues that I raise might be of some help)
I've just received a mail from Jeff and... probably, the version of the Developer Guide I consult (version 2.0) is not the last one :S

ciao
Cesare

[EMAIL PROTECTED]

[EMAIL PROTECTED]

03/10/2006 17:22

Per favore, rispondere a
[email protected]

Per	[EMAIL PROTECTED], [email protected]
CC
Oggetto	RE: Stored procedures (package functions) returning a row

This is an Oracle generated message and it's telling you rather clearly that your procedure cannot be found.
--- 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:

One thing that I would suggest is that while the procedure might exist within your schema it might not be valid, meaning that it might contain errors. Please look to see if the procedure has errors. If it does then Oracle will tell you that it does not exist and that is because it is not valid.

There are only a few possibilities here:

The procedure does not exist
The procedure exists but is not contained within the package/schema that you are specifying.
The procedure exists but is invalid due to compilation errors.

This is clearly not a problem with iBatis or with the way that the procedure is being call. This is simply an issue within the Oracle schema that you are using.

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 03, 2006 3:56 AM
To: [email protected]
Subject: Re: Stored procedures (package functions) returning a row

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.

RE: Stored procedures (package functions) returning a row

Reply via email to