Skip to Main Content
  • Questions

  • SP2-0606 when use dynamic spool path

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Volkenand.

Asked: February 09, 2018 - 9:05 am UTC

Last updated: February 22, 2018 - 1:39 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

SQL*Plus client : Release 11.2.0.1.0 on Windows 8 client

writing output in Spool file with hardcoded path is working in any directory, but with spool &filename with and without doublequotes around that string
I get the message
SP2-0606: Kann SPOOL nicht erstellen - Datei "&filename.LST"


What can be the cause ?

glogin.sql from SQL Plus

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
--Umlautproblem sqlplus beseitigen, funktioniert für Lucida Console
host chcp 1252
--set linesize 5000
set trimspool on
set pagesize 1000
--set colsep |
--set tab off
--set SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
--set ESCAPE \
set echo on
set feedback 1
--set pno 0


--SQL skript

--@test
set echo on
column filename new_val filename

--Quartal ermitteln
select to_char(sysdate,'Q') AS MY_QTR FROM DUAL;
--String Pfad ausgeben
--select 'W:\SG-323\TeamZentraleKatalogredaktion\Bankleitzahlen\Q' || to_char(sysdate,'Q') ||  '_' || to_char(sysdate, 'yyyy')||  '\ZD_ZZB-einspielenÄnderungskennz-A-M.txt' filename from dual;

select 'H:\Dokumente\SQL\Q' || to_char(sysdate,'Q') ||  '_' || to_char(sysdate, 'yyyy')||  '\ZD_ZZB-einspielenÄnderungskennz-A-M-testspool-hardcodiert.txt' filename from dual;

--Spool erstellen mit Spalten, Pfad in Variable
spool "&filename"
--spool H:\Dokumente\SQL\Q1_2018\ZD_ZZB-einspielenÄnderungske

and Connor said...

It's the unicode characters, eg

SQL> col x new_value y
SQL> select 'C:\tmp\spaces and character stuffÄ\demo.dat' x from dual;

X
--------------------------------------------
C:\tmp\spaces and character stuffÄ\demo.dat

1 row selected.

SQL> spool "&&y"
SP2-0606: Cannot create SPOOL file "C:\tmp\spaces and character stuffÄ\demo.dat"
SQL> spool off
not spooling currently
SQL>
SQL>
SQL> col x new_value y
SQL> select 'C:\tmp\spaces and character stuff\demo.dat' x from dual;

X
------------------------------------------
C:\tmp\spaces and character stuff\demo.dat

1 row selected.

SQL> spool "&&y"
SQL> spool off
SQL>
SQL>


But if you set NLS_LANG before you start, you'll be ok


C:\Users\hamcdc>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\Users\hamcdc>sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 13 10:24:50 2018

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

Last Successful login time: Sat Feb 10 2018 08:14:24 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> col x new_value y
SQL> select 'C:\tmp\spaces and character stuff\demo.dat' x from dual;

X
------------------------------------------
C:\tmp\spaces and character stuff\demo.dat

SQL> spool "&&y"
SQL> spool off


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL Plus call SQL script with dynamic spool path

Kerstin Volkenand, February 14, 2018 - 2:49 pm UTC

I use to open QLPLUS and then start as script: via @ W:\SG-323\TeamZentraleKatalogredaktion\Bankleitzahlen\Q1_2018\ZD_ZZB-einspielen-insert-Aenderungskennz-A-M-testspool.sql and had removed the NLS_LANG settings, but it is still the same message,
only when I use path for sppol with @ select 'H:\Dokumente\SQL\Q' || to_char(sysdate,'Q') || '_' || to_char(sysdate, 'yyyy')|| '\ZD_ZZB-einspielenAenderungskennz-A-M-testspool-hardcodiert.txt' filename from dual;
not when path is hardcoded.

SQL Plus call SQL script with dynamic spool path

Kerstin Volkenand, February 14, 2018 - 3:13 pm UTC

I use SQL plus command line and call with @ W:\SG-323\TeamZentraleKatalogredaktion\Bankleitzahlen\Q1_2018\ZD_ZZB-einspielen-insert-Aenderungskennz-A-M-testspool.sql
The message is only when I use dynamich path as

select 'H:\Dokumente\SQL\Q' || to_char(sysdate,'Q') || '_' || to_char(sysdate, 'yyyy')|| '\ZD_ZZB-einspielenAenderungskennz-A-M-testspool-hardcodiert.txt' filename from dual;
independent of the language settings.
Connor McDonald
February 22, 2018 - 1:39 am UTC

OK, try this:

C:\temp>set NLS_LANG=american_america.WE8PC850
C:\temp>sqlplus scott/tiger

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 22 09:37:06 2018

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

Last Successful login time: Sat Feb 17 2018 09:49:47 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> spool c:\temp\ZD_ZZB-einspielenÄnderungske.dat
SQL> select * from dual;

D
-
X

SQL> spool off
SQL> define f = c:\temp\ZD_ZZB-einspielenÄnderungske.dat
SQL> set verify off
SQL> select '&&f' from dual;

'C:\TEMP\ZD_ZZB-EINSPIELENÄNDERUNGSKE.DAT
-----------------------------------------
c:\temp\ZD_ZZB-einspielenÄnderungske.dat

SQL> spool &f
SQL> select sysdate from dual;

SYSDATE
---------
22-FEB-18

SQL> spool off
SQL>


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.