1

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?

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Mikhail T.
  • 3,043
  • 3
  • 29
  • 46
  • 1
    Have you seen this oracle sample code? [Programmer's Guide to the Oracle7 Server Call Interface](https://docs.oracle.com/cd/A57673_01/DOC/api/doc/OCI73/apa1.htm) – ryyker May 28 '21 at 19:29
  • Oracle documentation for Oracle Call Interface for 12.2 is at https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnoci/oci-programming-basics.html#GUID-46090C93-4BC4-4E88-AA09-3E9B716917A3 -- tl;dr so not sure if you can invoke the procedures in `DBMS_OUTPUT` or not. DBMS_OUTPUT doco: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_OUTPUT.html#GUID-C1400094-18D5-4F36-A2C9-D28B0E12FD8C – Mark Stewart May 28 '21 at 19:33
  • Yes, @ryyker, the sample code is, pretty much, the only available documentation for the API. Unfortunately, it makes no mention of `dbms_output`. I understand, that the procedures in the package operate on some buffer(s) internal to the server -- my question is, how can a client access them. `sqlplus` and various Oracle client GUI-clients all do it, but their sources aren't open... I can invoke an SQL-statement, but the only known way of obtaining a result is by processing a _data set_ (when the statement is a `select ...`). – Mikhail T. May 29 '21 at 18:03

1 Answers1

0

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Using `dbms_output` from `sqlplus` is easy -- and examples abound. Doing it from C-code is, what I'm looking for here. – Mikhail T. May 30 '21 at 19:37
  • Sorry I can't help with the C part. But the code I provided is all PL/SQL, and nothing specific to sqlplus. However you call your current PL/SQL snippet, you can simply add those commands before and after it. – Jon Heller May 31 '21 at 02:09
  • Looks like there is no one left out there, who accesses Oracle from C (or even C++) programs. There are still C-experts and SQL-experts, but no one with _both_... Certainly not on SO... – Mikhail T. Jun 01 '21 at 16:56
  • 1
    @MikhailT. There are some users here with both of those skill sets, but not many. I added a more specific tag, and hopefully that will attract them to this question. – Jon Heller Jun 01 '21 at 17:53
  • 1
    Check this (mine project) https://github.com/tora-tool/tora/blob/master/src/widgets/tooutput.cpp Basically you need to call DBMS_OUTPUT: ENABLE GET_LINES GET_LINE – ibre5041 Sep 14 '21 at 12:04