SQL Developer DBMS_OUTPUT without 'PL/SQL procedure successfully completed.'

Question

In SQL Developer, when running some PL/SQL, when the procedure is completed, the message 'PL/SQL procedure successfully completed.' is returned.

The PL/SQL that is run may return error messages to the user if the operation could not be completed for any reason through DBMS_OUTPUT.PUT_LINE, however, the user will also see 'PL/SQL procedure successfully completed.', which could be misleading (especially if the Script Output window is small enough that the DBMS_OUTPUT is not visible).

Is there any way to have the DBMS_OUTPUT return what it should whilst also having the script not return 'PL/SQL procedure successfully completed.'?

If not, are there any alternatives in SQL Developer that I may be unaware of to provide instant personalised feedback to the user?

declare 

    testex exception;

begin

    if 1=1 then 
        raise testex;
    end if;

exception when testex then

    dbms_output.put_line('Error msg');

end;

An output to `dbms_output` is not an error, so the feedback that the procedure was successful is correct. If you want to signal an error, then throw an exception in your PL/SQL code — , Mar 29 '18 at 11:25
As I need to provide instant user feedback, I've placed the DBMS_OUTPUT explaining the error within an exception; however, even if the exception is raised, the message 'PL/SQL procedure successfully completed.' is still shown — TomB, Mar 29 '18 at 11:35
Code added. Running the code shows 'Error msg' as well as 'PL/SQL procedure successfully completed.'. For practicality and to avoid confusion, I'd need only 'Error msg' to be displayed — TomB, Mar 29 '18 at 11:42
Your exception handler swallows the exception and thus it never happened as far as the caller is concerned — , Mar 29 '18 at 11:50
Is it not possible to show only the DBMS without the completion message then? If not, are there any solutions that could work? — TomB, Mar 29 '18 at 12:00

eifla001 · Accepted Answer · 2018-03-29T14:07:06.957

6

Below code works in my end. Did you try to run your code like below?

Copied text from a website to explain the SET FEEDBACK OFF command.

Source link: https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm

SET FEED[BACK] {6 | n | ON | OFF} - Displays the number of records returned by a script when a script selects at least n records.

ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.

SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.

edited Mar 29 '18 at 14:07

answered Mar 29 '18 at 12:14

eifla001

1,137
8
8

A little explanation stating that it is the 'SET FEEDBACK OFF' that prevents the 'PL/SQL procedure successfully completed.' message from being displayed would turn this from a question into an answer. – BriteSponge Mar 29 '18 at 13:41
See my updated my answer. I believe this question is a duplicate to this link https://stackoverflow.com/questions/250509/how-do-i-suppress-pl-sql-procedure-successfully-completed-message-in-sqlplus – eifla001 Mar 29 '18 at 14:08
It may well be a duplicate but it is also an answer and is the right answer. – BriteSponge Mar 29 '18 at 14:10

score 1 · Answer 2 · answered Mar 29 '18 at 11:46

1

Add a RAISE statement in the error handler to re-raise the exception so that any outer handler can deal with it:

declare 

    testex exception;

begin

    if 1=1 then 
        raise testex;
    end if;

exception when testex then

    dbms_output.put_line('Error msg');

    RAISE;  -- re-raise the exception to an outer handler

end;

Best of luck.

answered Mar 29 '18 at 11:46

Bob Jarvis - Слава Україні

48,992
9
77
110

Thanks for the reply but how would this work if my code is isolated with no outer exception handler? I.e. how would the code that I've provided produce the DBMS_OUTPUT with no ''PL/SQL procedure successfully completed.'' – TomB Mar 29 '18 at 11:48

score 1 · Answer 3 · answered Mar 30 '18 at 12:14

You have coded it explicitly to complete successfully in the event of a testex exception. The code says: if this happens, then print a message and end. If you want it to actually fail then it needs to raise an exception.

I would just use something like this:

begin
    if 1=1 then 
        raise_application_error(-20000, 'Bananas are not available on a Tuesday');
    end if;
end;
/

begin
*
ERROR at line 1:
ORA-20000: Bananas are not available on a Tuesday
ORA-06512: at line 3

This will cause an actual error, rather than just printing out a message that happens to talk about an error, allowing you to control deployments and build scripts etc.

It will also roll back any uncommitted transactions within the block, which your current approach does not, and it will show the actual line number.

SQL Developer DBMS_OUTPUT without 'PL/SQL procedure successfully completed.'

3 Answers3