Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, raj.

Asked: October 24, 2001 - 2:03 pm UTC

Last updated: April 16, 2020 - 11:36 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

Hi Tom,
I have a varchar2(30) field which when displayed on sqlplus, doesn't seem to be trimming the trailing spaces when I use rtim or trim in select stmt:

set head off
set colsep ","
set trim on
set wrap off
set linesize 800
select part_id,
'CATEGORY',
rtrim(part_desc),
serial_num
from parts

345,CATEGORY,MOTOR BELT ,031-14-024144


What could be wrong?
Thanks,
Raj


and Connor said...


It is trimming it -- its just that sqlplus formats the results into columns based on their widths. sqlplus is a reporting tool -- it likes to print things "pretty".

If you want them squashed together, you would:

select part_id || ',CATEGORY,' || part_desc || ',' || serial_num from parts;

See
</code> http://asktom.oracle.com/~tkyte/flat/index.html 
for a simple tool that'll help you do this. 

=====================

Addenda 2017:

Easy ways to achieve the 'trim' option

1) Use SQL Plus version 12.2

<code>
SQL> set markup csv on
SQL> select * from scott.emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"09-DEC-82",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,,30
7876,"ADAMS","CLERK",7788,"12-JAN-83",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10


2) Use SQLcl

Download from here

http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html 

SQL> select /*csv*/ * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10


Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

linesize below 80

korada, March 28, 2002 - 2:19 pm UTC

using sql plus formatting commands i am writing query data to a flat file.

there i gave linesize as 300(set linesize 300). now when the result data line size becomes below 80 its adding blank spaces at the end of the text

these are the contents of sql file

set heading off
set termout off
set verify off
set feedback off
set linesize 300
spool &1..dat
select
'ForCompany|XXXXX|XXXXXXXXXXXXX|S|'||nvl(em_id_c,'<LLNULL>')||'|1|'|| nvl(title_c,'<LLNULL>')||'|<LLNULL>|<LLEOR>|' to_file from em_title where actv_i='Y';
spool off
set linesize 80
set feedback on
set heading on
set termout on
set verify on

any idea why is this happening

Tom Kyte
March 28, 2002 - 5:14 pm UTC

Well, you could just grab my flat script -- it does all of this for you.

All you need to add is:

set trimspool on


to trim the output.

unloading data in the form of insert statements

A reader, May 23, 2003 - 11:53 am UTC

Hi Tom,
I used your utl_file method to unload data in CSV format. Works excellently! Do you a similar script that would unload data as a bunch of insert statements that could later be called from a sql script to load data back in?
For example, instead of:
123,John Smith
234,Jane Smith
...

I have a need to see:
insert into employee(emp_id,emp_name) values (123,'John Smith');
insert into employee(emp_id,emp_name) values (234,'John Smith');
...

Any help you can provide?
Thanks.


Tom Kyte
May 23, 2003 - 12:05 pm UTC

not a chance, wouldn't do it. it would kill the recieving system.

I do unload a sqlldr format file for easy reload though.

This is for very tiny tables

A reader, May 23, 2003 - 12:28 pm UTC

Tom, thanks for your reply. We are not talking massive data here.. just a handful of records that get loaded as part of static, reference data during initial schema creation at the time of system deployment. I have a bunch of sql scripts that create the initial schema and I just thought it would be easier for me to integrate a sql file with a few insert statements rather than call sqlldr.
Thanks.

gre, August 24, 2003 - 10:25 pm UTC

Tom,
I have a script to spool a table to a flat file written by a programmer before.
The the set commands in the script are very simalr with yours.But he has
a command " set space 0 " . What is this command means here ? I could not find this set command in the oracle dcoument. (I guess it probably was used in oracle 7 ,if so
we have related set command in later versions to handle this if necessary)

Thanks !


Tom Kyte
August 25, 2003 - 6:32 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a90842/apc.htm#634746 <code>



CHR(10) in SQL*Plus output

Rumburak, May 24, 2006 - 8:04 am UTC 

Hi Tom,

In my UNION ALL query I have a CHR(10) in a column. The break works, but I can't understand the break before the second row:

SQL> SELECT 'Hallo 1a' || CHR (10) || 'Hallo 1b' FROM dual UNION
  2  SELECT 'Hallo 2'                            FROM dual UNION
  3  SELECT 'Hallo 3'                            FROM dual
  4  ;

'HALLO1A'||CHR(10
-----------------
Hallo 1a
Hallo 1b

Hallo 2
Hallo 3

SQL>

Do You have an answer?

Bye,

Rumburak

Tom Kyte
May 24, 2006 - 8:07 am UTC 

ops$tkyte@ORA10GR2> SELECT 'Hallo 1a' || CHR (10) || 'Hallo 1b' FROM dual UNION
  2  SELECT 'Hallo 2'                            FROM dual UNION
  3  SELECT 'Hallo 3'                            FROM dual
  4  ;

'HALLO1A'||CHR(10
-----------------
Hallo 1a
Hallo 1b

Hallo 2
Hallo 3

ops$tkyte@ORA10GR2> set recsep off
ops$tkyte@ORA10GR2> @test
ops$tkyte@ORA10GR2> SELECT 'Hallo 1a' || CHR (10) || 'Hallo 1b' FROM dual UNION
  2  SELECT 'Hallo 2'                            FROM dual UNION
  3  SELECT 'Hallo 3'                            FROM dual
  4  ;

'HALLO1A'||CHR(10
-----------------
Hallo 1a
Hallo 1b
Hallo 2
Hallo 3

Thanks Tom!

Rumburak, May 24, 2006 - 10:18 am UTC

Thanks Tom for your answer.

Bye,

Rumburak

new line chars in column in spool file

Bhasker, June 12, 2008 - 7:04 pm UTC

Hi Tom,
I am trying to download data from Oracle to flat file spooling data from sqlplus.
However, for one column, we have newline characters inside one column. Data is not downloading properly into flat file. Even if it downloads, one column data gets into multiple lines. Any ideas how to avoid this and file download?. This downloaded file has to be loaded to another system, so using | as delimiter.


set feedback off
set echo off
set verify off
set pagesize 0
set heading off
set termout off
--set recsep off
--set trim on
set wrap on
set trimspool on
set linesize 9000

spool C:\spool_v2.txt ;
select to_char(local_time,'yyyy-mm-dd hh.mi.ss')||'|'|| country_code_id ||'|'|| pattern_search||'|'||wildcard_type||'|'||pattern_match
from MPDICTIONARY where rowid = 'AAAGVkADYAAAVsKAAA' ;

spool off;

set feedback on
set echo on

Instead of one line, data spreads into multiple lines. I tried with set wrap off, but only first line is shown from below output.
Rest of 4 lines due to newlines are not loaded into flat file.
spool_v2.txt
2007-05-01 12.00.00|9|les r\%e9sultats (disponibles \%e0 partir de 20h, au fur et \%e0 mesure des d\%e9pouillements)|0|les r
%e9sultats (disponibles
%e0 partir de 20h, au fur et
%e0 mesure des d
%e9pouillements)

Thanks for your suggestions!
Bhasker
Tom Kyte
June 12, 2008 - 7:32 pm UTC

well, a newline will tend to do that.

what would you LIKE to be there?

say you want "\n" (common in csv files)

then just select replace( field, chr(10), '\n')

or replace( field, chr(13)||chr(10), '\n')

depending on windows or regular operating system - windows uses two characters for a newline (inherited behavior from DOS)

Thanks!

Bhasker, June 12, 2008 - 7:52 pm UTC

Thanks Tom!
This is working fine and i could see newlines replaced by \n.
However, is there any set like command which looks for newlines inside columns and replaces newlines inside a column with \n?.
This particular issue with newlines has come for 2 or 3 tables for only few rows, so just thinking if there's a set command, we could directly set that in import sql script.

Thanks for helping out again...:)
Tom Kyte
June 12, 2008 - 8:26 pm UTC

there is no "set" command to make newlines (just a character to us, we don't do anything special, we just call printf() in C) be replaced by something else.

new line after each node

Serge F., July 15, 2008 - 2:16 pm UTC

I've got somehow similar issues in formatting the spool off from SQL Plus. I have an XMLType column from where I extract 1 row (and only one from the xmltype column) and I spool it off in an xml file.
After spooling it looks like this:
<ReconcileTransactionRequest><ReconciliationBatchId>1</ReconciliationBatchId><Re
conciliationRecords><ReconciliationRecord><recordID>152</recordID><transaction_type>SWAP</transaction_type><transactionAmount>32</transac
tionAmount><denomination>USD</denomination><processed_date>2008-07-02</processed
_date></ReconciliationRecord><Recon
ciliationRecord><recordID>154</recordID>...

How can I format this output in a nice xml tree format ?

<ReconcileTransactionRequest>
<ReconciliationBatchId>1</ReconciliationBatchId>
<ReconciliationRecords>
<ReconciliationRecord>
<recordID>152</recordID>
<transaction_type>SWAP</transaction_type>
<transactionAmount>32</transactionAmount>
<processed_date>2008-07-02</processed_date>
</ReconciliationRecord>
<ReconciliationRecord>
<recordID>154</recordID>........
.....................................
<\ReconcileTransactionRequest>

Tom Kyte
July 15, 2008 - 8:11 pm UTC

xml has no "shape" like that - it does not care at all - it is a formatting thing like xml spy that makes it nice for humans.

sqlplus is going to be very very very limited here - you'd be best off using a real piece of code to create your production files (error handling, notification of errors, error logging, etc - you need all of that)

and you need no whitespace at all, just pull it up in xml spy or IE or whatever to see it "pretty"

sqlplus

A reader, March 02, 2009 - 11:54 pm UTC

Tom:

is there a way to get a 4 byte column heading for the result set when the actual field is 1 byte wide. I used rpad and it worked but then i tried to query and data was not found based on 1 byte query.

Users will be using the view to query in one row format. They wont enter column stage format a4. I need to set that in the view.

stages
--------
vidno number(10),
stage varchar2(1),
statcd varchar2(1)

create or replace view vw_vid as
SELECT vidno,
max( decode( stages, 0, statcd, null) ) STG0,
max( decode( stages, 1, statcd, null) ) STG1,
max( decode( stages, 2, statcd, null) ) STG2,
max( decode( stages, 3, statcd, null) ) STG3,
max( decode( stages, 4, statcd, null) ) STG4,
max( decode( stages, 5, statcd, null) ) STG5,
max( decode( stages, 6, statcd, null) ) STG6,
max( decode( stages, 7, statcd, null) ) STG7 FROM (SELECT vidno,stage,statcd
FROM (select a.*, dense_rank() over (partition by vidno,stage order by seq_no desc) as rnk FROM stages a ) A
WHERE rnk = 1 order by vidno )
group by vidno
/

Tom Kyte
March 03, 2009 - 4:21 pm UTC

I hope they query by vidno and optionally by stage or statcd - else this is going to be a slow slow view.

more details, give the query against the view, if you do this sort of padding in the view, almost surely any index you are expecting to use will be "avoided"

Your users really use sqlplus as their interface to the database??? Really? I mean, I do, but that is the exception, not the rule.


And why couldn't they use format??? if they are smart enough to use sqlplus, it seems likely they could be relied on to actually format their data - they are using the most rudimentary of tools after all...

sqlplus

A reader, March 03, 2009 - 6:41 pm UTC

The queries will be run using Winsql Lite

show me videos that has stage 0 rejected
select * from vw_vid where STG0 = 'R'

show me videos that has stage 0 approved and stage 4 approved
select * from vw_vid where STG0='A' or STG1 = 'A'


Do you see any issues with performance ?

I did a cast to varchar2 in the inner query and it seems to have worked. any issue with that.

SELECT vidno,
max( decode( stages, 0, statcd, null) ) STG0,
max( decode( stages, 1, statcd, null) ) STG1,
max( decode( stages, 2, statcd, null) ) STG2,
max( decode( stages, 3, statcd, null) ) STG3,
max( decode( stages, 4, statcd, null) ) STG4,
max( decode( stages, 5, statcd, null) ) STG5,
max( decode( stages, 6, statcd, null) ) STG6,
max( decode( stages, 7, statcd, null) ) STG7
FROM
(SELECT vidno,stage,cast(statcd as varchar2(5)) STATCD
FROM (select a.*, dense_rank() over (partition by vidno,stage order by seq_no desc) as rnk FROM stages a ) A
WHERE rnk = 1 order by vidno )
group by vidno
/
Tom Kyte
March 03, 2009 - 9:38 pm UTC

... Do you see any issues with performance ? ...

sure, don't you?

The entire result set would have to be built AND THEN filtered.

if your end users are using something as rudimentary as 'winsql lite', one would think "format col a4" would not be beyond them.



Alexey, April 23, 2009 - 10:51 am UTC

Hi Tom,

I have a VARCHAR2(255 BYTE) field which can contain a blank characters at the beginning of the value and at the end of value also. I need to spool these values to a file. But I don't need to have a blank characters that appear at the end of line when I try to spool result of query. So I typing "set trims on" and getting values without unnecessary blank characters but I also loosing blank characters that were present at the end of value(((
Do you have idea that can help me to solve this problem?
Tom Kyte
April 27, 2009 - 11:05 am UTC

short of not having this be the last column - if you trimspool, it'll trim it - it is just whitespace.

so, you'll either need to make this be NOT the last column or concatenate quotes on it - eg: '"'||column || '"'

XML in a single line

Rajesh, January 12, 2011 - 4:31 am UTC

Hi Tom,
I think you are wrong in answering the question in which the XML output of SQLPLUS breaks into several lines. You said XML has no 'shape'. yes thats right. But in this case, if the SQLPLUS is giving breaking the nodes in between such that the opening tag ans closing tag are in different lines. So the final output is not really an XML. If you open the XML in IE/XMLSpy IDE it is going to give error.
Hope you understood the real problem. It would be solved only if the SQLPLUS gives the full output in a single line!!
Tom Kyte
January 12, 2011 - 10:59 am UTC

I was simply responding to the statement they made:

How can I format this output in a nice xml tree format ?


sqlplus will be limited - it can put it on a single line (that is trivial, set linesize N)

so in answer to their request - XML has no 'shape'!!!

Format XML

Henry Cortez Wu, May 12, 2011 - 10:35 pm UTC

See the following link to format XML output
http://www.oratechinfo.co.uk/sqlxml.html#point5

In summary, just use EXTRACT('/*')

e.g. from the link above:

SELECT XMLELEMENT("test", XMLELEMENT("test2", NULL),
XMLELEMENT("test3", NULL)).EXTRACT('/*')
FROM dual;

Link don't work

Raju, April 30, 2012 - 11:04 am UTC

Hi Tom,

the Link below provided is not accessable, is that updated? if so, could you please share the updated link?

http://asktom.oracle.com/~tkyte/flat/index.html

thank you.
Tom Kyte
May 01, 2012 - 3:22 pm UTC

look for the data unloader link here

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyte.html

Link no longer works.

Steve, November 01, 2017 - 9:32 pm UTC

Is there an updated link to this utility? Thx
Connor McDonald
November 02, 2017 - 1:16 am UTC

Easy ways to achieve this:

1) Use SQL Plus version 12.2

SQL> set markup csv on
SQL> select * from scott.emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"09-DEC-82",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,,30
7876,"ADAMS","CLERK",7788,"12-JAN-83",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10


2) Use SQLcl

Download from here

http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html 

SQL> select /*csv*/ * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17/DEC/80,800,,20
7499,"ALLEN","SALESMAN",7698,20/FEB/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/FEB/81,1250,500,30
7566,"JONES","MANAGER",7839,02/APR/81,2975,,20
7654,"MARTIN","SALESMAN",7698,28/SEP/81,1250,1400,30
7698,"BLAKE","MANAGER",7839,01/MAY/81,2850,,30
7782,"CLARK","MANAGER",7839,09/JUN/81,2450,,10
7788,"SCOTT","ANALYST",7566,09/DEC/82,3000,,20
7839,"KING","PRESIDENT",,17/NOV/81,5000,,10
7844,"TURNER","SALESMAN",7698,08/SEP/81,1500,,30
7876,"ADAMS","CLERK",7788,12/JAN/83,1100,,20
7900,"JAMES","CLERK",7698,03/DEC/81,950,,30
7902,"FORD","ANALYST",7566,03/DEC/81,3000,,20
7934,"MILLER","CLERK",7782,23/JAN/82,1300,,10


SET SQLFORMAT from SQLCL

Rajeshwaran Jeyabal, November 03, 2017 - 12:25 pm UTC

with SQLCL you don't even need to add the hint's CSV to the SQL. just use the SET SQLFORMAT from command line like this.

demo@ORA11G> $type d:\script.sql
set linesize 131
set pagesize 9999
set sqlformat csv
select * from dept;


demo@ORA11G> @d:\script.sql
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"


demo@ORA11G>

Trailing space issue from spool query

Sridhar, April 09, 2020 - 10:14 am UTC

Hi Tom,

I have tried to export the csv file using spool query for the below settings.
I can see the trailing space in some of the columns, and same when I was trying to import into another database using sqlloader, I can see the space in the column values,
could you please suggest, do I need to do any changes while exporting or importing.

set colsep "~"
set linesize 999
set trimspool on
set heading off
set pagesize 0
set wrap off
set feedback off
set newpage 0
set arraysize 50000
spool LVV_DEEL_LEVERING3.csv;
select HL_ID,
DL_VOLG_NR,
DL_DAT_LAATSTE_WIJZ,
rtrim(DL_USER_LAATSTE_WIJZ),
DL_WERKORDER_ID,
DL_DAT_PLAN_GEREED,
DL_DAT_ANN_LEVERING,
DL_DAT_ANN_WERKORDER,
DL_DAT_GEREED,
DL_OMSCHR,
rtrim(NV_CODE),
rtrim(OP_GEVER_CODE),
rtrim(OP_NEMER_CODE),
VL_ID,
rtrim(DL_BEHANDELAAR),
DL_AFGEHANDELD,
DL_DAT_AFGEHANDELD,
DL_OORZAAK_ANNULER from LVV_DEEL_LEVERING where DL_DAT_LAATSTE_WIJZ >= sysdate-30;
spool off;
Connor McDonald
April 16, 2020 - 11:36 am UTC

We would need to see some data, but also, perhaps check out "set markup csv" in more recent versions of SQL Plus. It will do it all for you
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library