One Star

[resolved] How to get a Oracle REF CURSOR into Talend

I call a Oracle Stored procedure which has an out parameter of type REF CURSOR. For the call I use a tOracleStoredProcedure with type "Object" in the Schema column and type "RECORD SET" in the Parameters line. So far it seems to work.
Now I want to read the values from this cursor, and this where I got stuck trying, and can't find examples either.
If I log the flow directly I get something like
"oracle.jdbc.driver.OracleResultSetImpl@1402d5a"
which probably means, the object can't be logged directly.
I tried to use a tParseRecordSet, but I can't figue out how to use it correctly. It says
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
The method getObject(int) in the type ResultSet is not applicable for the arguments ()
Syntax error on tokens, delete these tokens
at playground.sporderstateshist_0_1.sporderstateshist.tJava_2Process(sporderstateshist.java:2390)
at playground.sporderstateshist_0_1.sporderstateshist.tJava_1Process(sporderstateshist.java:881)
at playground.sporderstateshist_0_1.sporderstateshist.tOracleConnection_4Process(sporderstateshist.java:405)
In the generated Java Code I see the line 2390:
row6.StatCd = re_tParseRecordSet_1.getObject(*** ERROR in Table ***);
which doesn't look like correct Java syntax, at least to a C++ programmer like me.
Is there something fundfamentally wrong with my approach?
Are there know bugs in the version I'm using - 2.4.1.r16077?
Can someone point me to an example how to tParseRecordSet is meant to be used?
Would be thankful for any tips, or general expressions of sympathy,
Florian
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Hi,
What I am looking for is, I do not want to specify the mapping. Whatever columns my cursor returns I want to dump that to a file.
Any thoughts ?

You have to define the mapping in the schema, it does't support a dynamic schema on tParseRecordSet component.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
6 REPLIES
Community Manager

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Hello
see a demo about tParseRecordSet, 7942.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Hi,
cool, now it works!
Step 3, mapping the column names in the value column of the attribute table, was the part I was missing.
Thanks a lot,
Florian
Six Stars

Re: [resolved] How to get a Oracle REF CURSOR into Talend

In my current development project based on Oracle database we decided to deny any direct access to database store. Every I/O is done by database API, exactly functions packaged in packages, so tOracleSP and tParseRecord is my key component....
But back to playing with tOracleSP. Of course I also missed the mapping the real columns :-), thought that it will list all data from cursor automatically to the output stream, heh, but when I saw in console something like "oracle.jdbc.driver.OracleResultSetImpl@5f8172|0|0||||", at the end are 4 empty collumns and the cursor also returns 4 columns, I said to myself, hey, you need to tell Talend names of columns returned from columns. And now everything works perfectly.
From my point of view there are 2 mandatory thing you always need to do:
1. On the Stored procedure component for your selected database always set the returned cursor to be stored to Talend (Java) data type Object.
2. set up mapping real columns name returned in cursor filling these names into column's Value on the tParseRecordSet component
And thats it.
I still sometimes think in other way then developers of Talend :-) and this my behavior always make me trouble, on the other hand, when I solve some step (understand how to do that) in development, I always really enjoy this amazing product.
One Star

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Hi,
What I am looking for is, I do not want to specify the mapping. Whatever columns my cursor returns I want to dump that to a file.
Any thoughts ?
Community Manager

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Hi,
What I am looking for is, I do not want to specify the mapping. Whatever columns my cursor returns I want to dump that to a file.
Any thoughts ?

You have to define the mapping in the schema, it does't support a dynamic schema on tParseRecordSet component.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: [resolved] How to get a Oracle REF CURSOR into Talend

Still I think you can override this by writing generic java routine on your own, but the component doesn't support it, for now. You can request the new feature :-)