Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Question Unanswered: stored procedures - serveroutput in java

    Hi

    is there any chance to receive the serveroutput from the stored procedures within a java programm?

    The problem is, that "SET SERVEROUTPUT ON" causes a exception.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    SET SERVEROUTPUT is not a SQL command but a SQL*Plus command, that's why you cannot use it through JDBC.

    But using dbms_output is pretty easy in Java:

    Call the dbms_output.enable() procedure before doing any JDBC calls that use the dbms_output package:
    Code:
    CallableStatement enableStatement = conn.prepareCall( "begin dbms_output.enable(:1); end;" );
    enableStatement.setLong( 1, size );
    enableStatement.executeUpdate();
    enableStatement.close();
    Once your stored procedure (or whatever has finished), run the following code to retrieve anything that was "printed" using dbms_output.println()
    Code:
    CallableStatement showOutputStatement = conn.prepareCall(
    "declare " +
    "    l_line varchar2(255); " +
    "    l_done number; " +
    "    l_buffer long; " +
    "begin " +
    "  loop " +
    "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; " +
    "    dbms_output.get_line( l_line, l_done ); " +
    "    l_buffer := l_buffer || l_line || chr(10); " +
    "  end loop; " +
    " :done := l_done; " +
    " :buffer := l_buffer; " +
    "end;" );
    
    showOutputStatement.registerOutParameter( 2, Types.INTEGER );
    showOutputStatement.registerOutParameter( 3, Types.VARCHAR );
    StringBuffer result = new StringBuffer(1024);
    try
    {
      for(;;)
      {
        showOutputStatement.setInt( 1, 32000 );
        showOutputStatement.executeUpdate();
        result.append(showOutputStatement.getString(3).trim());
        if (showOutputStatement.getInt(2) == 1 ) break;
      }
    finally
    { 
      showOutputStatement.close(); 
    } 
    System.out.println("OUTPUT: " + result.toString());

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't speak Java, but - while you wait for another (i.e. better) answer, read this.

    SET is SQL*Plus command, while stored procedures (SP) are PL/SQL ... well, procedures. That's why SET SERVEROUTPUT ON (SSO) won't work; besides, I believe you do not have SSO in the SP, do you? Such a procedure wouldn't compile.

    Here is DBMS_OUTPUT package manual (Oracle 8, but never mind; what is your Oracle version, anyway?). It says that one should use DBMS_OUTPUT.ENABLE in order to enable output (obviously); it is not necessary to call it if you use SSO in SQL*Plus.

    However, as you use Java and not SQL*Plus, you might try with it.

    Furthermore, where do you use DBMS_OUTPUT? Is it in an exception handling section, perhaps? If so, avoid it and use RAISE_APPLICATION_ERROR instead - it doesn't depend on SQL*Plus, Java or whatever else.

    Finally, read this Ask Tom article; perhaps you'll find it helpful.

    [EDIT]

    Hello, Shammat! Next time wait until I finish my dummy answer, and then give the proper answer

  4. #4
    Join Date
    Oct 2006
    Posts
    2
    Thanks for your help!

    It works now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •