Execute Oracle stored procedure returning a cursor without ExecuteScript processor

[#1]

Hi guys:

It's possible to run Oracle stored proc that return a cursor as SP parameter using only NiFi processors?

[#2]

Running Stored procedures from NiFi sql based processors  is not possible however you can use following suggestion suggested at https://community.cloudera.com/t5/Support-Questions/Execute-Stored-Procedure-of-MSSQL-in-Nifi/m-p/21...

Thank You.

[#3]

Using ExecuteSQL you can run stored procedures I have used before. The matter is when the stored procedure use output parameters to return results. Is there any processor to convert those ref parameters to flowfiles? My environment involve an Oracle database with stored procedures returning data as output cursors.

[#4]

Hi,

In the ExecuteSQL or ExecuteSQLRecord you have Pre Query, Select Query and Post Query properties. In the Pre Query can you declare variables to store the output parameters, run the stored proc passing those parameter and then insert them into a temp table which then you can use the Select Query property to read the values from the temp table and that will translate to flowfiles. If you are using the ExecuteSQLRecord you can define in which format you want the flowfile in by defining the record writer property. Hope that helps.

[#5]

Hi @SAMSAL:

I was trying to implement your solution but I didn't find a way to define a SYS_REFCURSOR in Pre-Query statements. Can you share any working simple example using Oracle DBMS?