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/[email protected]/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; /
