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;