Oracle Stored Procedure with collection of objects as out parameter

One Star

Oracle Stored Procedure with collection of objects as out parameter

Hallo,
I'm using tOracleSP to call an Oracle Stored Procedure, but I can't figure out how to configure one of the parameters. The parameter in question is in Oracle defined as an OUT parameter, and its type is a collection of objects:
create or replace type object_type as object ...
create or replace type collection_type as table of object_type;
create or replace procedure myStoredProcedure
(P_TabOut out collection_type) ...
In Talend, I have tried every combination of Object or List as type in the Schema definition and OUT or RECORD SET as Schema Column type in the Parameters definition. With every combination I get a different error:
Schema type Object/Parameter Type RECORD SET: java.sql.SQLException: ORA-06550: Zeile 1, Spalte 7: PLS-00306: Falsche Anzahl oder Typen von Argumenten
Schema type Object/Parameter Type OUT: java.sql.SQLException: Ungültiger Spaltentyp: 1111
Schema type List/Parameter Type RECORD SET: Type mismatch: cannot convert from Object to List
Schema type List/Parameter Type OUT: The method getList(int) is undefined for the type CallableStatement
Is there an example how to map an SP parameter that is a collection of objects in Oracle to Talend? Does anyone know how to do it? Is it possible?
Thanks,
Florian.
One Star

Re: Oracle Stored Procedure with collection of objects as out parameter

Hm, in the meantime I've got a patch for a simple custom-defined object as an OUT parameter -a new feature-, which is a step in the direction I need to go. But my real-world requirement with a collection of objects as OUT parameter still remains unsolved.
Does anybody know what the schema column type "List" in tOracleSP is meant for?
If I select it with an OUT parameter, it generates a "getList" method call on a java.Sql.CallableStatement object, which is just nonsense because "The method getList(int) is undefined for the type CallableStatement".
One Star

Re: Oracle Stored Procedure with collection of objects as out parameter

... three months later ...
With the help from Talend development I've got a custom-defined solution for my table of Oracle custom-defined types.
However, there is a follow-up problem: VARCHAR2 fields which are part of the custom-defined type end up as three question marks ??? in Talend when the character set of the Oracle DB is WE8ISO8859P15.
Google recherche told me that character conversion inside tables of custom-defined objects is a special case. CharacterSetUnknown is rather strict and, instead of handling only special characters specially as I would have expected, the whole string is replaced by "???".
Is there anyone out there who has encountered this problem with Talend or elsewhere with JDBC? Is there an easy solution (like copying orai18n.jar to the right place)?
Six Stars

Re: Oracle Stored Procedure with collection of objects as out parameter

Hello,
any working progress here? In case I need a cursor returned from my tOracleSP component, I use then tParseRecordSet and iterate for each record, It is working, I just found one bug when cursor has collumn of CLOB data type, but it has been already fixed.
Now I want to use return type Oracle Collection. Does anyone did it in Talend?
Best regards,
Ladislav