how to set serveroutput on using jdbc connection in Jmeter

Question

I am trying to execute a block of PL/SQL code using JDBC request as below

set serveroutput on; 
declare
..
BEGIN
DBMS_OUTPUT.ENABLE();
..
..
DBMS_OUTPUT.PUT_LINE(X);
END;

But I am getting error as ORA-00922: missing or invalid option in the Response. If I remove the set serveroutput on, the SQL block is executing successfully. But I am not getting any values in the Response.

I tried to run the exact same piece of SQL block in SQL Developer and it does show me the values expected.

How do I run this block of code and get the values to be populated in JMeter ?

Cairn

Answer 1

set serveroutput on SQLPlus specific command. You have to call DBMS_OUTOUT.GET_LINES after PL/SQL block execution.

Youthful

i tried the same now. Declared variables z(varchar2) and n (integer). And added the line DBMS_OUTPUT.GET_LINE(z,n);. How do i now get the value from z? do i need to use put_line again? – Cairn

you have to call such anonymous block begin DBMS_OUTPUT.GET_LINE(:z,:n); end; from JDBC and then use System.out.println() to display rows returned from DBMS_OUTPUT. Or you can use GET_LINE function in a loop if you have problems getting array of strings via JDBC. This is what SQLDeveloper(and other tools) does internaly. – Youthful

Answer 2

Tweaking an old answer, you could create a function that lets you get the dbms_output buffer as a result set - which may be easier for you handle from JMeter:

create or replace function get_lines
return sys.odcivarchar2list pipelined is
  lines dbms_output.chararr;
  numlines integer;
begin
  numlines := 999;
  dbms_output.get_lines(lines, numlines);
  if numlines > 0 then
    for i in 1..numlines loop
      pipe row (lines(i));
    end loop;
  end if;
end;
/

After executing your block you can query:

select * from table(get_lines);

You canread more about how dbms_output.get_lines works in the documentation. You might be able to call it directly from Jmeter, as @ibre5041 suggested. Or there may be a better alternative to using dbms_output at all.

Carrico

Answer 3

The DBMS_OUTPUT server output isn't automatically "piggy backed" with your other data, so you have to fetch it explicitly. I've described the approach here in a blog post on how to fetch Oracle's DBMS_OUTPUT content from JDBC. The gist of it is this:

try (CallableStatement call = c.prepareCall(
    "declare "
  + "  num integer := 1000;"
  + "begin "
  + "  dbms_output.get_lines(?, num);"
  + "end;"
)) {
    call.registerOutParameter(1, Types.ARRAY,
        "DBMSOUTPUT_LINESARRAY");
    call.execute();
 
    Array array = null;
    try {
        array = call.getArray(1);
        Stream.of((Object[]) array.getArray())
              .forEach(System.out::println);
    }
    finally {
        if (array != null)
            array.free();
    }
}

It requires a second round trip if you do it this way. Of course, you could write an anonymous block that does both your own logic, and the fetching of server output explicitly.

Don't forget to enable server output prior to this:

begin dbms_output.enable(); end;

See also Getting output from dbms_output.get_lines using JDBC

Aniseikonia