[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
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.
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 🙂
