How to use jmeter to test an Oracle Stored Procedure with sys_refcursor return type?

Question

I want to test an Oracle Stored Procedure by using jmeter.I have done everything but parameters.

And here is my SQL Query:

declare outinfo varchar2(20); outtable sys_refcursor; begin {call RK_JSCX(?,?)}; end;

The outtable in Oracle is a cursor.And i used resultSet to contain it in java.However,whatever i set in parameter types ,it said invalid type.

Sample Start: 2012-10-25 16:06:41 CST Load time: 0 Latency: 0 Size in bytes: 25 Headers size in bytes: 0 Body size in bytes: 25 Sample Count: 1 Error Count: 1 Response code: null 0 Response message: java.sql.SQLException: Invalid data type: cursor

Response headers: oracle.jdbc.driver.T4CConnection@58ba09

SampleResult fields: ContentType: text/plain DataEncoding: UTF-8

How can fix it? Thanks!

Here is my code in java:

public String RK_JSCX() throws Exception {

    RK_JSCX_Response response = null;
    List<RK_JSCX_Outtable> list = null;
    Connection con = null;
    CallableStatement cs = null;
    ResultSet rs = null;
    String sql = null; 
    try {
        sql = "{call RK_JSCX(?,?)}";
        con = ConnectionUtils.getInstance().getConnect();

        if (con.isClosed()) {
            throw new IllegalStateException("ERROR.THE   CONNECTION   ISCLOSED");
        }

        cs = con.prepareCall(sql);
        cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
        cs.registerOutParameter(2, Types.VARCHAR);

        cs.execute();

        rs = (ResultSet) cs.getObject(1);
        list = new ArrayList<RK_JSCX_Outtable>();
        while (rs.next()) {

            RK_JSCX_Outtable out = new RK_JSCX_Outtable(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6));

            list.add(out);
        }
        String outInfo = cs.getString(2);
        response = new RK_JSCX_Response(list, outInfo);

    } catch (SQLException e) {

        e.printStackTrace();

    } catch (Exception e) {
        e.printStackTrace();

    }finally {

        try {
            if (rs != null) {
                rs.close();
                if (cs != null) {
                    cs.close();
                }
                if (con != null) {
                    con.close();
                }
            }
        } catch (SQLException e) {

            System.out.println("Exception2");
            e.printStackTrace();
        }
    }
    return JSON.toJSONString(response);
}

Answer 1

This is how to do it:

• SQL Query : call RK_JSCX(?,?)

• Parameter values : OUT, OUT

• Parameter types : OUT -10,OUT VARCHAR

  -10 being the int value of OracleTypes.CURSOR

• Variable names: cursor, outInfo

   Names are what you want
  

JMeter allows using more types than java.sql.Types constants, in this case instead of using Constant names, you use integer values of constants.

Documentation has been clarified (in next JMeter version) , see:

• https://issues.apache.org/bugzilla/show_bug.cgi?id=54048