Support Questions

Find answers, ask questions, and share your expertise

StoreProcedure , send and recieve parameters

Explorer

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 ?

3 ACCEPTED SOLUTIONS

Super Collaborator

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.

 

 

View solution in original post

Explorer

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 solution in original post

Super Collaborator

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?

 

View solution in original post

3 REPLIES 3

Super Collaborator

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.

 

 

Explorer

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

Super Collaborator

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?

 

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.