Home » SQL & PL/SQL » Client Tools » Bug in SQL*Plus "spool" command? (Oracle 18c (18.3) and 19c (19.3), Windows 7)
Bug in SQL*Plus "spool" command? [message #680221] Wed, 29 April 2020 03:36 Go to next message
Michel Cadot
Messages: 67293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I wonder if someone has the same difference of behavior than me in the spool command of SQL*Plus between 18c and 19c in other platforms or versions (mine is Win7) or if this is because Win7 is not certified for 19c.
It seems that now "$" is no more accepted in spool file name:
D:\Temp\Listing>sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Mer. Avr. 29 10:09:07 2020
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

SQL> spool foo$foo
SQL> spool off
SQL> exit
D:\Temp\Listing>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 29 10:09:29 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> spool foo$foo
SP2-0332: Cannot create spool file.
SQL> exit
Re: Bug in SQL*Plus "spool" command? [message #680234 is a reply to message #680221] Wed, 29 April 2020 09:13 Go to previous messageGo to next message
_jum
Messages: 571
Registered: February 2008
Senior Member
Have no 19c, but you could try:
SET ESCCHAR $
Re: Bug in SQL*Plus "spool" command? [message #680236 is a reply to message #680234] Wed, 29 April 2020 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't know this option but it does not work:
D:\Temp\Listing>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 29 17:21:43 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> set escchar $
SP2-0306: Invalid option.
Usage: SET ESCCHAR [OFF|@|?|%]
SQL> set escchar '$'
SP2-0306: Invalid option.
Usage: SET ESCCHAR [OFF|@|?|%]
For information:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SET-system-variable-summary.html#GUID-37A75C28-8B05-4D5E-838C-083 28D51FBB6
Quote:
12.41.21 SET ESCCHAR

Syntax

SET ESCCHAR {@ | ? | % | OFF}

Specifies a character to be escaped and not interpreted when used in a file name for the SPOOL, START, @, RUN and EDIT commands. These special characters are translated to the following:

@ in a filename will be translated to Oracle SID

? is translated to Oracle Home in Unix

% is translated to Oracle Home in Windows

While it is not recommended that these characters are used in filenames, if you have legacy files that do use them, it might be useful to include a SET ESCCHAR command in your GLogin file to implement it across your site.

If not escaped, the characters @, ? and % have significance when interpreted and cause errors for the SPOOL, START, @, RUN and EDIT commands.

SET ESCCHAR is set OFF by default.
Re: Bug in SQL*Plus "spool" command? [message #680242 is a reply to message #680236] Thu, 30 April 2020 00:53 Go to previous messageGo to next message
_jum
Messages: 571
Registered: February 2008
Senior Member
Found in Release 19 User's Guide and Reference
2.41.21 SET ESCCHAR


Quote:
Note:
Starting from Oracle Database release 19c, version 19.3, file names with the $ character will no longer run on Windows.

[Updated on: Thu, 30 April 2020 00:54]

Report message to a moderator

Re: Bug in SQL*Plus "spool" command? [message #680243 is a reply to message #680242] Thu, 30 April 2020 01:34 Go to previous message
Michel Cadot
Messages: 67293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just below what I quoted, I simply missed the note, thanks. Smile

So not a bug, just a program restriction. Sad
(Why Oracle chose that something that has worked the last 50 years should no more work now Question )

Previous Topic: SQL Developer doesn't store passwords
Next Topic: Error while executing the sql query in pl/sql developer
Goto Forum:
  


Current Time: Sun Aug 09 09:49:17 CDT 2020