This discussion is archived
3 Replies Latest reply: Oct 10, 2012 2:13 AM by GauravBhide RSS

Spool query result to file

936341 Newbie
Currently Being Moderated
Hi everyone,

I would like to spool the results of my query to a CSV file with a delimiter ';' on a hard drive. I use Toad for Oracle, my script worked fine previously on version 9, and now I have version 10.1, and it doesn't work anymore.
What happens is I do get the output alright, but for some reason the output creates blank spaces at the end of each line, but only where the last column value for the line is 0.

For example, I get something like this:

Let's say I have an output of 5 columns, first 3 being string and the 4th and 5th being numbers

A B C D E
---------------------------------------------------------------------------------------
stringA stringB stringC 1.01 26.2
stringA stringB stringC 10.002 0
stringA stringB stringC 23 1001.1001

In CSV this looks like this:

A;B;C;D;E
stringA;stringB;stringC;1.01;26.2
stringA;stringB;stringC;10.002;0
stringA;stringB;stringC;23;1001.1001

The first and third row would appear fine, but the second row (the one that ends with a zero) would have a bunch of blank spaces after the zero.

Here is the script that I use:

-------------------------------------------------------------------------------------------------------------------------------------------

SET PAGESIZE 0 LINESIZE 2000 TRIMSPOOL ON FEEDBACK OFF ECHO OFF TERMOUT OFF;

SPOOL "C:\SPOOL_RESULT.CSV"

SELECT
'A'||';'||
'B'||';'||
'C'||';'||
'D'||';'||
'E'
FROM DUAL
UNION ALL
SELECT
A||';'||
B||';'||
C||';'||
D||';'||
E
FROM SOME_TABLE;

SPOOL OFF;

CLEAR BUFFER;

-------------------------------------------------------------------------------------------------------------------------------------------

My opinion is that this TRIMSPOOL doesn't work as effectively as it should. I've tried several others, such as TRIMOUT, but nothing seems to work.

I could bypass this by setting a 'dummy' column at the end that would always have some value <> 0, say 1, but this is not a clean solution.

Any ideas?

Thanks
  • 1. Re: Spool query result to file
    908002 Expert
    Currently Being Moderated
    use set colsep ';' before spool command
  • 2. Re: Spool query result to file
    916175 Newbie
    Currently Being Moderated
    Uisng the colsep',' before the spool it doesnt work, i have tried that many times.
    I also have a problem of the columns not been displayed properly on the .csv file, i get values like that, with spaces
    122982130122 ,604470871,436641000015 ,A1NV12_1 ,XC ,232011030000015,1
    436641000015,122982130122

    one of others solutions was not to use the colsep',', just by calling the columns1||'.'||, that somehow prints the sqlplus commands (the columns headers),
    so i'm out of ideas.

    Any help
  • 3. Re: Spool query result to file
    GauravBhide Newbie
    Currently Being Moderated
    Hi,

    Try this one.
    SET PAGESIZE 0 LINESIZE 2000 TRIMSPOOL ON FEEDBACK OFF ECHO OFF TERMOUT OFF;
    
    SPOOL "C:\SPOOL_RESULT.CSV"
    
    SELECT
    'A'||';'||
    'B'||';'||
    'C'||';'||
    'D'||';'||
    'E'
    FROM DUAL
    UNION ALL
    SELECT
    A||';'||
    B||';'||
    C||';'||
    D||';'||
    trim(E)
    FROM SOME_TABLE;
    
    SPOOL OFF;
    CLEAR BUFFER;
    Trim will remove all the blank spaces.
    SQL> select length('0      ') from dual;
    
    LENGTH('0')
    -----------
              7
    
    SQL> select length(trim('0      ')) from dual;
    
    LENGTH(TRIM('0'))
    -----------------
                    1

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points