question Re: StoreProcedure , send and recieve parameters in Support Questions

StoreProcedure , send and recieve parameters

PepeClaro — Thu, 22 Sep 2022 19:51:47 GMT

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 ?

Re: StoreProcedure , send and recieve parameters

SAMSAL — Thu, 22 Sep 2022 22:06:57 GMT

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.

Re: StoreProcedure , send and recieve parameters

PepeClaro — Fri, 23 Sep 2022 15:47:30 GMT

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

Re: StoreProcedure , send and recieve parameters

SAMSAL — Fri, 23 Sep 2022 17:51:20 GMT

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?