Oracle spool too large

Writing some table dumps using spool command. problem with linesize limit of 2000. The spool command seems to pad each feild out to it;s maximum length - e.g. I have a comments field in a table which is char(4000). even when this is empty, spool puts out 4k blank spaces which pushes me over the linesize limit so I get each row spread across multiple lines. This in turn makes analyzing the result painful.
I have the following parameters set for the spool

set colsep ,    
set pagesize 0  
set trimspool on
set headsep off  
set linesize 2000
set echo off
set feedback off

Is there anything I can do to have the individual fields trimmed?
Alternative solutions also appreciated
jorbagwAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
>> Is there anything I can do to have the individual fields trimmed?
Well ... you could try TRIM() ... e.g.

SELECT trim(col1)||','||trim(col2)||','||trim(col3)||','||trim(col4) .. etc

You will need to manually concatenate to avoid SQL*Plus padding the columns.
0
 
OP_ZaharinCommented:
- have you tried using SET  TRIMSPOOL ON?

- also try using the free Oracle SQL Developer? you just need to run the sql-query then right-click on the result grid and then select export data to save the results into a text file.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
0
 
Swadhin RaySenior Technical Engineer Commented:
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
slightwv (䄆 Netminder) Commented:
>>http://www.experts-exchange.com/Database/Oracle/Q_27841512.html

Unfortunately this is loading a delimited flat text file into Oracle, not exporting it.

I believe you are looking for the question that solved the issues used in the code in your blog:
http://www.experts-exchange.com/Database/Oracle/Q_27832701.html


Odd,
I get your code working on 8/20 and your Blog post is 8/21.  Might be nice to reference the site that made that blog post possible.


Anyway I don't think that code will work here since the column_value in your code is 2000 characters.  It should work with 4000 characters but it will need to be tweaked/tested.
0
 
Swadhin RaySenior Technical Engineer Commented:
@slightwv : Yes you are correct I provided the wrong link for the question.

The correct link is :
http://www.experts-exchange.com/Database/Oracle/Q_27841512.html

As you mentioned earlier.

And only blog I also mentioned the same. And I thought of sharing boz it might help others too.
0
 
slightwv (䄆 Netminder) Commented:
>>You will need to manually concatenate to avoid SQL*Plus padding the columns.

Unfortunately this will probably not work here since this is limited to a 4000 character string limit for all columns and one of the columns is possibly 4000 characters itself.

There are a ton of links out there on string aggregation in Oracle.  I prefer the XML approach that can return a CLOB.

http://www.experts-exchange.com/Database/Oracle/Q_24914739.html#a25864822
0
 
lwadwellConnect With a Mentor Commented:
>> Unfortunately this will probably not work here since this is limited to a 4000
Of course .. the 4000 limit on varchar2.  But what keeps bugging me is the OP's original statement:
>> problem with linesize limit of 2000
I am sure that a larger linesize limit is possible (32K from memory)
0
 
jorbagwAuthor Commented:
Have tried the xml solution and it's incredibly slow -may be something to do with the setup here, but not something I can do much about.
0
 
jorbagwAuthor Commented:
Didnt really get a solution that would solve the problem. All contributors helpful, but nothing definitive
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.