JMeter - how to call Oracle function via JDBC Request

Question

I'm using JMeter to test some Oracle functions and procedures. I want to use JDBC request sampler to call a function (not a stored procedure). Function I want to call is specific as it contains a DML operation inside it (does an insert) so I cannot put it into a select statement as it would result in ORA-14551 - cannot perform a DML operation inside a query error.

So trying to execute a JDBC Request with query type callable statement:

BEGIN

  {call MY_PACKAGE.MY_FUNCTION(?,?)};

END;

or

{call MY_PACKAGE.MY_FUNCTION(?,?)};

!EDIT!

Adding a screenshot to show a function cannot be called that way:

or select statement:

select MY_PACKAGE.MY_FUNCTION(?,?) from dual

will not work (returns PLS-00221 and ORA-14551 respectively). Now I know how to handle that kind of function via JSR223 Sampler (ex. Groovy script), but the question is can it be done via JDBC Request (and as a result to store output of the function into jmeter variable)?

For testing purposes, the function I am calling is defined in package as:

create or replace package my_package
is
    function my_function(par1 varchar2, par2 varchar2) return varchar2;
end my_package;

create or replace package body my_package
is

    function my_function(par1 varchar2, par2 varchar2) return varchar2
    is

    begin    
        return 'Test_Output';
    end my_function;

end my_package;

Answer 1

Use Query type Callable Statement with query:

{call MY_PACKAGE.MY_FUNCTION(?,?)}

There is generally no need to use { and } to enclose Callable statements; however they may be used if the database uses a non-standard syntax.

Answer 2

Try this:

declare
  c varchar2(500);
begin
  c := your.function(?, ?);
  select c into ? from dual; --this is to return the value to JMeter
end;

Parameter values: TEST,TEST,OUT
Parameter types: VARCHAR,VARCHAR,OUT VARCHAR

Other variable names are not really needed, but you can set them if you use them later in test plan.

This might also work (I did not test it):

? := your.function(?, ?);

(note: the out parameter is first now, unlike above, where it was last - adjust the parameter values and types in the JMeter config)