Reading the Output of DBMS_OUTPUT using Groovy

Question

I'm trying to execute an oracle PL/SQL procedure with groovy but I get exceptions. From what I've understood it is due to these intial lines-

SET SERVEROUTPUT ON
SET FEEDBACK OFF 
execute dbms_output.enable(buffer_time);

My code looks like this:

Connection con=DriverManager.getConnection(url,username,password);
def sql= new Sql(con)
String stmt= " SET SERVEROUTPUT ON
SET FEEDBACK OFF 
execute dbms_output.enable(buffer_time);
declare
//some varaibles
begin
//code
dbms.output.put.line(variable)
end
"
sql.execute(stmt);

When I remove the first 3 lines, I get no errors but there will be no output as I'm using dbms.output.put.line hence they are neccessary. How do I resolve the SQL Syntax Exception I get from the intial lines?

"i get exceptions" "i get error" - what exceptions? what errors? — cfrick, Aug 16 '21 at 15:18
Using `dbms_output` to return data to a calling application is not something that makes sense in 99.99999% of cases. `dbms_output` is more of a crude debugging tool. It would generally make far more sense to, say, declare a function that returned a value and to call that function from your client. Or a procedure with an `out` parameter. — Justin Cave, Aug 16 '21 at 15:39
The first 3 lines don't work because they're SQL*Plus commands, not PL/SQL. I don't think it will solve your problem, but you could move `dbms_output.enable();` to after `begin` and it would run. — kfinity, Aug 16 '21 at 16:02

Marmite Bomber · Accepted Answer · 2021-08-16T17:32:46.263

The solution how to read the DBMS_OUTPUT in JDBC is described here and here

Below is the adapted script using (partly) GroovySQL.

Note that the important part is the call of dbms_output.get_lines - without it you'll see no output.

I use (plain) JDBC to call it as it seems some extensions in GroovySQL are required to pass typed ARRAY as an OUT parameter - see here

def stmt = """begin 
  dbms_output.enable(); 
  dbms_output.put_line('line 1'); 
  dbms_output.put_line('line 2'); 
end;"""
   
sql.execute(stmt)

int arraySize = 100;
def stmt2 = """begin 
  dbms_output.get_lines(?, ?); /* output array, array size */
end;
"""

def cs = con.prepareCall(stmt2)
cs.registerOutParameter(1, Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
cs.registerOutParameter(2, Types.INTEGER);
cs.setInt(2, arraySize);
cs.execute()

def array = cs.getArray(1);

println array.dump()
array.getArray().each {
   println(it)
}

array.free();
cs.close();

It seems that the returned array contains one additional element filled with null that should be ignored

name = XXXX.DBMSOUTPUT_LINESARRAY
max length = 2147483647
length = 3
element[0] = line 1
element[1] = line 2
element[2] = null

score 0 · Answer 2 · answered Nov 15 '21 at 16:44

I am getting error

Fail to construct descriptor: empty Object name

I am not sure how should I pass object name. Below is my groovy call.

sql.call("{call dbms_output.get_lines(?, ?)}", [Sql.ARRAY, sql.inout(Sql.INTEGER(100))]){
retVal1, retVal2 ->
log.info(retVal2);
}

I think your line cs.registerOutParameter(1, Types.ARRAY, "DBMSOUTPUT_LINESARRAY"); is also setting object name.

Reading the Output of DBMS_OUTPUT using Groovy

2 Answers2