1 Reply Latest reply: Nov 9, 2012 1:23 AM by user346369 RSS

    Issue with SPOOL


      I am using SPOOL to extract data from a Oracle database into a CSV file
      When I open the CSV file, it is all good with the data I need.

      But, When I change the file to .txt format, all data comes up in a single line

      This is causing problem when I FTP the file.
      The destination application receives the file with data in a single line.

      Is there any way I can avoid this while spooling ?
      I am currently using the below

      set echo off;
      set verify off;
      set termout off;
      set linesize 1000;
      set trimspool on;
      set heading off;
      set newpage none;
      set tab off;
      set space off;

      I also tried getting a text file directly from SPOOL.
      Even with that the data comes in a single line

      Please advise
        • 1. Re: Issue with SPOOL
          Well, this is a Forms forum, and you are asking about a SQL Plus issue, but I'm not sure where you should ask, so I'll give you my thoughts...

          I think you have yourself tangled up in the differences between a Windows/DOS based platform and a Unix-based system, and the changes that may occur when you move the file between the two systems.

          DOS (and Windows) puts two characters at the end of each line: a CR and LF -- chr(10) and chr(13)

          unix, on the other hand, puts one character at the end: a CR -- chr(10)

          CR=Carriage return, LF=Line Feed. Depending on which system you are running SQL Plus, you will get the different end of line treatment. It does NOT depend on whether you spool to a filename without .txt or with .txt.

          When you move your file between the two systems, SOME transfer programs use the file extension (.txt or .lst) to decide whether to convert the file or not. If you override the auto-translate feature, and tell your transfer process to treat it as a binary file, no conversion will occur.

          And also, depending on how you display the contents of your file, you may see the data as a single line, or you may see it as multiple lines.

          You can manually translate file types on a Windows platform by downloading two programs from the internet: dos2unix.exe and unix2dos.exe -- I have those two programs installed in my Windows 7 platform (forgot how I did), so I can right-click a file, and select open with... and choose either of the two programs, to convert the file in a flash.

          When the file is in unix format, there are some editors that run on Windows, that will display multiple lines even though the file is in unix format. But Windows Notepad will not, so I use Notepad to view a file to determine which mode it is using.

          I realize this is confusing. So good luck!