Skip to main content
PepeClaro
PepeClaro
Gold ⭐️
September 22, 2022
Solved

StoreProcedure , send and recieve parameters

Hello everyone

I am using PutSql processor and I need to execute a Procedure from the Oracle database, I has used the follow code in the SQL Statement:

declare

inputvar varchar2(20) := "1";

outputvar varchar2(20) ;
begin
MyPKG.MyStProcedure( inputvar, outputvar);
end;

My questions are: 1) How can I recieve the input parameter and pass to it my code? and 2) How can I send the output parameter to next queue o some attribute ?

PepeClaro
PepeClaroAnswer
Gold ⭐️
September 23, 2022

Thanks a lot for your answer and time. But, my question is about there is another method I can resolve this problem, maybe I can use a special processor o I can use another sentences of PL/SQL.?

View in thread

3 replies

SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
September 22, 2022

Hi,

 

This question has been asked before several times as you can see in the links below. your best option is to use an ExecuteScript processor and add the output as an attribute to the incoming flowfile.

https://stackoverflow.com/questions/72348844/get-output-of-stored-procedure-using-putsql-in-nifi

https://stackoverflow.com/questions/57359497/in-nifi-groovy-script-how-to-execute-stored-procedure

http://funnifi.blogspot.com/2016/04/sql-in-nifi-with-executescript.html

Hope that helps.

 

 

Translate
PepeClaro
PepeClaroAuthorAnswer
Gold ⭐️
September 23, 2022

Thanks a lot for your answer and time. But, my question is about there is another method I can resolve this problem, maybe I can use a special processor o I can use another sentences of PL/SQL.?

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
September 23, 2022

I dont think there is an out of the box processor where you can utilize such thing. However you can do some workaround where you can use the ExecuteSQL processor instead since this processor allows you to return the stored proc output in Avro format in new flowfile based on whatever your select statement is in the ExecuteSQL SQL Select Query property. Since this will generate new flowfile, the assumption here is that you dont care about the original flowfile.  before going farther and give you an example how to do it, do you want to preserve the original flow file and you were thinking of adding the stored proc output as attribute?

 

Translate
Terms & ConditionsAccessibility statement