2

I need to execute an Oralce PL/SQL statement using jdbc, but I'm struggling on how to extract the data.

Here is my statement

DECLARE 
   l_xmltype XMLTYPE; 
   l_ctx dbms_xmlgen.ctxhandle; 
BEGIN 
   l_ctx := dbms_xmlgen.newcontext('select * from myTable where rownun < 10;); 
   dbms_xmlgen.setrowsettag(l_ctx, 'My_DATA'); 
   dbms_xmlgen.setrowtag(l_ctx, 'My_Element'); 
   l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ; 
   dbms_xmlgen.closeContext(l_ctx);  
  dbms_output.put_line(l_xmltype.getClobVal); 
  dbms_output.get_lines(?, l_xmltype);
End;

And my code

CallableStatement cs = connection.prepareCall(plsql);
cs.registerOutParameter(1, Types.ARRAY,"DBMSOUTPUT_LINESARRAY");
cs.execute();

Array array = null;
array = cs.getArray(1);
Stream.of((Object[]) array.getArray())
    .forEach(System.out::println);

And I'm getting the error

java.sql.SQLException: ORA-06550: line 1, column 380: PLS-00306: wrong number or types of arguments in call to 'GET_LINES'

I'm not an expert in PL/SQL nor jdbc so I'm struggling to find a solution.

jason.kaisersmith
  • 8,712
  • 3
  • 29
  • 51
  • The error code says, what is the problem. dbms_output.get_lines has two parameters "lines" as table of varchar2, and "numlines" as in/out number parameter. You are sending the wrong parameters. I suggest to wrap this anonymous block into stored-procedure and call it. Easy to maintain and read. – Ychdziu May 04 '18 at 11:13
  • 2
    possible duplicate to https://stackoverflow.com/questions/47830370/getting-output-from-dbms-output-get-lines-using-jdbc – Marmite Bomber May 04 '18 at 11:33

1 Answers1

1

The second argument of GET_LINES is a number, not an XMLTYPE. Change your call to something like:

dbms_output.get_lines(?, 50);

I've also shown a more complete example on how to do this in a similar question. Note that you also have to add the following call first, to enable dbms_output collection:

dbms_output.enable();

Note that in case you're using jOOQ, you can automatically fetch server output on any statement.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509