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

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 09 Oct 2006 22:52:32 +0100
Message-ID: <7lgli2d29p1bc9fdc4g52k6d088flpojip@4ax.com>

On 9 Oct 2006 07:46:37 -0700, "Greg" <esabens_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.

You were quite close; with double quotes it does work:

C:\>sqlplus test/test_at_xe_excession

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 9 22:42:23 2006

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> column date_column new_value today
SQL> SELECT 'c:\files\i like spaces\'||to_char(sysdate,'yymmdd')||'.txt' 2 date_column FROM dual;

DATE_COLUMN

c:\files\i like spaces\061009.txt

SQL> spool "&today"
SQL> select sysdate from dual
2 ;

SYSDATE

09-OCT-06 SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ
ction

C:\>cd c:\files\i like spaces\

C:\files\i like spaces>dir
Volume in drive C has no label.
Volume Serial Number is 1015-D37B

Directory of C:\files\i like spaces

09/10/2006  22:42    <DIR>          .
09/10/2006  22:42    <DIR>          ..
09/10/2006  22:42               300 061009.txt
               1 File(s)            300 bytes
               2 Dir(s)  57,289,240,576 bytes free

-- 
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 - 16:52:32 CDT