Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Does ExecuteSQL processor allow to execute stored procedure ?

Solved Go to solution
Highlighted

Does ExecuteSQL processor allow to execute stored procedure ?

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Does ExecuteSQL processor allow to execute stored procedure ?

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?

15 REPLIES 15

Re: Does ExecuteSQL processor allow to execute stored procedure ?

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?

Re: Does ExecuteSQL processor allow to execute stored procedure ?

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

Mentor

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

@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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

Mentor

awesome!!!

Re: Does ExecuteSQL processor allow to execute stored procedure ?

Mentor

Re: Does ExecuteSQL processor allow to execute stored procedure ?

New Contributor

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.

Re: Does ExecuteSQL processor allow to execute stored procedure ?

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

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

Don't have an account?
Coming from Hortonworks? Activate your account here