question Does ExecuteSQL processor allow to execute stored procedure ? in Support Questions

Does ExecuteSQL processor allow to execute stored procedure ?

shishir_saxena4 — Wed, 06 Apr 2016 19:02:18 GMT

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

aervits — Wed, 06 Apr 2016 19:16:13 GMT

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 ?

shishir_saxena4 — Fri, 08 Apr 2016 01:27:32 GMT

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 ?

aervits — Fri, 08 Apr 2016 01:31:07 GMT

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

shishir_saxena4 — Sun, 18 Aug 2019 12:09:19 GMT

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

aervits — Fri, 08 Apr 2016 10:44:27 GMT

awesome!!!

Re: Does ExecuteSQL processor allow to execute stored procedure ?

aervits — Sat, 09 Apr 2016 07:59:53 GMT

@Shishir Saxena check this out, you inspired this http://funnifi.blogspot.com/2016/04/sql-in-nifi-with-executescript.html

Re: Does ExecuteSQL processor allow to execute stored procedure ?

mashayekhi_m — Fri, 02 Dec 2016 00:19:24 GMT

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 ?

philo1314 — Tue, 11 Apr 2017 20:36:01 GMT

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 ?

mvbandara — Sun, 13 Aug 2017 16:13:09 GMT

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

Re: Does ExecuteSQL processor allow to execute stored procedure ?

gkp_shakeel — Mon, 19 Feb 2018 17:22:35 GMT

@Artem Ervits Do you have any sample groovy code which execute Stored Pprocedue with input and output parameter ?

Re: Does ExecuteSQL processor allow to execute stored procedure ?

gkp_shakeel — Thu, 22 Mar 2018 14:05:50 GMT

Solved using the Groovy Code.
Stored PROC using the following input and output.

in_track_id IN NUMBER,
in_username IN VARCHAR2,
in_course_no IN VARCHAR2,
out_details OUT SYS_REFCURSOR,
out_status_code OUT NUMBER,
out_status_desc OUT VARCHAR2

import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.Sql
import oracle.jdbc.OracleTypes
import java.sql.ResultSet
 
 
 
 
//DB Setting Start
//Harcoded connection string
//def sql = Sql.newInstance('jdbc:oracle:thin:@//ecuxxx.xxx.xxx.net:1526/dbSchema', 'UserID', 'UserPass$', 'oracle.jdbc.OracleDriver')
 
 
//Get the conncation string from NIFI conncation pool
def lookup = context.controllerServiceLookup
def dbServiceName = ConncationPool.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
    cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
 
 
def conn = lookup.getControllerService(dbcpServiceId).getConnection();
sql = Sql.newInstance(conn);
 
 
 
 
 
 
////Get the session values from Nifi flow Start 
def flowFile = session.get()
if(!flowFile) return
in_track_id = flowFile.getAttribute('body_track_id')
in_username =  flowFile.getAttribute('body_username')
in_course_no = flowFile.getAttribute('body_course_number')
////Get the session values from Nifi flow END
 
 
// special OutParameter for cursor type
OutParameter CURSOR_PARAMETER = new OutParameter() {
    public int getType() {
        return OracleTypes.CURSOR;
    }
};
def data = []
// Stored proc having 3 input and 3 out put, one out put having CURSOR datatype
String sqlString ="""{call <StoredPrcoName>(?, ?, ?, ?, ?, ?)}""";
// rs contains the result set of cursor and data values start Shakeel
def  status_desc
def  status_code
def  status_data
//def parametersList = ['20585', 'A3843UP', '00009934', CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR]; // testing purpose
def parametersList = [in_track_id, in_username, in_course_no, CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];
 
 
sql.call(sqlString, parametersList) {out_details, out_status_code,out_status_desc ->
    status_desc = out_status_desc
    status_code = out_status_code
    out_details.eachRow {
        data << it.toRowResult()
        status_data = data
    }
};
// rs contains the result set of cursor and data values end
 
 
//Set the session values start 
def attrMap = ['status_desc':status_desc, 'status_code':String.valueOf(status_code),'status_data':String.valueOf(status_data),'Conn':String.valueOf(conn)]
flowFile = session.putAllAttributes(flowFile, attrMap)
session.transfer(flowFile, REL_SUCCESS)

Re: Does ExecuteSQL processor allow to execute stored procedure ?

mike_bjc — Wed, 16 Jan 2019 00:50:42 GMT

I got error when trying SQLCMD using the following ExecuteProcess:

Anyone had the same issue?

Re: Does ExecuteSQL processor allow to execute stored procedure ?

ivaldez — Tue, 17 Sep 2019 16:16:53 GMT

Yes you can. This is the query i put in the "SQL select query" property to call the stored procedure:

EXEC [dbo].[usp_Service_Requests] @Service_request_num='${serviceRequestNumber}'

Re: Does ExecuteSQL processor allow to execute stored procedure ?

ro — Tue, 24 Sep 2019 10:01:44 GMT

What is your nifi version?

Re: Does ExecuteSQL processor allow to execute stored procedure ?

gdr — Wed, 11 Dec 2019 19:27:01 GMT

Hi all!!

Im noob but i can execute a oracle procedure into a package using PutSql Processor.

So enjoy it..

PutSql Processor configuration

Oracle Sentence

declare codigo NUMBER; mensaje VARCHAR2(500); begin pkg_name.prc_name(${block},${txseq},${item},codigo,mensaje); end;

Package

create or replace PACKAGE pkg_name is
PROCEDURE prc_name (
p_block IN NUMBER,
p_txseq IN NUMBER,
p_item IN NUMBER,
p_cod_error OUT NUMBER,
p_msj_error OUT VARCHAR2
);
end pkg_name;

Body

create or replace PACKAGE BODY pkg_name IS

PROCEDURE prc_name (
p_block IN NUMBER,
p_txseq IN NUMBER,
p_item IN NUMBER,
p_cod_error OUT NUMBER,
p_msj_error OUT VARCHAR2
) IS

v_id VARCHAR2(20);
v_tag VARCHAR2(20);
v_item_id VARCHAR2(20);
e_error EXCEPTION;
e_no_procesado EXCEPTION;
BEGIN
UPDATE paf_tablename paf
SET
paf.status = 'P',
paf.cod_err = 0,
paf.msg_err = 'Procesado'
WHERE
paf.block = p_block
AND paf.item = p_item
AND paf.txseq = p_txseq;

p_cod_error := 0;
p_msj_error := 'correcto';

EXCEPTION
WHEN OTHERS THEN

p_cod_error := 9000;
p_msj_error := 'Error al procesar bloque '
|| p_block
|| ' Transaccion '
|| p_txseq
|| ' item '
|| p_item
|| ' '
|| substr(sqlerrm, 1, 100);

END prc_name;

END pkg_name;

Sorry my english.

Re: Does ExecuteSQL processor allow to execute stored procedure ?

dominikschauer — Sat, 19 Jun 2021 00:39:29 GMT

The simplest solution at least for SQL Server is using the PuFile processor and simply putting your EXEC or EXECUTE statement in there. Look at my other answer here to see how to configure it and an example.

Re: Does ExecuteSQL processor allow to execute stored procedure ?

LejlaKM — Fri, 11 Feb 2022 08:11:02 GMT

Hi,

can you tell me how do you create your parameters (${block},${txseq},${item}) in processor before this one so that you can pass them to plsqp procedure? Can you give me whole flow because this solution is not working for me now...

Thank you 😄

Re: Does ExecuteSQL processor allow to execute stored procedure ?

gdr — Fri, 11 Feb 2022 12:42:20 GMT

In my case, these parameters are filtered after a call to a web service, so my solution won't work for you.
Can you show us how you're doing it?

Re: Does ExecuteSQL processor allow to execute stored procedure ?

LejlaKM — Fri, 11 Feb 2022 13:17:05 GMT

Hi,

I have two separate flow files (one for insert and one for delete) which works fine. And in the end i put Output port.

After that job is done I need to execute plsql procedure from destination database., so I made another process group with input port and another flow

Processor generate table fetch selecting max and min date from one table like this

And after that I need those two values to pass to plsql procedure and I don't know how.

Last processor putsql look like this

and when I have no parameter to pass all works fine, but I need those parameter to pas

I hope that you can help me with this. I am new in nifi as you can see