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 — user330315
– user330315, Commented Mar 29, 2018 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
– TomB, Commented Mar 29, 2018 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
– TomB, Commented Mar 29, 2018 at 11:42
Your exception handler swallows the exception and thus it never happened as far as the caller is concerned — user330315
– user330315, Commented Mar 29, 2018 at 11:50

eifla001 · Accepted Answer · 2018-03-29 14:07:06Z

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, 2018 at 14:07

answered Mar 29, 2018 at 12:14

eifla001

1,1578 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BriteSponge Over a year ago

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.

eifla001 Over a year ago

See my updated my answer. I believe this question is a duplicate to this link stackoverflow.com/questions/250509/…

BriteSponge Over a year ago

It may well be a duplicate but it is also an answer and is the right answer.

Bob Jarvis - Слава Україні · Accepted Answer · 2018-03-29 11:46:19Z

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, 2018 at 11:46

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

50.2k10 gold badges81 silver badges119 bronze badges

1 Comment

TomB Over a year ago

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.''

William Robertson · Accepted Answer · 2018-03-30 12:14:04Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related