Re: sqlplus: Help with Spooling (path and file questions)

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 09 Oct 2006 23:30:10 +0100
Message-ID: <pagli2tn7smjsohb54uo6ahugi1iqcp2f3@4ax.com>

On 9 Oct 2006 07:59:12 -0700, "sybrandb" <sybrandb_at_gmail.com> wrote:

>On Oct 9, 4:46 pm, "Greg" <esab..._at_yahoo.com> wrote:
>> Good morning. I'm new to sqlplus (and Oracle for that matter - but
>> loving it!). I'm trying to figure out how to create path and filename
>> for the spool command in sqlplus. Here's what I'm trying:
>>
>> column date_column new_value today
>> SELECT 'm:\home\Big Share\Uploads\2006
>> Fall\'||to_char(sysdate,'yymmdd')||'.txt' date_column FROM dual;
>> spool &today
>>
>> I'm getting a message that says it is illegal. I've tried
>> double-quotes and even no quotes. I'm not sure how to make this work.
>>
>> One other question, how can you make is so that the .lst is not
>> appended to the filename? I also want to say to a file without an
>> extension.
>>
>> I'm using Oracle 9i on a WinXP workstation.
>
>Are you getting this message (please always be as specific as possible
>and try to avoid forcing people in reproducing your problem)
>SP2-0333: Illegal spool file name: "m:\home\Big Share\Uploads\2006
>Fall\061009.txt" (bad character: ' ')
>
>Oracle has never allowed NTFS file names, so you either must use DOS
>file names (without spaces and the like) or you must use the hidden DOS
>names.

Please provide a reference to where it says that "long" filenames are not supported for spool files?

What is an "NTFS" filename anyway? FAT was where filenames went "long" and supported filenames longer than 8 characters, and spaces.

If long filenames are not supported, then why does it explicitly tell you how to use filenames with spaces in the SQL*Plus documentation?

"You must use quotes around file names containing white space."

The installation manual says not to install the Oracle home into a directory containing spaces, but nothing about long filenames. Many of the Oracle files in the Oracle home itself are long filenames, i.e. longer than 8.3, so clearly Oracle supports long filenames.

>To spool a file without extension, just add a trailing dot.

Nope, also not true. It used to, but of course this was undocumented, and has now been "fixed" (for some definition of "fixed"):

C:\files\i like spaces>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 9 23:23:57 2006

SQL> spool test.
SQL> host dir
Volume in drive C has no label.
Volume Serial Number is 1015-D37B

Directory of C:\files\i like spaces

09/10/2006  23:24    <DIR>          .
09/10/2006  23:24    <DIR>          ..
09/10/2006  23:24                 0 test..LST
               1 File(s)              0 bytes
               2 Dir(s)  56,440,365,056 bytes free

Doesn't using 10.2 either:

C:\files\i like spaces>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 9 23:23:03 2006

SQL> spool test.
SQL> host dir
Volume in drive C has no label.
Volume Serial Number is 1015-D37B

Directory of C:\files\i like spaces

09/10/2006  23:23    <DIR>          .
09/10/2006  23:23    <DIR>          ..
09/10/2006  23:23                 0 test..LST
               1 File(s)              0 bytes
               2 Dir(s)  56,440,369,152 bytes free

Nor on Linux 10.2:

[oracle_at_testbox spool]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 9 23:04:54 2006

SQL> spool test.
SQL> host ls
test..lst

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Mon Oct 09 2006 - 17:30:10 CDT