OraFAQ Forum: Client Tools » Bug in SQL*Plus "spool" command?

Home » Other » Client Tools » Bug in SQL*Plus "spool" command? (Oracle 18c (18.3) and 19c (19.3), Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bug in SQL*Plus "spool" command? [message #680221]

Wed, 29 April 2020 03:36

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Bug in SQL*Plus "spool" command? [message #680234 is a reply to message #680221]

Wed, 29 April 2020 09:13

_jum
Messages: 577
Registered: February 2008

Senior Member

Have no 19c, but you could try:
SET ESCCHAR $

Report message to a moderator

Re: Bug in SQL*Plus "spool" command? [message #680236 is a reply to message #680234]

Wed, 29 April 2020 10:30

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.