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

1
votes

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;
3
sqloracleplsqloracle-sqldeveloperdbms-output
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 - a_horse_with_no_name
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
edit your question and show us the code - a_horse_with_no_name
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
Your exception handler swallows the exception and thus it never happened as far as the caller is concerned - a_horse_with_no_name

3 Answers

6
votes

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

enter image description here

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.

1
votes

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.

1
votes

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.