oracle-databasegroovyapache-nifi

How do I get more than one out parameter from a stored procedure in Groovy


I've been retrieving a single out parameter from Oracle stored procedures in Groovy (for context, in NiFi) for a while now like this:

import groovy.sql.SQL

def inParemter = 'send this value to the stored proc'
def errorCount = 0
def conn = (create connection to DB, details are inconsequential here)
def sql = Sql.newInstance(conn)
sql.call('{call MY_SCHEMA.MY_STOREDPROC(?, ?)}', [inParameter, Sql.INTEGER], { ec -> errorCount = ec })
conn.close()

My question is, what is the syntax for retrieving more than one out parameter? This is NOT it:

sql.call('{call MY_SCHEMA.MY_STOREDPROC(?, ?, ?)}', [inParameter, Sql.INTEGER, Sql.VARCHAR], { ec -> errorCount = ec }, { es -> errorString = es})

I've tried some other things, too. Done some Googling, gotten close, but no one is trying this exact thing. I've seen some examples that have:

...{ ec, es -> ... }

But not sure how to form the syntax for assigning ec and es to a Groovy variables.


Solution

  • You tried using two separate closures for each OUT parameter, Groovy syntax only allows one closure for all OUT parameters.

    // Incorrect:

    sql.call('{call ...}', [...], { ec -> ... }, { es -> ... }) // won't work
    

    Correct Example:

    sql.call('{call MY_SCHEMA.MY_STOREDPROC(?, ?, ?)}',
             [inParameter, Sql.INTEGER, Sql.VARCHAR],
             { ec, es ->
                 errorCount = ec
                 errorString = es
             })
    
    println "Error Count: $errorCount"
    println "Error String: $errorString"
    

    This works because the closure captures both OUT parameters (ec, es) in one go.