Forum Discussion

markgholland's avatar
markgholland
Occasional Contributor
9 years ago

Caling a Stored Procedure in JDBC Test Step

Hello,

 

I'm trying to call a stored procedure via a JDBC Test Step to an Oracle 11g database.  I want to call the procedure and then evaluate the results.  Specifically, I don't know the syntax for the output parameter.

 

I've tried several ways, and I can't seem to get results.  The stored procedure has several inputs (10+) and a single output.  The documentation doesn't seem to be useful and I've been unsuccessful with finding a solution.

 

I've tried...

 

{call procedureName(parameter1 => value1, parameter2 => value2, parameter3 => value3, ... )}

 

 

Does the OUT parameter need to be included in the call?

 

Help would be appreciated!

 

Thanks,

Mark

  • markgholland's avatar
    markgholland
    Occasional Contributor
    9 years ago

    Does anyone have any suggestions?  I'd be open to use a groovy script as well if anyone has an example.

     

    A bit more information... the OUT parameter is a "REF CURSOR" type when the stored procedure is executed via SQL Developer.

  • markgholland's avatar
    markgholland
    Occasional Contributor
    9 years ago

    I'm digging this one back up as I was unsuccessful the first time and it was reprioritized, but I'd like to figure this out.  At this point, it doesn't look like it's possible to do via the JDBC Test step with the "stored procedure" flag on, but it looks like some folks have had some luck using Groovy.  Here is what I have so far:

     

     

     

    import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.OracleDriver")

//get environment specific(dev1, qa1, etc.) project db connection string properties
def host = context.expand( '${#Project#dbhost}' )
def port = context.expand( '${#Project#dbport}' )
def serviceName = context.expand( '${#Project#dbservicename}' )
def username = context.expand( '${#Project#dbusername}' )
def password = context.expand( '${#Project#dbpassword}' )
def schemaName = context.expand( '${#Project#db_scema_name}' )

    //build full stored procedure name, including the db schema
def procName = ( schemaName + ".STORED_PROCEDURE_NAME" )

//build connection string
conString = ("jdbc:oracle:thin:@" + host + ":" + port + "/" + serviceName)

//connect to db
def con = Sql.newInstance(conString, username, password, "oracle.jdbc.driver.OracleDriver")

//call stored procedure
con.call '{' + procName + '(PARAMETER1,PARAMETER2,?)}', [367,'en_US',Sql.REFCURSOR], { output -> log.info output}

    //TODO- do something with the output of the stored procedure
    
//close connection
con.close()

     

    I'm making the call to an Oracle 11g database, but I don't know how to work with the "Ref Cursor" datatype.  Any suggestions or working examples would be appreciated!