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)