Readable output from Oracle procedure that returns a cursor in JMeter

Question

I have a procedure that takes in one VARCHAR parameter and returns an OUT CURSOR which is a list, in this case 3 rows with 9 columns.

My current JDBC Request:

CALL foo.bar.procedure('123456', ?)

• Query type: Callable Statement
• Parameter values: OUT
• Parameter types: OUT -10
• Variable names: outList
• Result variable name: resultList (this is always null, does it mean that the result is empty?)
• Handle ResultSet: Store as Object

Response data:

-1 updates.
Output variables by position:
[1] oracle.jdbc.driver.OracleResultSetImpl@21512d0b

outList is now oracle.jdbc.driver.OracleResultSetImpl@21512d0b

I've tried getting some information from outList in a BeanShell sampler like is suggested here but I haven't been able to get anything from it except just the ResultSet object id.

I'm new to calling procedures through Jmeter, View Result Tree doesn't seem to show me any result data and I've been going back and forth with the SQL Query and the parameter values, trying to fix it, but I always have the same trouble with the output.

I've also tried something like this with similar settings:

DECLARE
    refCursor sys_refcursor;
    Type MyRec Is Record (
       v1    varchar2(
       v2    varchar2(50),
       ...
       v13    varchar2(10));
   rec MyRec;   
BEGIN
    foo.bar.procedure('123456',refCursor);
    LOOP
        FETCH refCursor INTO rec;
        EXIT WHEN refCursor%NOTFOUND;
        dbms_output.put_line(
         rec.v1||','||
         rec.v2||','||
         ...
         rec.v13);
   END LOOP;
END;

Am I calling the procedure correctly or is something missing in the JDBC Request settings?

Answer 1

I finally solved this by moving away from JDBC Request sampler and used the BeanShell Sampler instead.

import java.sql.*;
import oracle.jdbc.*;
import org.apache.jmeter.protocol.jdbc.config.DataSourceElement;

ResultSet rs = null;
ResultSetMetaData rsmd = null;
CallableStatement stmt;

// "myConnConfigName" is the 'JDBC Connection Configuration' variable name
Connection conn = DataSourceElement.getConnection("myConnConfigName");

try {
    stmt = conn.prepareCall("CALL foo.bar.procedure(?,?)");
    stmt.setString(1, "123456");
    stmt.registerOutParameter(2, OracleTypes.CURSOR);
    stmt.executeUpdate();

    rs = (ResultSet) stmt.getObject(2);

    while (rs.next()) {
        rsmd = rs.getMetaData();

        log.info("ColumnCount:" + rsmd.getColumnCount().toString());
        log.info("RowNo:" + rs.getRow().toString());

        // TODO: Store data.
        //       Loop through columns with rs.getString(i);
    }
}
catch(Throwable ex) {
    log.error("Error message: ", ex);
    throw ex;
}
finally {
    if (rs != null) {
        rs.close();
    }
    if (stmt != null) {
        stmt.close();
    }
    if (conn != null) {
        conn.close();
    }
}

Answer 2

1. Add JSR223 PostProcessor as a child of your request

2. Extract the "interesting" values from the OracleResultSetImpl as per Retrieving and Modifying Values from Result Sets article, something like:

   import java.sql.ResultSet
   
   ResultSet rs = (ResultSet)vars.getObject('outList')
   
   while (rs.next()) {            
       vars.put('column_1_row_1_value`, rs.getString(0)) 
       ///...
   }
   

See JDBC Tutorial and Apache Groovy - Why and How You Should Use It for more details.