Re: [eclipselink-users] Error when call a procedure which has an associa

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]

Re: [eclipselink-users] Error when call a procedure which has an associative array as an argument.

From: James Sutherland <jamesssss@xxxxxxxxx>
Date: Thu, 1 Sep 2011 06:53:14 -0700 (PDT)
Delivered-to: eclipselink-users@xxxxxxxxxxx
List-archive: <https://dev.eclipse.org/mailman/private/eclipselink-users>
List-help: <mailto:eclipselink-users-request@eclipse.org?subject=help>
List-subscribe: <https://dev.eclipse.org/mailman/listinfo/eclipselink-users>, <mailto:eclipselink-users-request@eclipse.org?subject=subscribe>
List-unsubscribe: <https://dev.eclipse.org/mailman/options/eclipselink-users>, <mailto:eclipselink-users-request@eclipse.org?subject=unsubscribe>

Do not create a class for the array, remove PlanoDetallePedidoArray and its
descriptor.  Just use an ArrayList for the array values.


Javier Restrepo Rendón wrote:
> 
> Hi,
> 
> I have a problem to call a procedure which has an associative array
> as an argument.
> In my project, Eclipselink 2.1.3 (included in TopLink) is used on
> WebLogic 10.1.5.
> 
> Could some help me?
> 
> The procedure is as follow.
> 
> CREATE OR REPLACE PACKAGE APPS.ORDER_CRUD AS
>     TYPE DetallePedidoRecord IS RECORD (
> 	 Id VARCHAR2 (100):= '',
> 	 OrdenCompra VARCHAR2 (100):= '',
> 	 ItemNo VARCHAR2 (100):= '',
> 	 Cantidad NUMBER (12, 4):= 0,
> 	 PrecioBruto NUMBER (12, 4):= 0,
> 	 DescuentoVolumen NUMBER (12, 4):= 0,
> 	 TipoArticulo VARCHAR2 (100):= ''
>     );
> 
>  TYPE DetallePedidoArray IS VARRAY (999) OF DetallePedidoRecord;
> 
>  PROCEDURE create_order (p_IdOrder OUT VARCHAR2,
> 	 p_Cliente IN VARCHAR2,
> 	 p_OrdenCompra IN NUMERIC,
> 	 p_FechaPedido IN DATE,
> 	 p_FechaConfirmacion IN DATE,
> 	 p_Comentario IN VARCHAR2,
> 	 p_TipoPedido IN VARCHAR2,
> 	 p_DetallePedido IN DetallePedidoArray);
> END ORDER_CRUD;
> 
> 
> Firstly I defined shadow types in the 'global' JDBC namespace outside
> of the PL/SQL package.
> 
> create or replace TYPE DetallePedidoRecord as object (
> 	Id VARCHAR2 (100),
> 	OrdenCompra VARCHAR2 (100),
> 	ItemNo VARCHAR2 (100),
> 	Cantidad NUMBER (12, 4),
> 	PrecioBruto NUMBER (12, 4),
> 	DescuentoVolumen NUMBER (12, 4),
> 	TipoArticulo VARCHAR2 (100)
> );
> 
> create or replace TYPE DetallePedidoArray IS VARRAY (999) OF
> DetallePedidoRecord;
> 
> 
> Secondly I make some Java class for mapping the PL/SQL record type and
> PL/SQL associative array.
> 
> public class PlanoDetallePedido implements Serializable {
>     private String id;
>     private String ordenCompra;
>     private String itemNo;
>     private Double cantidad;
>     private Double precioBruto;
>     private Double descuentoVolumen;
>     private String tipoArticulo;
> }
> 
> public class PlanoDetallePedidoArray implements Serializable {
>     private PlanoDetallePedido nest[];
> }
> 
> 
> Finally I implement Java method to call the procedure.
> 
> PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
> call.setProcedureName("ORDER_CRUD.create_order".toUpperCase());
> 
> call.addNamedOutputArgument("p_IdOrder".toUpperCase(),
> JDBCTypes.VARCHAR_TYPE);
> call.addNamedArgument("p_Cliente".toUpperCase(), JDBCTypes.VARCHAR_TYPE);
> call.addNamedArgument("p_OrdenCompra".toUpperCase(),
> JDBCTypes.VARCHAR_TYPE);
> call.addNamedArgument("p_FechaPedido".toUpperCase(), JDBCTypes.DATE_TYPE);
> call.addNamedArgument("p_FechaConfirmacion".toUpperCase(),
> JDBCTypes.DATE_TYPE);
> call.addNamedArgument("p_Comentario".toUpperCase(),
> JDBCTypes.VARCHAR_TYPE);
> call.addNamedArgument("p_TipoPedido".toUpperCase(),
> JDBCTypes.VARCHAR_TYPE);
> 
> PLSQLrecord nestRecord = new PLSQLrecord();
> nestRecord.setTypeName("ORDER_CRUD.DetallePedidoRecord".toUpperCase());
> nestRecord.setCompatibleType("DetallePedidoRecord".toUpperCase());
> nestRecord.setJavaType(PlanoDetallePedido.class);
> nestRecord.addField("Id".toUpperCase(), JDBCTypes.VARCHAR_TYPE, 100);
> nestRecord.addField("OrdenCompra".toUpperCase(), JDBCTypes.VARCHAR_TYPE,
> 100);
> nestRecord.addField("ItemNo".toUpperCase(), JDBCTypes.VARCHAR_TYPE, 100);
> nestRecord.addField("Cantidad".toUpperCase(), JDBCTypes.NUMERIC_TYPE, 12,
> 4);
> nestRecord.addField("PrecioBruto".toUpperCase(), JDBCTypes.NUMERIC_TYPE,
> 12, 4);
> nestRecord.addField("DescuentoVolumen".toUpperCase(),
> JDBCTypes.NUMERIC_TYPE, 12, 4);
> nestRecord.addField("Tipo".toUpperCase(), JDBCTypes.VARCHAR_TYPE, 100);
> 
> /*Descriptor for mapping nested Record*/
> ObjectRelationalDataTypeDescriptor nestRecordDesc = new
> ObjectRelationalDataTypeDescriptor();
> nestRecordDesc.descriptorIsAggregate();
> nestRecordDesc.setJavaClass(PlanoDetallePedido.class);
> nestRecordDesc.setAlias("NestedRecord");
> nestRecordDesc.setStructureName("DetallePedidoRecord".toUpperCase());
> DirectToFieldMapping valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("id");
> valueMapping.setFieldName("Id".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("ordenCompra");
> valueMapping.setFieldName("OrdenCompra".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("itemNo");
> valueMapping.setFieldName("ItemNo".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("cantidad");
> valueMapping.setFieldName("Cantidad".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("precioBruto");
> valueMapping.setFieldName("PrecioBruto".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("descuentoVolumen");
> valueMapping.setFieldName("DescuentoVolumen".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> valueMapping = new DirectToFieldMapping();
> valueMapping.setAttributeName("tipoArticulo");
> valueMapping.setFieldName("TipoArticulo".toUpperCase());
> nestRecordDesc.addMapping(valueMapping);
> getEntityManagerImpl().getServerSession().addDescriptor(nestRecordDesc);
> 
> PLSQLCollection inArray = new PLSQLCollection();
> inArray.setTypeName("ORDER_CRUD.DetallePedidoArray".toUpperCase());
> inArray.setCompatibleType("DetallePedidoArray".toUpperCase());
> inArray.setJavaType(PlanoDetallePedidoArray.class);
> inArray.setNestedType(nestRecord);
> 
> /*Descriptor for mapping Associative Array*/
> ObjectRelationalDataTypeDescriptor inArrayDesc = new
> ObjectRelationalDataTypeDescriptor();
> inArrayDesc.descriptorIsAggregate();
> inArrayDesc.setJavaClass(PlanoDetallePedidoArray.class);
> inArrayDesc.setStructureName("DetallePedidoArray".toUpperCase());
> inArrayDesc.setAlias("TableIndex");
> getEntityManagerImpl().getServerSession().addDescriptor(inArrayDesc);
> 
> call.addNamedArgument("p_DetallePedido".toUpperCase(), inArray);
> 
> ValueReadQuery query = new ValueReadQuery();
> query.addArgument("p_Cliente".toUpperCase());
> query.addArgument("p_OrdenCompra".toUpperCase());
> query.addArgument("p_FechaPedido".toUpperCase());
> query.addArgument("p_FechaConfirmacion".toUpperCase());
> query.addArgument("p_Comentario".toUpperCase());
> query.addArgument("p_TipoPedido".toUpperCase());
> query.addArgument("p_DetallePedido".toUpperCase(),
> PlanoDetallePedidoArray.class);
> 
> query.setCall(call);
> query.bindAllParameters();
> 
> List args = new ArrayList();
> args.add(pedido.getCliente());
> args.add(pedido.getOrdenCompra());
> args.add(new java.sql.Date(pedido.getFechaPedido().getTime()));
> args.add(new java.sql.Date(pedido.getFechaConfirmacion().getTime()));
> args.add(pedido.getComentario());
> args.add(pedido.getTipoPedido());
> 
> PlanoDetallePedidoArray arr = new PlanoDetallePedidoArray();
> arr.setNest(arrDetallePedido);
> args.add(arr);
> 
> EntityManagerImpl em = getEntityManagerImpl();
> ServerSession ss = em.getServerSession();
> Object obj = ss.executeQuery(query, args);
> 
> 
> But, I cannot call ther procedure because of this error, as follow.
> How can I do this?
> 
> Sorry for my poor English.
> best regards.
> 
> 
> *EJB Exception: : Local Exception Stack:
> Exception [EclipseLink-4002] (Eclipse Persistence Services -
> 2.1.3.v20110304-r9073):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLException: Invalid column type
> Error Code: 17004
> Call:
> DECLARE
>   P_CLIENTE_TARGET VARCHAR(255) := :1;
>   P_ORDENCOMPRA_TARGET VARCHAR(255) := :2;
>   P_FECHAPEDIDO_TARGET DATE := :3;
>   P_FECHACONFIRMACION_TARGET DATE := :4;
>   P_COMENTARIO_TARGET VARCHAR(255) := :5;
> 
>   P_TIPOPEDIDO_TARGET VARCHAR(255) := :6;
>   P_DETALLEPEDIDO_TARGET ORDER_CRUD.DETALLEPEDIDOARRAY;
>   P_DETALLEPEDIDO_COMPAT DETALLEPEDIDOARRAY := :7;
>   P_IDORDER_TARGET VARCHAR(255);
>   FUNCTION EL_SQL2PL_1(aSqlItem DETALLEPEDIDORECORD)
> 
>   RETURN ORDER_CRUD.DETALLEPEDIDORECORD IS
>     aPlsqlItem ORDER_CRUD.DETALLEPEDIDORECORD;
>   BEGIN
>     aPlsqlItem.ID := aSqlItem.ID;
>     aPlsqlItem.ORDENCOMPRA := aSqlItem.ORDENCOMPRA;
>     aPlsqlItem.ITEMNO := aSqlItem.ITEMNO;
> 
>     aPlsqlItem.CANTIDAD := aSqlItem.CANTIDAD;
>     aPlsqlItem.PRECIOBRUTO := aSqlItem.PRECIOBRUTO;
>     aPlsqlItem.DESCUENTOVOLUMEN := aSqlItem.DESCUENTOVOLUMEN;
>     aPlsqlItem.TIPO := aSqlItem.TIPO;
>     RETURN aPlsqlItem;
> 
>   END EL_SQL2PL_1;
>   FUNCTION EL_SQL2PL_3(aSqlItem DETALLEPEDIDOARRAY)
>   RETURN ORDER_CRUD.DETALLEPEDIDOARRAY IS
>     aPlsqlItem ORDER_CRUD.DETALLEPEDIDOARRAY;
>   BEGIN
>     IF aSqlItem.COUNT > 0 THEN
>       FOR I IN 1..aSqlItem.COUNT LOOP
> 
>         aPlsqlItem(I) := EL_SQL2PL_1(aSqlItem(I));
>       END LOOP;
>     END IF;
>     RETURN aPlsqlItem;
>   END EL_SQL2PL_3;
> BEGIN
>   P_DETALLEPEDIDO_TARGET := EL_SQL2PL_3(P_DETALLEPEDIDO_COMPAT);
>   ORDER_CRUD.CREATE_ORDER(P_IDORDER=>P_IDORDER_TARGET,
> P_CLIENTE=>P_CLIENTE_TARGET, P_ORDENCOMPRA=>P_ORDENCOMPRA_TARGET,
> P_FECHAPEDIDO=>P_FECHAPEDIDO_TARGET,
> P_FECHACONFIRMACION=>P_FECHACONFIRMACION_TARGET,
> P_COMENTARIO=>P_COMENTARIO_TARGET, P_TIPOPEDIDO=>P_TIPOPEDIDO_TARGET,
> P_DETALLEPEDIDO=>P_DETALLEPEDIDO_TARGET);
> 
>   :8 := P_IDORDER_TARGET;
> END;
>   bind => [:1 => string, :2 => 999, :3 => 1999-12-24, :4 =>
> 1999-12-24, :5 => string, :6 => string, :7 =>
> com.grupomun.entidades.PlanoDetallePedidoArray@2a796a, P_IDORDER =>
> :8]
> Query: ValueReadQuery()
>     at
> org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
> *
> 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance 
-- 
View this message in context: http://old.nabble.com/Error-when-call-a-procedure-which-has-an-associative-array-as-an-argument.-tp32275684p32379754.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

References:
- [eclipselink-users] Error when call a procedure which has an associative array as an argument.
  - From: Javier Restrepo Rendón

Prev by Date: Re: [eclipselink-users] SecondaryTable question
Next by Date: [eclipselink-users] custom sequence generator
Previous by thread: [eclipselink-users] Error when call a procedure which has an associative array as an argument.
Next by thread: [eclipselink-users] Distinguishing a @PreUpdate-due-to-flush() from "genuine" update?
Index(es):
- Date
- Thread

Breadcrumbs