Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Does ExecuteSQL processor allow to execute stored procedure ?

avatar

Can I execute stored procedure on database using ExecuteSQL proessor ? Is there any sample ?

1 ACCEPTED SOLUTION

avatar
Master Mentor

I don't think so but maybe you can wrap the exec storedprocedure into a shell script or groovy and run it in ExecuteProcess/ExecuteScript?

View solution in original post

23 REPLIES 23

avatar
Master Mentor

I don't think so but maybe you can wrap the exec storedprocedure into a shell script or groovy and run it in ExecuteProcess/ExecuteScript?

avatar

Thank You @Artem Ervits for your suggestion. I was able to call my stored proc using sqlplus and ExecuteProcess processor. Amazing community !

avatar
Master Mentor

Please post a screenshot, it's a great question.

avatar

@Artem Ervits My database is Oracle, so I used sqlplus to execute a script. Where script has following content.

execute spname(param)
exit

I used ExecuteProcess processor to call sqlplus

Note: This approach assumes that client tools for database are installed on NiFi Server.

3284-executeprocess.jpg

avatar
Master Mentor

awesome!!!

avatar
Master Mentor

avatar

It was really helpful. I used the same method to run a stored procedure on MSSQL using SQLCMD :

SQLCMD  -S server_name -U user_name -P password -i sample.sql

and in sample.sql I put:

exec sp_name sp_parameters

Then I used ExecuteProcess to call SQLCMD along with its parameters.

avatar
New Contributor

Yes, you can.

Checking the source code, if you set "true" for generate key in "putSql", it will use executeUpdates rather than executeBatch(). It works if you don't need any output from your SP

avatar
New Contributor

I have tried calling mysql sp with putsql processor.Its worked for me.
my so doesn't return any values only does some insert operations.
thanks @Yilin Han