Integration

Hi all,
I have problem with call stored procedure, which returns rowtype.
I have this package in PL/SQL:

CREATE OR REPLACE PACKAGE BODY TEST_REC IS

-- Function and procedure implementations

PROCEDURE GET_ONE_TABLE_RECORD_PROC
+(+
a_prod_id IN BM_PRODUCT.BM_P_PROD_ID%TYPE,
a_record OUT BM_PRODUCT%ROWTYPE
+) IS+
BEGIN
-- get record from table
SELECT * INTO a_record FROM bm_product WHERE bm_p_prod_id = a_prod_id;

END GET_ONE_TABLE_RECORD_PROC;

PROCEDURE GET_ALL_TABLE_RECORDS_PROC(a_recordset OUT t_cursor) IS
BEGIN

-- get all records from table (open cursor)
OPEN a_recordset FOR
SELECT * FROM bm_product;

END GET_ALL_TABLE_RECORDS_PROC;

END TEST_REC;

And, If I call GET_ALL_TABLE_RECORDS_PROC everything is fine. I use this code:

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("TEST_REC.GET_ALL_TABLE_RECORDS_PROC");
call.useNamedCursorOutputAsResultSet("a_recordset");
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(BMProduct.class);
query.setCall(call);
listBmProduct = (Collection<BMProduct>) session.executeQuery(query);

But, I can call GET_ONE_TABLE_RECORD_PROC procedure.

I use this code:

StoredProcedureCall call = new StoredProcedureCall();
call.setReturnsResultSet(true);
call.setProcedureName("TEST_REC.GET_ONE_TABLE_RECORD_PROC");
call.addNamedArgumentValue("a_prod_id", id); // input
call.addNamedOutputArgument(
+"a_record", // procedure parameter name+
+"a_record", // out argument field name+
BMProduct.class+
+);+
ReadObjectQuery query = new ReadObjectQuery();
query.setReferenceClass(BMProduct.class);*
query.setCall(call);
+ +
BMProduct bmProduct = (BMProduct) session.executeQuery(query);

where BMProduct.class is class representation bm_product from db.

But it returns this Exception:
Exception [TOPLINK-4002] (Oracle TopLink - 11g (11.1.1.0.0) (Build 080909)): oracle.toplink.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-06550: row 1, column 7: PLS-00306: wrong number or types of arguments in call 'GET_ONE_TABLE_RECORD_PROC' ORA-06550: row 1, column 7: PL/SQL: Statement ignored Error Code: 6550 Call: BEGIN TEST_REC.GET_ONE_TABLE_RECORD_PROC(a_prod_id=>?, a_record=>?); END; bind => *[0, => a_record]* Query: ReadObjectQuery(toplink.model.entity.BMProduct)

Any idea, how i fixed this problem? I think this is in line:
call.addNamedOutputArgument(
+"a_record", // procedure parameter name+
+"a_record", // out argument field name+
BMProduct.class // this...*
+);+

But I don't now, how class type use? Or is another way for the procedure call returning rowtype?

Thanks advance!
Best regards

Edited by: KLD on Nov 20, 2008 7:44 AM

Edited by: KLD on Nov 20, 2008 7:48 AM