Remove Blank Line In Between Select Queries When Spooling to CSV File 2-CodePudding

I have query that is executed from SQLPLUS (11.2.0.4) and output is spooled to file. I am getting output file empty lines after each line many empty spaces after the last column in the query. I have opened the file in notepad and i can see tons of empty spaces appended to the last column and ended with CRLF ( windows) , the last column is varchar 4000.

I have read tons of articles related to the same in internet but none of them help me. I have tried to set trimspool ON/OFF, trimout ON/OFF (btw - both commands are skipped ), page 0, heading off, pagesize 0, page 0, space 0, newpage nono

Example of SQL :

set termout off
set pagesize 0
set termout off
set pagesize 0
set heading off
set feedback off
set newpage none
set space 0
set linesie 8000
set longchunksize 200000 
/*above was tried step by step - no help*/

spool "G:/gggg/fffff.csv"
PROMT COL1|COL2|COL3

select col1||';'||col2||';'||nvl(col3,'') abc
FROM transactions;
spool off;

output (notepad  ):
COL1|COL2|COL3CRLF
col1|col2|col3                                                              CRLF
col1|col2|col3                                                              CRLF
col1|col2|col3                                                              CRLF

output(linux)
COL1|COL2|COL3$
col1|col2|$
COL3      $

col1|col2|$
COL3      $

col1|col2|$
COL3      $

CodePudding user response：

What's your table look like? Is COL3 defined as a CHAR or VARCHAR2? If a CHAR, that explains everything.

We need to see your data and your table, to be able to help you, otherwise you're asking us to guess.

Using the HR sample schema and the locations table, notice the varchar2's.

CREATE TABLE locations (
    location_id    NUMBER(4, 0),
    street_address VARCHAR2(40),
    postal_code    VARCHAR2(12),
    city           VARCHAR2(30)
        CONSTRAINT loc_city_nn NOT NULL ENABLE,
    state_province VARCHAR2(25),
    country_id     CHAR(2),
    CONSTRAINT loc_c_id_fk FOREIGN KEY ( country_id )
        REFERENCES hr.countries ( country_id )
    ENABLE
);

COUNTRY_ID is CHAR(2) - so you'll always see 2 spaces for that column, even if you only inserted '1' character.

Querying the table, with sqlformat set to CSV, I get CSV back, automatically.

SQLPlus 11.2.0.4 is approx from 2008, that's quite old.

Here's what you can do with

If you can update your question with sample DDL and data, we can give you a better answer.

Disclaimer: I'm a product manager at Oracle for our Database tools.

CodePudding user response：

Thank you for answer, I will try to share as much as possible.

To change SQLPLUS is not an option.
it is requested to have output file without double quotes and with tilda as delimiter e.g.

aaaa~bbbb~cccc~eeeeeeeeeeeeeeeeeeeeeeeeee
dddd~rrrr~bggggggg~rrrrrrrrrrrrrrrrrrrrrrrrrr
eeee~rrrrrrr~ttttttt~yyyyyyyyyyyyyyyyyyyyyyyyyy

Columns type :

col1 integer
col2 varchar2(4000 BYTE)
col3 varchar2(4000 BYTE)

Current output looks like

COL1NAME~COL2NAME~COL3NAME
aaaa~bbbb~cccc~eeeeeeeeeeeeeeeeeeeeeeeeee

dddd~rrrr~bggggggg~rrrrrrrrrrrrrrrrrrrrrrrrrr

eeee~rrrrrrr~ttttttt~yyyyyyyyyyyyyyyyyyyyyyyyyy

I would like to remove the empty lines without any cut of the characters in lines.

I can see in notepad :

COL1NAME~COL2NAME~COL3NAMECRLF
aaaa~bbbb~cccc~eeeeeeeeeeeeeeeeeeeeeeeeee..................................CRLF
dddd~rrrr~bggggggg~rrrrrrrrrrrrrrrrrrrrrrrrrr..............................CRLF
eeee~rrrrrrr~ttttttt~yyyyyyyyyyyyyyyyyyyyyyyyyy............................CRLF

I can see in linux (after less) :

COL1NAME~COL2NAME~COL3NAME$
aaaa~bbbb~cccc~eeeeeeeeeeeeeeeeeeee$
eeeeee                             $
                                   $
dddd~rrrr~bggggggg~rrrrrrrrrrrrrrrr$
rrrrrrrrrr                         $
                                   $
eeee~rrrrrrr~ttttttt~yyyyyyyyyyyyyy$
yyyyyyyyyyyy                       $
                                   $