OraFAQ Forum: Client Tools » How to spool the data of a clob or long column

Home » SQL & PL/SQL » Client Tools » How to spool the data of a clob or long column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to spool the data of a clob or long column [message #434613]

Wed, 09 December 2009 23:19

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

Report message to a moderator

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

BlackSwan
Messages: 26640
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

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26640
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.

Report message to a moderator

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

Michel Cadot
Messages: 66715
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

Report message to a moderator

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

Glearn@2019
Messages: 1
Registered: October 2019

Junior Member

column width is allowed to spool till 4000 char only. what to do?

Report message to a moderator

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

Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is not correct:

SQL> set linesize 32767
SQL> set longchunksize 32767
SQL>

Report message to a moderator

Previous Topic:	Validating the code in PL/SQL
Next Topic:	connect sql developer without the tns file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 07:03:25 CST 2019