Re: Stored procedures (package functions) returning a row

[C . Zecca]

[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