Will dbms_output.put () be buffered differently than dbms_output.put_line ()?

Question

Im using Aqua Data Studio to debug a stored procedure by scattering output statuses.

I have a delete statement in a package that violates an integrity constraint:

DELETE FROM x WHERE x.ID = an_x_with_children; 

My turn does not work with ORA-02292 on this line, as expected. I want to see the value of an_x_with_children variable. Therefore, I end the line with the following conclusions:

 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 in the message console before the integrity error message. But he does not print!

Now, if I change the output to use the put_line() procedure as follows:

 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 proc errors.

docs for the dbms_output package dbms_output not mention the put and put_line , which behave differently in this respect. For example, he says

The output generated using PUT or PUT_LINE is buffered.

Therefore, I would expect both or none of them to show results with proc errors.

Can someone explain what happens to this behavior?

Answer 1

Here is an example showing the behavior you see:

 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 an SQL query or anonymous PL / SQL calls."

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

With PUT, you have not finished your line. And so it does not print.

The following is also mentioned in the NEW_LINE procedure: "This procedure places the end of line marker. The GET_LINE procedure and the GET_LINES procedure return" lines "as limited by" new lines. "Each call to the PUT_LINE or NEW_LINE procedure generates a line returned by GET_LINE (S)."

Yours faithfully,
Rob