CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperdbms-output
TomB
TomB

Reputation: 265

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

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;

Upvotes: 1

Views: 2942

Answers (3)

William Robertson
William Robertson

Reputation: 15991

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.

Upvotes: 1

eifla001
eifla001

Reputation: 1157

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.

Upvotes: 6

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

Reputation: 50017

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.

Upvotes: 1

Related Questions