Re: Stored procedures (package functions) returning a row

[C . Zecca]

Jeff

Your XML config code exerted by the following Java code

SqlMapClient lSqlMapClient = Configurazione.getSqlMapIstanza();

    ChiaveBean lChiaveBean = new ChiaveBean();
       try
       {
        lSqlMapClient.update( "row_return_parMap", lChiaveBean );
       }
...

does not work. Here is the diagnostic

DEBUG [main] - {pstm-100001} PreparedStatement: { ? = call ut_Foo.get_key_2 }
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred in it/finmatica/gpj/aa/frontebd/RowReturnTest.xml.  
--- The error occurred while applying a parameter map.  
--- Check the GPJ.parMap.  
--- Check the statement (update procedure failed).  
--- Cause: java.sql.SQLException: ORA-06550: line 1, column 20:
PLS-00382: _expression_ is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

We give up.
:/

After, all, if Oracle does not support any JDBC driver able to return complex types (records, tables, ...) how could iBatis?
The team is going to overload some functions and procedure in the PL/SQL packages to add their versions whose signatures will be based only on basic SQL types.

"Jeff Butler" <[EMAIL PROTECTED]>

"Jeff Butler" <[EMAIL PROTECTED]> 10/10/2006 16:32 Per favore, rispondere a user-java@ibatis.apache.org

Per user-java@ibatis.apache.org CC Oggetto Re: Stored procedures (package functions) returning a row

Try this:

<parameterMap id="parmMap" class="it.finmatica.gpj.aa.frontebd.ChiaveBean">
<parameter property="id" jdbcType="INTEGER" javaType="int" mode="OUT"/>
</parameterMap>

<procedure id="get_key_2" parameterMap="parmMap">
{ ? = call ut_Foo.get_key_2 }
</procedure>

Everything else the same.

Jeff Butler

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

[EMAIL PROTECTED] scritti il 06/10/2006 14:57:31

> It is not feasible for Oracle JDBC drivers to support calling
> arguments or return values of the PL/SQL types TABLE (now known as
> indexed-by tables), RESULT SET, RECORD, or BOOLEAN
> http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05


I followed the Jeff's suggestion to use update then call it with syntax is like {? = call myFunction}.

XML file config
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
   "http://ibatis.apache.org/dtd/sql-map-2.dtd"> 

<sqlMap namespace="GPJ">

<resultMap id="resMap" class="it.finmatica.gpj.aa.frontebd.ChiaveBean ">
<result property="id" column="tipo_dato_ID" jdbcType="INTEGER" javaType="int"/>
</resultMap>

<procedure id="get_key_2" resultMap="resMap">
{ ? = call ut_Foo.get_key_2 }
</procedure>

</sqlMap>

Java

       SqlMapClient lSqlMapClient = Configurazione.getSqlMapIstanza();

       ChiaveBean lChiaveBean = new ChiaveBean();
       try
       {
       lSqlMapClient.update( "get_key_2", lChiaveBean );
       }
       catch (SQLException pException)
       {
       pException.printStackTrace ();
       throw pException;
       }
       int lInt = lChiaveBean.getId();

ChiaveBean is a simple JavaBean class with a single property of int type anmed id and its setter/getter methods


PL/SQL
create or replace package ut_foo is

type t_PK is record
( tipo_dato_ID number( 10)
);

function get_key_2
return t_PK;
pragma restrict_references( get_key_2, WNDS );

[...]

generated SQL

[*] DEBUG [main] - {pstm-100001} PreparedStatement: { ? = call ut_Foo.get_key_2 }

Raises the following exception
com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred in it/finmatica/gpj/aa/frontebd/FooTest.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-01008: not all variables bound

Caused by: java.sql.SQLException: ORA-01008: not all variables bound

at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:500 )
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:85 )
[...]


What's wrong? Any suggestion?
There are around a number of references to the ORA-1008 error and it seems a quite irksome error.

For the second question (about learning iBatis) I will open a new thread

Regards