A stored parameter called out out

Question

An attempt to use named parameters for calls to Oracle stored procedures. The in parameters work fine, but I cannot access the out parameters.

The code for the test stored procedure is at the bottom of the page.

/* imports and sql connection */
import groovy.util.GroovyTestCase
import groovy.sql.Sql
import lib.*
import java.sql.*
import oracle.jdbc.*

def sql;

this.class.classLoader.rootLoader.addURL(new URL("file:ojdbc7.jar"))
sql= Sql.newInstance ("jdbc:oracle:thin:@......", "..user..", "..password..")

The following example works fine using? placeholders

/*** Example 1 ***/
/* Works fine using ? placeholders */

def greeting

println "**** Example 1 ****\n"

sql.call "{call groovy_test(?,?)}",
    [ 'Johny', Sql.VARCHAR ],
    { dbgreeting -> greeting = dbgreeting }

    println "greeting: " + greeting

/* Outputs: greeting: hello Johny /*

The following example does not work with an invalid column type

/*** Example 2 ***/

def greeting2

println "\n**** Example 2 ****\n"

sql.call "{call groovy_test(:myname,:greeting)}",
    [ myname:'Johny', greeting:Sql.VARCHAR ],
    { dbgreeting -> greeting2 = dbgreeting }

    println "greeting: " + greeting    

/* Error
groovy.sql.Sql call
WARNING: Failed to execute: {call groovy_test(:myname,:greeting)} because: Invalid column type
Caught: java.sql.SQLException: Invalid column type
*/  

There are no results in the following example.

/*** Exampl 3 ***/

def greeting3

println "\n**** Example 3 ****\n"

sql.execute "{call groovy_test(:myname,:greeting)}",
    [ myname:'Johny', greeting:Sql.VARCHAR ],
    { dbgreeting -> greeting3 = dbgreeting }

println "Test3 greeting: " + greeting3

/* Output: greeting: null */

Does anyone have an idea on how to access out parameters when using a name with a name? More complex procedures are executed, and they get input values. This has been verified in the table data.

Stored Procedure Code:

create procedure groovy_test( myname varchar2, greeting out varchar2 )
as
begin

  greeting := 'hello ' || myname;

end;

Answer 1

groovy, FWIW...

.. Sql.VARCHAR , String. . Sql.LONGVARCHAR

Oracle varchar2 4000 . Sql.VARCHAR , Sql.LONGVARCHAR .

   sql.execute "{ groovy_test (: myname,: greeting)}",    ['Johny', Sql.VARCHAR],    { → 3 = }

, ?

Ref: SQL Groovy