CODEWITHSUNDEEP
javaoracle-databasegroovyplsql
HAH
HAH

Reputation: 211

Reading the Output of DBMS_OUTPUT using Groovy

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?

Upvotes: 1

Views: 539

Answers (2)

Rohit Borse
Rohit Borse

Reputation: 85

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.

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21115

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

Upvotes: 3

Related Questions