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 ?
set serveroutput on SQLPlus specific command. You have to call DBMS_OUTOUT.GET_LINES after PL/SQL block execution.
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.
