ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mikel sanchez" <mikel.sanchez...@gmail.com>
Subject Calling Stored Procedure with varray parameters of package scoped types
Date Thu, 06 Mar 2008 17:03:46 GMT
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;
/

Mime
View raw message