Does ExecuteSQL processor allow to execute stored procedure ?

[#1]

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

[#2]

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)

[#3]

I got error when trying SQLCMD using the following ExecuteProcess:

Anyone had the same issue?

[#4]

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

[#5]

What is your nifi version?

[#6]

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.