Solved using the Groovy Code. Hope it will help someone.. 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)
... View more
