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
Contributor

Can you please tell me how it worked for you?

avatar
Contributor

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

avatar
Contributor

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)

avatar
New Contributor

I got error when trying SQLCMD using the following ExecuteProcess:

Anyone had the same issue?


nifi-exec-sql-proc.png

avatar
New Contributor

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


avatar
New Contributor

What is your nifi version?

avatar
Explorer

Hi all!!

 

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

So enjoy it..

PutSql Processor configuration

Anotación 2019-12-11 160051.pngOracle 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.

avatar

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.

 

avatar
Explorer

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 😄 

avatar
Explorer

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?