1

I have some stored procedures and Java codes. In my Java codes I'm calling stored procedures that are in the Oracle database. My stored procedures contain a few DBMS_OUTPUT to see some steps. When I call the procedure from SQL manually, there is no problem, I can see the outputs at the output tab but my problem is I don't know where is the output when the procedure called from Java. Is Oracle DB ignores this outputs when we called the stored procedure from Java or any other platform? Or is it stores them in some wheres?

İlkay Gunel
  • 712
  • 2
  • 11
  • 24
  • 1
    Are you able [create a pipelined function](https://stackoverflow.com/a/19143017/266304) and make a second call to get the output as a separate result set? (If you can't create your own type you can use `sys.odcivarchar2list`, or possibly `sys.dbms_debug_vc2coll` might be more appropriate here...) – Alex Poole Mar 30 '20 at 14:38
  • 1
    [Or without new objects](https://stackoverflow.com/a/47831073/266304), which might be better! – Alex Poole Mar 30 '20 at 14:49

1 Answers1

0

The output should be written to a session trace file on the DB server. I do not know if there is a way to fetch this output within Java for display on the client. A workaround would be to insert your messages into a table (possibly using an autonomous transaction) and query that table after the procedure has completed.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • Thank you for this comment. – İlkay Gunel Apr 01 '20 at 10:46
  • It's not written to any trace. The dbms_output buffer is a collection within the dbms_output package, which needs to be fetched using `dbms_output.get_lines()`. – William Robertson Apr 02 '22 at 16:18
  • @WilliamRobertson I think I interpreted the question as being about a Java stored procedure running inside the database. In that situation, as I recall, output is written to a trace file. But rereading the question, it's not clear that's what the OP is doing. – Dave Costa Apr 02 '22 at 16:25
  • Ah, see what you mean. If the question means output from Java stored procedures that would make sense. I see there is a chapter about it in the [Java Developer's Guide](https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdev/redirecting-server-output.html). I don't know Java and I've never heard of anyone using Java Stored Procedures though so I can't confirm. – William Robertson Apr 03 '22 at 08:40