Home » Other » Client Tools » How to spool the data of a clob or long column
How to spool the data of a clob or long column [message #434613] Wed, 09 December 2009 23:19 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

How to spool the data of a clob or long column from table to flat file from sqlplus command prompt.


Thanks in advance

Re: How to spool the data of a clob or long column [message #434614 is a reply to message #434613] Wed, 09 December 2009 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vikram1780 wrote on Wed, 09 December 2009 21:19

How to spool the data of a clob or long column from table to flat file from sqlplus command prompt.


Unable or unwilling to run simple test yourself?

SQL> @spool_clob.sql
SQL> create table clob_temp (my_clob clob);

Table created.

SQL> spool my_clob.lis
SQL> insert into clob_temp values ('Junk Data');

1 row created.

SQL> select * from clob_temp;

MY_CLOB
--------------------------------------------------------------------------------
Junk Data

SQL> spool off

Re: How to spool the data of a clob or long column [message #434616 is a reply to message #434614] Wed, 09 December 2009 23:36 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi Swan,

Thanks for your reply.

But the data in clob column is having more than 30 or 40 lines. In that case part of data is getting truncated while spooling into file or not properly aligned.

Thanks for your reply
Re: How to spool the data of a clob or long column [message #434618 is a reply to message #434616] Wed, 09 December 2009 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But the data in clob column is having more than 30 or 40 lines. In that case part of data is getting truncated while spooling into file or not properly aligned.

Then SPOOL is not a viable solution for you.
We can not change any limitations that SPOOL imposes on you.

Sometimes you must accept reality as it is or develop your own solution to work around existing limitations.

You need to be smarter that the equipment your are working with.
Re: How to spool the data of a clob or long column [message #434637 is a reply to message #434616] Thu, 10 December 2009 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In that case part of data is getting truncated while spooling

set long ...
set longchunksize ...
set linesize ...

Regards
Michel
Re: How to spool the data of a clob or long column [message #677806 is a reply to message #434637] Sat, 12 October 2019 00:59 Go to previous messageGo to next message
Glearn@2019
Messages: 1
Registered: October 2019
Junior Member
column width is allowed to spool till 4000 char only. what to do?
Re: How to spool the data of a clob or long column [message #677807 is a reply to message #677806] Sat, 12 October 2019 01:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not correct:
SQL> set linesize 32767
SQL> set longchunksize 32767
SQL> 
Previous Topic: Validating the code in PL/SQL
Next Topic: sql developer connection using jdbc:vjdbc:servlet connection url
Goto Forum:
  


Current Time: Thu Mar 28 14:53:16 CDT 2024