4

I would like to know about how to pass array value in the jMeter for oracle stored procedure.

Below details setup already but which is not working.Please help me on this.

Oracle PL/SQL:

PROCEDURE Get_User(
   p_input1                     IN  VARCHAR2,
   p_input2                     IN  VARCHAR2,
   p_input3                     IN  VARCHAR2,
   p_input4                     IN  SCHEMA.TABLE1.COLUMN1%TYPE,
   arr_user_names               IN  SCHEMA2.CUSTOM_TYPE_TABLE,
   p_user_name_out              OUT VARCHAR2,
   p_address_out                OUT SCHEMA.TABLE1.COLUMN1%TYPE,
   arr_result_set               OUT SYS_REFCURSOR);


create or replace type CUSTOM_TYPE as object( name VARCHAR2(30),salary  NUMBER(5,2));

create or replace type CUSTOM_TYPE_TABLE is table of CUSTOM_TYPE;

Query:

call SCHEMA1.PKG1.Get_User(?,?,?,?,?,?,?,?)

Parameter Value:

INVAL1,INVAL3,INVAL3,INVAL4,'users':[{'name':'all'}],OUT,OUT,OUT

Parameter Type:

VARCHAR,VARCHAR,VARCHAR,VARCHAR,ARRAY,OUT VARCHAR,OUT VARCHAR,OUT -10

Variables Name:

p_input1,p_input2,p_input3,p_input4,arr_user_names,p_user_name_out,p_address_out,arr_result_set

ResultSet variable name:

p_user_name_out,p_address_out,arr_result_set

Error: Response message: java.sql.SQLException: Fail to convert to internal representation: 'users':[{'name':'all'}

sunleo
  • 10,589
  • 35
  • 116
  • 196

1 Answers1

3

Looking into AbstractJDBCTestElement.setArgument() function I fail to see Types.Array clause so my expectation is that you not be able to implement this using built-in JMeter JDBC Test Elements.

I would recommend considering switching to JSR223 Sampler and Groovy language where you will have full control of your logic flow. See Using Array Objects tutorial for more details if needed.

Dmitri T
  • 159,985
  • 5
  • 83
  • 133
  • Then do I have to entire code to execute a procedure and check the response in groovy? – sunleo Jul 09 '18 at 10:14
  • If yes related to the connection configuration, do I have to create everything or I can make use of the JDBC Connection Configuration of jMeter? – sunleo Jul 09 '18 at 10:27
  • You can instantiate the connection using JDBC Connection Configuration like: `java.sql.Connection conn = org.apache.jmeter.protocol.jdbc.config.DataSourceElement.getConnection("your_variable_name_here");` . Other logic, i.e. preparing and executing the query and working with results needs to be done in Groovy completely. – Dmitri T Jul 09 '18 at 11:15