ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From C.Ze...@ads.it
Subject RE: Stored procedures (package functions) returning a row
Date Tue, 03 Oct 2006 15:50:49 GMT

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/user-java@ibatis.apache.org/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



                                                                           
             Christopher.Mathr                                             
             usse@sybase.com                                               
                                                                       Per 
             03/10/2006 17:22          C.Zecca@ads.it,                     
                                       user-java@ibatis.apache.org         
                                                                        CC 
                Per favore,                                                
               rispondere a                                        Oggetto 
             user-java@ibatis.         RE: Stored procedures (package      
                apache.org             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: C.Zecca@ads.it [mailto:C.Zecca@ads.it]
Sent: Tuesday, October 03, 2006 3:56 AM
To: user-java@ibatis.apache.org
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.


Mime
View raw message