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!
