stored procedure named out parameter

Viewed 1019

Trying to use named parameters for calls to Oracle stored procedures. The in parameters work fine but I can not seem to access the out parameters.

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 next example fails with 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 is no output from 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 any idea on how to access the out parameters when using named notation? More complex procedures do run, and they do get the 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;
1 Answers
Related