4

Im using Aqua Data Studio to debug a stored proc by scattering output statments throughout.

I have a delete statement in the package which violate an integrity constraint:

DELETE FROM x WHERE x.ID = an_x_with_children;

My proc fails with an ORA-02292 on this line, as expected. I want to see the value of the an_x_with_children variable. So I wrap the line with outputs like so:

dbms_output.put('Attempting to delete x: ' || an_x_with_children);
DELETE FROM x WHERE x.ID = an_x_with_children;
dbms_output.put(' Success');

And expect to see the message as the last thing in the messages console prior to the integrity constraint violated error message. But it doesn't print!

Now if I change the output to use the put_line() procedure like this:

dbms_output.put_line('Attempting to delete x: ' || an_x_with_children);
DELETE FROM x WHERE x.ID = an_x_with_children;
dbms_output.put_line(' Success');

I see the message "Attempting to delete x: 123" immediately before the proc errors out.

The docs for the dbms_output package don't mention the put and put_line procedures behaving any differently in this respect. For instance, it says

Output that you create using PUT or PUT_LINE is buffered.

So I would expect either both or neither to show output when the proc errors.

Can someone explain what's going on with this behaviour to me?

MatthewMartin
  • 32,326
  • 33
  • 105
  • 164
Tom Tresansky
  • 19,364
  • 17
  • 93
  • 129
  • This is the documented behaviour of PUT() and PUT_LINE(). "You can build a line of information piece by piece by making multiple calls to PUT, or place an entire line of information into the buffer by calling PUT_LINE." – APC Sep 21 '12 at 14:55
  • Umm, because it explains why you see the message when you use PUT_LINE() and don't see it when you use PUT()? – APC Sep 21 '12 at 22:39
  • @APC It was in fact due to ADS using GET_LINE() to read only complete lines from the buffer which was causing the issue. As far as I can tell, had it simply flushed whatever was in the buffer when the proc exited, as you might expect such a tool to do, it wouldn't matter if I had finished building a complete line or not. Perhaps I should have made it more clear in my question that I understood the distinction between the 2 procedures. Surely you can see, since I did understand that point, why I would wonder what point you were trying to make. – Tom Tresansky Sep 24 '12 at 13:03
  • I suppose the thing is, having read the documentation, I expect GET_LINE() to retrieve only whole lines. Therefore the different behaviours of PUT and PUT_LINE makes sense. I agree that if you expect GET_LINE() to do something other what it actually does do the difference in output might be mystifying. – APC Sep 24 '12 at 13:19
  • I get that, and I did check the Oracle docs on the dbms_output package (as evidenced by the link above) and wouldn't have expected anything different from GET_LINE(). The issue was never about how GET_LINE() behaves, it was about how ADS behaves. I didn't know what that ADS would have to access the buffer used by dbms_output via calls to GET_LINE() versus getting at the buffered data via some other mechanism. This is something THEIR (ADS') docs don't seem to be very explicit about and was the impetus behind the quesion. I'd tag it ADS if I could. – Tom Tresansky Sep 24 '12 at 17:30

1 Answers1

6

Here is an example that shows the behaviour you're seeing:

SQL> exec dbms_output.put_line('hello')
hello

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put('hello again')

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put(' and again')

PL/SQL procedure successfully completed.

SQL> exec dbms_output.new_line
hello again and again

PL/SQL procedure successfully completed.

The documentation says "SQL*Plus calls GET_LINES after issuing a SQL statement or anonymous PL/SQL calls."

And procedure GET_LINES says "This procedure retrieves an array of lines from the buffer."

With PUT you haven't completed your line yet. And so it doesn't print.

The NEW_LINE procedure mentions this as well: "This procedure puts an end-of-line marker. The GET_LINE Procedure and the GET_LINES Procedure return "lines" as delimited by "newlines". Every call to the PUT_LINE Procedure or NEW_LINE Procedure generates a line that is returned by GET_LINE(S)."

Regards,
Rob.

Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55