[resolved] Capturing data using RETURNING clause of an UPDATE stmnt (tOracleRow)

[Anonymous]

I need to perform a SQL update to a row in a metadata table, in my job process (NOT within the data pipeline). I have done this using tOracleRow in the past.
HOwever, in addition, I need to return a couple of values back to Talend, as a result of the SQL update to that row in the table. These are SQL%ROWCOUNT (oracle reserved word that returns a count of records affected by the DML statement), and MaxRowsToExtract (to throttle the extraction process)
Example:
UPDATE ETLProcess SET IsRunningFlag = 1
WHERE ProcessName = 'DimCustomer'
and IsRunningFlag = 0
RETURNING SQL%ROWCOUNT into :IsRowUpdated, MaxRowsToProcess into :MaxRows
Assuming tOracleRow (or another option), how can I get these back into Talend variables?
Dave

[Anonymous]

Hello Dave
Create a procedure to process your request on tOracleRow, and then run the procedure on tOracleSP which you get the output parameters and pass them into Talend vars.
Best regards
shong

[Anonymous · Accepted solution]

Hello Dave
Create a procedure to process your request on tOracleRow, and then run the procedure on tOracleSP which you get the output parameters and pass them into Talend vars.
Best regards
shong

One requirement is no stored procs - has to be a more portable solution, with SQL only.
Here is what I did:
A (1) tOracle Connection, then a (2) tOracle Row with a (3) SELECT for UPDATE (locks the record, whe available for lock) into a (4) tLoadContext, then a conditional branch to an Exit (and tOracleRollback) or a (5) tOracleRow for an UPDATE, then a (6) tOracleCommit.
That worked.
It can definatly be argued that my solution is actually LESS portable due to all the Oracle specific components

Thanks,
Dave