Created 04-06-2016 12:02 PM
Can I execute stored procedure on database using ExecuteSQL proessor ? Is there any sample ?
Created 04-06-2016 12:16 PM
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?
Created 04-06-2016 12:16 PM
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?
Created 04-07-2016 06:27 PM
Thank You @Artem Ervits for your suggestion. I was able to call my stored proc using sqlplus and ExecuteProcess processor. Amazing community !
Created 04-07-2016 06:31 PM
Please post a screenshot, it's a great question.
Created on 04-08-2016 02:31 AM - edited 08-18-2019 05:09 AM
@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.
Created 04-08-2016 03:44 AM
awesome!!!
Created 04-09-2016 12:59 AM
@Shishir Saxena check this out, you inspired this http://funnifi.blogspot.com/2016/04/sql-in-nifi-with-executescript.html
Created 12-01-2016 04:19 PM
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.
Created 04-11-2017 01:36 PM
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
Created 08-13-2017 09:13 AM
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
Created 06-21-2022 03:50 AM
Can you please tell me how it worked for you?
Created 02-19-2018 09:22 AM
@Artem Ervits Do you have any sample groovy code which execute Stored Pprocedue with input and output parameter ?
Created 03-22-2018 07:05 AM
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)
Created 01-15-2019 04:50 PM
I got error when trying SQLCMD using the following ExecuteProcess:
Anyone had the same issue?
Created 09-17-2019 09:16 AM
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}'
Created 09-24-2019 03:01 AM
What is your nifi version?
Created on 12-11-2019 11:23 AM - edited 12-11-2019 11:27 AM
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.
Created 06-18-2021 05:39 PM
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.
Created 02-11-2022 12:11 AM
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 😄
Created 02-11-2022 04:42 AM
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?
Created 02-11-2022 05:17 AM
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