Skip to main content
anaran
anaran
Silver ⭐️⭐️
August 17, 2022
Question

Execute Oracle stored procedure returning a cursor without ExecuteScript processor

Hi guys:

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

4 replies

ckumar
Platinum ⭐️
ckumar
Platinum ⭐️
August 18, 2022

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/213365

 

Thank You.

Translate
anaran
anaranAuthor
Silver ⭐️⭐️
August 18, 2022

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.

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
August 18, 2022

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.

Translate
anaran
anaranAuthor
Silver ⭐️⭐️
August 22, 2022

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?

Translate
Terms & ConditionsAccessibility statement