How to access the dbms_output buffer from C code?

Question

Various Oracle solutions involve PL/SQL snippets with the dbms_output package -- in particular the dbms_output.put_line().

These work with the sqlplus interpreter, but some of our code uses the C API (oci.h, ociapr.h and friends).

Is there a way to get the contents of the DBMS buffer(s) with those functions?

Answer 1

You can use the procedure DBMS_OUTPUT.GET_LINE to retrieve information from the buffer. I can't help you with the C code, but the below code are the Oracle PL/SQL procedures to call.

Before you run the PL/SQL snippet that calls DBMS_OUTPUT.PUT_LINE, run this procedure to enable the buffer:

dbms_output.enable;

After you run the PL/SQL snippet, you can retrieve a single line of output like this:

declare
    v_line varchar2(32767);
    v_status integer;
begin
    dbms_output.get_line(v_line, v_status);

    --Do something with the output in V_LINE here
    ...
end;
/

For larger output, you may want to call DBMS_OUTPUT.GET_LINES, which returns an array of strings and the number of lines. See the package documentation for a full description of the procedures and arguments.