0

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 :

https://community.cloudera.com/t5/Support-Questions/Does-ExecuteSQL-processor-allow-to-execute-stored-procedure/td-p/158922

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)

Signature of my stored procedure : enter image description here enter image description here Thank you!

KnowledgeSeeker
  • 77
  • 1
  • 12
  • I tried it but it didn't change anything . How am I supposed to use that variable later ? thank you – KnowledgeSeeker May 23 '22 at 13:07
  • 2
    it's not possible yet to get output parameters in nifi for oracle database. https://community.cloudera.com/t5/Support-Questions/I-am-Trying-to-invoke-a-mysql-stored-procedure-in-nifi-How/td-p/196506 You could use groovyscript. some examples here: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-groovyx-nar/1.16.1/org.apache.nifi.processors.groovyx.ExecuteGroovyScript/index.html – daggett May 23 '22 at 13:22
  • as soon as you testing approach `PKG_TEST.P_TEST` - you can use oracle table function instead of procedure and return output value through resultset. – daggett May 23 '22 at 13:27
  • Unfortunately it's an external database, I have to use that procedure 7. I think that the documentation ofputsql/exeutesql is incomplete when it comes to plsql... I will use GroovyScript thx. – KnowledgeSeeker May 23 '22 at 14:06

1 Answers1

0

i can't test it - so, it's just a reference code

use ExecuteGroovyScript processor

add SQL.mydb parameter on the level of processor and link it to required DBCP pool.

set approximately this as a script body

def ff=session.get()
if(!ff)return

def statement = '''
  declare out VARCHAR2; 
  begin
    PKG_TEST.P_TEST(?, out);
  end;
  ? := out;
'''
//parameters for each ? placeholder
def params = [
  ff.param_input as Long, //get parameter value from flowfile attribute
  SQL.mydb.VARCHAR, //out varchar parameter https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#VARCHAR
]
SQL.mydb.call(statement, params){p_out-> //we have only one out patameter
    //closure to process output parameters
    ff.param_output = p_out //assign value into flowfile attribute
}

//transfer flowfile to success
REL_SUCCESS << ff
daggett
  • 26,404
  • 3
  • 40
  • 56
  • I added my script to the post , can you check it please ? in the error message I have the output of my stored procedure so the call is OK but I have an error ! thx – KnowledgeSeeker Jun 20 '22 at 08:46
  • 1. what is the line number in error? 2. seems something wrong with `SQL.mydbxx.call(){}` - please show how it really looks – daggett Jun 20 '22 at 10:36
  • also i'm not sure all the parameters that you have provided as output in groovy declared as output in procedure - that's the most realistic issue. – daggett Jun 20 '22 at 10:45
  • yes they are all outputs (see image in the post) . – KnowledgeSeeker Jun 20 '22 at 12:17
  • I tried : SQL.mydbxx.call(sqlString, parametersList) and I don't have the error, the problem is that when I iterate over the parametersList, I can't convert Sql.VARCHAR to String, so I'm unable to add the outputs as attributes to the flowfile . That's why I try to use the other method : call(String sql, List params, Closure closure) I don't have a line number in error message (error stack in the post) but the error is in the closure – KnowledgeSeeker Jun 20 '22 at 12:21
  • 1
    you have mess with number of parameters. count of `?` in statement must correspond to number of objects in parameter list, and count of OUT parameters in stored procedure must correspond to number of parameters in closure. i don't see your real code - so i can't compare what's wrong. – daggett Jun 23 '22 at 09:32
  • Thanks a lot . I had the wrong number of arguments ><' it works fine now – KnowledgeSeeker Jun 23 '22 at 12:35