Re: spooling DBMS_OUTPUT from Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 24 Oct 1999 13:41:04 -0400
Message-ID: <c0ATOKXh9C4bMRKQ6ie2ve3jsq4A@4ax.com>

A copy of this was sent to "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> (if that email address didn't require changing) On Sun, 24 Oct 1999 18:16:11 +0200, you wrote:

>Your only chance is to redirect the stdout of your Pro*C program, and making
>a call to dbms_output.enable(<size of buffer in bytes>) before the first
>put_line.
>If that doesn't work, you will need to replace all dbms_output by utlfile
>calls.
>

Actually -- you'll need to add code to your Pro*C program (code that sqlplus runs itself when you issue "set serveroutput on")....

After you run a statement with Pro*C -- you'll need to make repeated calls to dbms_output.get_line/get_lines to get the output back in your Pro*C app. Then your pro*c app can do whatever it likes with it.

The code to 'dump' dbms_output.put_line calls might look like this:

{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     output[10][256];
    short       output_i[10];
    int         nlines;
EXEC SQL END DECLARE SECTION;
    int         i,j;

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL EXECUTE
    BEGIN

        dbms_output.enable( 1000000 );
        for i in 1 .. 25 loop
            dbms_output.put_line( 'This is line ' || i );
        end loop;

    END;
    END-EXEC;     for( nlines = 10; nlines == 10; )
    {

        nlines = 10;
        for( i = 0; i < nlines; i++ ) output[i].len = 255;

        EXEC SQL EXECUTE
        BEGIN
            dbms_output.get_lines( :output:output_i, :nlines );
        END;
        END-EXEC;
        for( j = 0; j < nlines; j++ )
            printf( "%.*s\n", output[j].len, output[j].arr );

    }
}

The last loop is the one that dumps the output. It grabs 10 lines at a time and prints them.

when you run the above, it just shows:

This is line 1
This is line 2
This is line 3
This is line 4
...
This is line 23
This is line 24
This is line 25

>Hth,
>
>
>--
>Sybrand Bakker, Oracle DBA
>paul cluiss <paul_cluiss_at_intervoice.com> wrote in message
>news:8BEBE870D403F406.2A793EA8D2FFC25C.6E3217E2B12C5458_at_lp.airnews.net...
>> Hello everyone,
>>
>> I have a Pro*C program which makes calls to stored procedures in an
>> Oracle package. This package has lots of debugging output in it of the
>> form DBMS_OUTPUT() calls. The debugging output shows up on the SqlPlus
>> session if serveroutput is ON, but I would like to also see this output
>> spool'd whenever any of the package's procedures are called, namely from
>> my Pro*C program, which isn't connected to any SqlPlus session. Does
>> anyone know how to set this up?
>>
>> Thanks,
>>
>> Paul Cluiss
>> Dallas, Texas
>>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Oct 24 1999 - 12:41:04 CDT