Using DBMS_OUTPUT.put_line With Excel Macro

Question

I have a databae in Oracle. I've successfully queried it using the following code:

Sub AnalyzeDBATables()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ( _
"User ID=" & _
";Password=" & _
";Data Source=" & _
";Provider=MSDAORA.1")

rs.Open "select * from dba_tables", cn
col = 0

' First Row: names of columns
Do While col < rs.Fields.Count
Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

' Now actual data as fetched from select statement

row = 1
Do While Not rs.EOF
row = row + 1
col = 0

Do While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Loop
rs.MoveNext
Loop

End Sub

It appears to work with simple SQL statements. However my problem arises with retrieving data from some rather long PLSQL code that utilizes DBMS_OUTPUT.put_line. My end data is returned in CSV values.

The more obvious solution might be to try to turn this output into a true table in Oracle at the end, and using SELECT with the above code to be able to harvest it. However I only have read priveleges to the database, so I really need to be able to harvest the output.

Is there a way to harvest the response from DBMS_OUTPUT.put_line that I could then parse within the macro. I've tried even a simple harvest of DBMS_OUTPUT.put_line('Hi'); but I get an error runtime error 3704

Let me know what your thoughts are!

If you could create a function you could potentially [do something like this](http://stackoverflow.com/a/19143017/266304) to return the `dbms_output` from a `select`. But it sounds like you can't do that, so you'd need to read it line-by-line as Justin says - and also agree with him that this is a horrible architecture. — Alex Poole, Aug 13 '14 at 15:31

score 1 · Answer 1 · answered Aug 13 '14 at 15:20

A procedure in a production database whose primary purpose is to write to the dbms_output buffer, hoping that the caller allocates such a buffer, that it is of sufficient size, and that the caller reads from the buffer and does something with the data is a horrible architectural approach. dbms_output is useful for writing out some very basic debugging. It shouldn't be relied upon for the primary purpose of the procedure.

That said, if you want to read the data that is written to dbms_output, you'd need to use the dbms_output.get_line or dbms_output.get_lines procedure. In an Excel macro, I expect that you'd need to write a loop that calls dbms_output.get_line until the status that is returned is 1.

Using DBMS_OUTPUT.put_line With Excel Macro

1 Answers1