[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle spool too large

Posted on 2012-09-04
9
Medium Priority
?
1,866 Views
Last Modified: 2012-09-12
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
0
Comment
Question by:jorbagw
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 38366813
- 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
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 38367566
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38367664
>>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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 38368257
@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
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38370721
>> 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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38370736
>>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
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 1000 total points
ID: 38370772
>> 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
 

Author Comment

by:jorbagw
ID: 38393747
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
 

Author Closing Comment

by:jorbagw
ID: 38393811
Didnt really get a solution that would solve the problem. All contributors helpful, but nothing definitive
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question