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.
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.