Re: Stored procedures (package functions) returning a row

Jeff Butler Tue, 10 Oct 2006 07:33:21 -0700

Try this:

<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

Re: Stored procedures (package functions) returning a row

Reply via email to