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, 10 Oct 2006 10:27:52 GMT

C.Zecca@ads.it 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
Mime
View raw message