33

Is there a way that you can have SERVEROUTPUT set to ON in sqlplus but somehow repress the message "PL/SQL procedure successfully completed" that is automatically generated upon completed execution of a plsql procedure?

JJMoho
  • 455
  • 1
  • 5
  • 8

2 Answers2

62

Use the command:

SET FEEDBACK OFF

before running the procedure. And afterwards you can turn it back on again:

SET FEEDBACK ON
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • and obviously `SET FEEDBACK ON` to re-enable it – ShoeLace May 04 '16 at 09:32
  • sorry for the stupid question, but why do you have to turn it back on? I mean, that's only if you want it on for something else in the same script, right? If you don't want _any_ output from some script, you can just turn it off, and forget about it. It will default to `ON` in other places, right? Or is setting it to `OFF` like some system-wide setting or something? – osullic Jul 06 '20 at 10:22
  • @osullic you don't have to, but since it is the default setting you may want to return to it so that you get useful feedback when running code manually. – Tony Andrews Jul 06 '20 at 10:35
1

This has worked well for me in sqlplus, but I did just notice that "set feedback off" suppresses errors in Sql Developer (at least version 17.2.0.188). Just something to be aware of if you use Sql Developer:

create or replace procedure test_throw_an_error as buzz number; begin dbms_output.put_line('In test_throw_an_error. Now, to infinity!'); buzz:=1/0; end;
/
set serveroutput on
set feedback off
exec test_throw_an_error;
exec dbms_output.put_line('Done, with feedback off');
set feedback on
exec test_throw_an_error;
exec dbms_output.put_line('Done, with feedback on');

Result:

Procedure TEST_THROW_AN_ERROR compiled

In test_throw_an_error. Now, to infinity!

Done, with feedback off

In test_throw_an_error. Now, to infinity!


Error starting at line : 11 in command -
BEGIN test_throw_an_error; END;
Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at "ECTRUNK.TEST_THROW_AN_ERROR", line 1
ORA-06512: at line 1
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:
Done, with feedback on

PL/SQL procedure successfully completed.
MikeA
  • 11
  • 2