StoreProcedure , send and recieve parameters

[#1]

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 ?

[#2 · Accepted solution]

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.

[#3 · Accepted solution]

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.?

[#4 · Accepted solution]

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.

[#5 · Accepted solution]

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.?

[#6 · Accepted solution]

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?