Does ExecuteSQL processor allow to execute stored procedure ?

[#1]

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

[#2 · Accepted solution]

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?

[#3]

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

[#4]

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

[#5]

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

[#6]

awesome!!!

[#7]

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

[#8]

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.

[#9]

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

[#10]

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

[#11]

Can you please tell me how it worked for you?

[#12]

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

[#13]

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)

[#14]

I got error when trying SQLCMD using the following ExecuteProcess:

Anyone had the same issue?

[#15]

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}'

[#16]

What is your nifi version?

[#17]

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.

[#18]

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.

[#19]

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 😄 

[#20]

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?

[#21]

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