I have to get the output of Oracle stored procedure in Nifi. I've tried PutSql with the following sql statement :
declare out VARCHAR2 ; begin PKG_TEST.P_TEST(1,out); end;
It works fine but it just executes the script. How can I get the value of output 'out' ? Edit : I tried the Groovy script here :
I get the following error :
2022-06-17 13:38:53,353 ERROR [Timer-Driven Process Thread-9] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=26ab18f1-3b0c-18cf-d90b-3d5904676458] groovy.lang.MissingMethodException: No signature of method: Script6a6d0a35$_run_closure1.doCall() is applicable for argument types: (String, String, java.sql.Date, null, String, null, null, String...) values: [xxxx, xxxx, 2022-05-30, null, OK, null, null, ...]: groovy.lang.MissingMethodException: No signature of method: Script6a6d0a35$_run_closure1.doCall() is applicable for argument types: (String, String, java.sql.Date, null, String, null, null, String...) values: [xxxx,xxxx, 2022-05-30, null, OK, null, null, ...]
at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:255)
So I have the output of procedure but I get the error !
Script :
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 java.sql.ResultSet
////Get the session values from Nifi flow Start
def flowFile = session.get()
if(!flowFile) return
String TYPE_NOTIFICATION = flowFile.getAttribute('TYPE_NOTIFICATION')
String ID_NOTIFICATION = flowFile.getAttribute('ID_NOTIFICATION')
////Get the session values from Nifi flow END
String sqlString ="""{call PKG_TEST.P_TEST(?,?,?,?,?,?,?,?,?,?,?)}""";
def parametersList = [ID_NOTIFICATION, TYPE_NOTIFICATION,Sql.VARCHAR,Sql.VARCHAR,Sql.DATE,Sql.VARCHAR,Sql.VARCHAR,Sql.VARCHAR,Sql.VARCHAR,Sql.VARCHAR,Sql.DATE ];
SQL.mydbxx.call(sqlString, parametersList) {out1, out2,...->
flowFile.putAttribute("out1",out1)...
};
session.transfer(flowFile, REL_SUCCESS)