You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Mikel sanchez <mi...@gmail.com> on 2008/03/06 18:03:46 UTC
Calling Stored Procedure with varray parameters of package scoped types
Hi
This is my first message to the list. I tried to be as clear as
possible. Thanks in advance for any help you can provide :)
QUESTION:
========
In short, ¿is it possible to call a stored procedure (inside package
"A") using parameters with types scoped in their own package "B"? how?
Long story:
I needed to call a stored procedure on Oracle with collections of UDT
(user defined types) as parameters, from Ibatis.
After some research I found:
http://www.mail-archive.com/user-java@ibatis.apache.org/msg10751.html
...
It worked for me (almost, because the ARRAY constructor didn't like a
java array[] as the third argument, so I had to change that from
EmpSalary[] to STRUCT[], but that's not the problem right now...)
The problem I have now, as the author from the message says, is that
"this doesn't work for package scoped types". I'll try to explain
myself.
FIRST, here are the data definitions:
- The types "EMPLEADO" and "ARRAY_EMPLEADOS" inside package "GENERAL" ...
CREATE OR REPLACE PACKAGE GENERAL AS
TYPE EMPLEADO IS RECORD (EMP_ID NUMBER(5), EMP_NAME VARCHAR2(255),
START_DATE DATE, SALARY NUMBER);
TYPE ARRAY_EMPLEADOS IS VARRAY(100000) OF EMPLEADO;
End GENERAL;
/
- ... and the stored procedure "PROCESAR" inside package "PROCEDIMIENTOS"
CREATE OR REPLACE PACKAGE PROCEDIMIENTOS AS
PROCEDURE PROCESAR(i_array IN GENERAL.ARRAY_EMPLEADOS,resultado OUT
GENERAL.ARRAY_EMPLEADOS);
End PROCEDIMIENTOS;
/
CREATE OR REPLACE PACKAGE BODY PROCEDIMIENTOS AS
PROCEDURE PROCESAR(i_array IN GENERAL.ARRAY_EMPLEADOS,resultado OUT
GENERAL.ARRAY_EMPLEADOS)
IS
empleado UNIUNI.GENERAL.EMPLEADO;
BEGIN
resultado := i_array;
FOR idx IN i_array.first()..i_array.last() LOOP
empleado := i_array(idx);
empleado.EMP_ID := empleado.EMP_ID * 10;
empleado.EMP_NAME := empleado.EMP_NAME || '_CHANGED';
empleado.SALARY := empleado.SALARY * 1.5;
resultado(idx) := empleado;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- handle errors here...
dbms_output.put_line('Error: '||substr(1,255,sqlerrm));
END PROCESAR;
END PROCEDIMIENTOS;
/
SECOND, here is the SQL Map for Ibatis ("UNIUNI" is database schema):
<?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="NamespacePrueba">
<typeAlias alias="empSalary" type="com.x.sislog.domain.amedida.EmpSalary" />
<typeAlias alias="empSalaryTypeHandler2"
type="com.x.sislog.persistencia.ibatis.customhandlers.EmpSalaryTypeHandlerCallback2"
/>
<parameterMap id="empSalaryParams2" class="map">
<parameter property="iArray"
jdbcType="UNIUNI.GENERAL.ARRAY_EMPLEADOS"
typeName="UNIUNI.GENERAL.ARRAY_EMPLEADOS"
typeHandler="empSalaryTypeHandler2" mode="IN" />
<parameter property="resultado"
jdbcType="UNIUNI.GENERAL.ARRAY_EMPLEADOS"
typeName="UNIUNI.GENERAL.ARRAY_EMPLEADOS"
typeHandler="empSalaryTypeHandler2" mode="OUT" />
</parameterMap>
<procedure id="getEmpSalariesList2" parameterMap="empSalaryParams2">
{call UNIUNI.PROCEDIMIENTOS.PROCESAR(?,?)}
</procedure>
</sqlMap>
And THIRD here is the custom type handler
"EmpSalaryTypeHandlerCallback2" (modified from the one of tnsilver,
the guy who wrote the list message):
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import org.apache.commons.dbcp.DelegatingConnection;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
import com.x.sislog.domain.amedida.EmpSalary;
public class EmpSalaryTypeHandlerCallback2 implements TypeHandlerCallback
{
private final Log log =
LogFactory.getLog(EmpSalaryTypeHandlerCallback2.class);
private static final String SCHEMA = "UNIUNI";
private static final String EMPLEADO = SCHEMA + "." + "GENERAL.EMPLEADO";
private static final String ARRAY_EMPLEADOS = SCHEMA + "." +
"GENERAL.ARRAY_EMPLEADOS";
/**
* If we need an OUT parameter of type OM.EMP_SALARY_TAB (e.g. our
* implementation of getResult will be called) we will need to modifyiBatis
* framework (v2.3.0) to allow access to JdbcTypeRegistry.setType(currently
* private -> should be public).
*
* If we only need to sen in a OM.EMP_SALARY_TAB - we do not need tomodify
* anything and the next static block is not required.
*/
static
{
JdbcTypeRegistry.setType(EMPLEADO, OracleTypes.STRUCT);
JdbcTypeRegistry.setType(ARRAY_EMPLEADOS, OracleTypes.ARRAY);
};
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException
{
log.info("calling setParameter...");
try
{
List empSalaries = (List) parameter;
// log.info("Converting list to array...");
/*
EmpSalary[] recArray = new EmpSalary[empSalaries.size()];
for (int i = 0; i < recArray.length; i++)
{
recArray[i] = (EmpSalary)empSalaries.get(i);
}
log.info("Converted list to array.");
*/
//setter.getPreparedStatement().getConnection();
Connection conn = setter.getPreparedStatement().getConnection();
if (conn instanceof DelegatingConnection)
{
DelegatingConnection dcon = (DelegatingConnection) conn;
conn = dcon.getInnermostDelegate();
}
//conn = (OracleConnection) conn;
STRUCT[] structArray = new STRUCT[empSalaries.size()];
StructDescriptor structDescriptor =
StructDescriptor.createDescriptor(EMPLEADO, conn);
for (int i = 0; i < structArray.length; i++)
{
Object[] propertiesArray = new Object[4];
propertiesArray[0] = ((EmpSalary)empSalaries.get(i)).getEmpid();
propertiesArray[1] = ((EmpSalary)empSalaries.get(i)).getEmpname();
//DATE date = new DATE(
((EmpSalary)empSalaries.get(i)).getStartdate() );
if ( ((EmpSalary)empSalaries.get(i)).getStartdate() != null )
{
Timestamp timestamp = new Timestamp(
((EmpSalary)empSalaries.get(i)).getStartdate().getTime() );
propertiesArray[2] = timestamp;
}
else
{
propertiesArray[2] = null;
}
propertiesArray[3] = ((EmpSalary)empSalaries.get(i)).getSalary();
structArray[i] = new STRUCT(structDescriptor, conn,
propertiesArray);
}
ArrayDescriptor arrayDescriptor =
ArrayDescriptor.createDescriptor(ARRAY_EMPLEADOS, conn);
ARRAY array = new ARRAY(arrayDescriptor, conn, structArray);
setter.setArray(array);
}
catch (SQLException sqle)
{
log.info("SQLException: " + sqle, sqle);
throw sqle;
}
}
public Object getResult(ResultGetter getter) throws SQLException
{
/*
String retorno = getter.getString();
return retorno;
*/
ARRAY array = (oracle.sql.ARRAY) getter.getArray();
ResultSet rs = array.getResultSet();
List empSalaries = new ArrayList();
while (rs != null && rs.next())
{
STRUCT struct = (STRUCT) rs.getObject(2);
Object[] attribs = struct.getAttributes();
EmpSalary empSalary = new EmpSalary();
empSalary.setEmpid( new
Integer(((java.math.BigDecimal)attribs[0]).intValue()) );
empSalary.setEmpname((String) attribs[1]);
empSalary.setStartdate((Date) attribs[2]);
empSalary.setSalary( new
Double(((java.math.BigDecimal)attribs[3]).doubleValue()) );
empSalaries.add(empSalary);
}
return empSalaries;
}
public Object valueOf(String arg0)
{
if (arg0 == null)
{
return new ArrayList();
}
return arg0;
}
}
PROBLEM:
========
After calling the procedure I get the following exception:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
Complete stack trace:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient
operation; uncategorized SQLException for SQL []; SQL state [null];
error code [17060];
--- The error occurred in
com/x/sislog/persistencia/ibatis/sqlmap/EMP_SALARY_SqlMap.xml.
--- The error occurred while executing query procedure.
--- Check the {call UNIUNI.PROCEDIMIENTOS.PROCESAR(?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"; nested
exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/x/sislog/persistencia/ibatis/sqlmap/EMP_SALARY_SqlMap.xml.
--- The error occurred while executing query procedure.
--- Check the {call UNIUNI.PROCEDIMIENTOS.PROCESAR(?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
Caused by: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
at com.x.sislog.persistencia.implementacion.EmpSalaryDAOImpl.getEmpSalariesList2(EmpSalaryDAOImpl.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy14.getEmpSalariesList2(Unknown Source)
at _test.Test.main(Test.java:67)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/x/sislog/persistencia/ibatis/sqlmap/EMP_SALARY_SqlMap.xml.
--- The error occurred while executing query procedure.
--- Check the {call UNIUNI.PROCEDIMIENTOS.PROCESAR(?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
Caused by: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
... 14 more
Caused by: java.sql.SQLException: Fallo al construir el descriptor:
Unable to resolve type: "UNIUNI.GENERAL.ARRAY_EMPLEADOS"
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:775)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:166)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:89)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:161)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:80)
at $Proxy21.registerOutParameter(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.registerOutputParameters(SqlExecutor.java:365)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:282)
at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 20 more
Exception in thread "main"
NOTE:
====
Tried the procedure on SQL Plus with the following code, and works perfect:
SET SERVEROUTPUT ON;
declare
empleado1 GENERAL.EMPLEADO;
empleado2 GENERAL.EMPLEADO;
lista GENERAL.ARRAY_EMPLEADOS;
retonno GENERAL.ARRAY_EMPLEADOS;
begin
empleado1.EMP_ID := 1;
empleado1.EMP_NAME := 'EMPLEADO1';
empleado1.START_DATE := null;
empleado1.SALARY := 0.5;
empleado2.EMP_ID := 2;
empleado2.EMP_NAME := 'EMPLEADO2';
empleado2.START_DATE := null;
empleado2.SALARY := 0.5;
lista := GENERAL.ARRAY_EMPLEADOS( empleado1, empleado2);
PROCEDIMIENTOS.PROCESAR( lista, retonno);
dbms_output.put_line( retonno(1).EMP_NAME);
end;
/