ignore column width in sqlplus

Question

I'm getting the follow

SQLPLUS> create table source(aa nvarchar2(2), bb nvarchar2(100), cc nvarchar2(10));
insert into source values ('aa',null,'aa');
insert into source values (null,'aa','aa');
insert into source values ('aa','aaa',null);
set serveroutput on pages 0 feedback off echo off head off verify off numformat 99999999999.9999999999999 null '\N' colsep ' ' linesize 32767
select * from source;
aa \N aa
\N aa aa
aa aaa \N

I want to ignore the column width and show the result as <value><colsep><value> without touching the SQL (only the commands before it or sqlplus). So the result will look like:

aa \N aa
\N aa aa
aa aaa \N

(representing tab as two spaces)

• pmdba: What version of Oracle and sqlplus are you using?
• Nir: version 21.0.0.0.0

Answer 1

You could try using CASE and concatenation || in one statement.

select 
 (CASE WHEN AA IS NULL THEN N'\N' ELSE AA END)
 || CHR(9) || 
 (CASE WHEN BB IS NULL THEN N'\N' ELSE BB END)
 || CHR(9) || 
 (CASE WHEN CC IS NULL THEN N'\N' ELSE CC END)
 ResultSet 
 FROM source;

This returns:

RESULTSET
aa \N aa
\N aa aa
aa aaa \N

The db<>fiddle link displays the working code using your example data.

• Nir: I should have said, I can't touch the SQL itself, only what comes before like set or sqlplus

Answer 2

Try "set markup csv on":

sqlplus /nolog
connect user/pass/@db
set markup csv on
select * from source
/