Hello,
SQL Developer 21.2.1.204 (... but also reproduced in 4.1.5)
A while ago, I already wrote about subtle differences in behaviour of SQL Developer's SQL worksheets depending on whether a login.sql file is being used or not. Here's another one.
Needless to say, I do use a login.sql file, as I need to have a couple of ALTER SESSION statements, plus DEFINEs, plus alias definitions, performed at session initialization time. In SQL Developer, the login.sql file is configured in Tools -> Preferences..., Database tab, Filename for connection startup script.
Here's the bug: in that configuration, SET TERMOUT OFF is being ignored the first time a script is run from a SQL worksheet in a fresh session.
Steps to reproduce:
1/ Create the following script, and save it as termout_test.sql in a directory in your SQLPATH
prompt *** set termout test
show termout
set termout off
show termout
prompt *** You can't see me! TERMOUT is off, remember?
show termout
begin
raise_application_error(-20000,
'You can''t see me either, TERMOUT is still off, right?');
end;
/
set termout on
show termout
2/ Configure a login.sql file
For reproducing this, a non-empty login.sql is necessary. The bug does not happen when no login.sql is configured, or when an empty file is being used. For demonstration purposes, let's create a file containing just the following line:
-- Hello World!
save it as almost_empty.sql, and set that file as the connection startup script.
(Menu Tools -> Preferences..., then Database tab; put the absolute file path in the Filename for connection startup script input field.)
3/ A fresh session, plus a fresh SQL worksheet, are needed. So you must first disconnect from the database, then reconnect and open a new SQL worksheet in the new session.
4/ Call the test script: type the following in the new SQL worksheet:
@termout_test
and press F5 (or just F9 on the @termout_test line) in order to call the test script.
Results:
Expected readout: (assuming TERMOUT is ON initially)
*** set termout test
termout ON
termout ON
Got:
*** set termout test
termout ON
termout OFF
*** You can't see me! TERMOUT is off, remember?
termout OFF
Error starting at line : 11 File @ E:\Home\user9540031\SQL_Developer\scripts\termout_test.sql
In command -
begin
raise_application_error(-20000,
'You can''t see me either, TERMOUT is still off, right?');
end;
Error report -
ORA-20000: You can't see me either, TERMOUT is still off, right?
ORA-06512: at line 2
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.
termout ON
Obviously—and annoyingly—the SET TERMOUT command has been ignored throughout the test script.
But if you run the test script again, either in the same SQL worksheet (even if you disconnect from the database and connect again, so you're in a new DB session), or in another SQL worksheet using the same DB session (SQL worksheets share some context when a connection startup script is being used), you'll get the expected readout. So in a nutshell, SET TERMOUT doesn't work the first time a script is being run, and nominally after that.
And this does not happen when no connection startup file is being used, or when an empty file is set (which amounts to the same from a practical viewpoint).
Workaround:
So far, the only workaround I've found is as follows: create an empty file named noop.sql in a directory in your SQLPATH, and call it from the new SQL worksheet, before calling any script which relies on SET TERMOUT: the bug does not seem to happen except in the very first script that is called in the new SQL worksheet + DB session.
@noop
@termout_test
*** set termout test
termout ON
termout ON
Remarks:
SQLcl is not affected, and produces the expected readout, just as SQL*Plus.
This is a year-old issue, as it can be reproduced it in version 4.1.5 too.
As stated, this is a minor annoyance only: SQL*Plus tricks that should be hidden by SET TERMOUT OFF appear plainly in the readout, long result sets that should go only to a spool file do appear on screen, etc. But all I have to do to prevent from that is to remember to call the noop.sql script first.
Hope this helps...
Regards,