SQL Plus SP2-0606

Question

Using SQL tool, I want to create a text file that has a VARCHAR records.

Using SQL Developer I have successfully created the output text file by calling a script. But based on my research, the TRIMSPOOl command is currently not supported by SQL Developer, making my output to have trailing spaces.

Here is the code in the script:

    spool on
    set echo off
    set verify off
    set feedback off
    set heading off
    set trimspool on
    set termout off
    spool C:\SQLFiles\Output.txt;
    select cust_name || cust_addr as Cust_Details
    from customer_db;
    spool off;
    

I now have the SQL Plus 11g, and I'm trying to run the script i created in SQL Developer. I'm getting an SP2-0606 error, saying:

    SP2-0606: Cannot create SPOOL file "on.LST:
    

Based on the research I did, it is because of the Spool command, and I don't have the right to access the default folder??..

Can you help please on what setup I should change do to make the desired result in SQL Plus.

Answer 1

You have to put the file name in double quotes - I guess SQLPlus uses C-style escape sequences, giving the backslash \ a special meaning. Also, you should remove the semicolon ';':

So if you replace

spool C:\SQLFiles\Output.txt;

with

spool "C:\SQLFiles\Output.txt"

it should work as expected (if C:\SQLFiles exists and is writable for your account).

UPDATE

As @LalitKumarB has pointed out, spool works perfectly fine without the double quotes. The real problem is elsewhere:

spool on

This tells SQLPlus to create an output file on in the current directory. So you'll get this error message if you don't have access to the directory you're starting SQLPlus from.