[eclipselink-users] Error when call a procedure which has an associative

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

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

From: Javier Restrepo Rendón <javierrestreporendon@xxxxxxxxx>
Date: Tue, 16 Aug 2011 17:21:00 -0500
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>

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<Object> args = new ArrayList<Object>();
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)

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

Prev by Date: [eclipselink-users] Complicated MappedSuperclass/AccessType question
Next by Date: Re: [eclipselink-users] Complicated MappedSuperclass/AccessType question
Previous by thread: [eclipselink-users] Complicated MappedSuperclass/AccessType question
Next by thread: Re: [eclipselink-users] Error when call a procedure which has an associative array as an argument.
Index(es):
- Date
- Thread

Breadcrumbs