Home » SQL & PL/SQL » SQL & PL/SQL » Export SELECT output to text file
Export SELECT output to text file [message #195212] Wed, 27 September 2006 07:22 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi all ,i have a following query

select TABLE1.task_id || '|' || TABLE1.task_type_code || '|' || TABLE1.sched_date || '|' || TABLE1.compl_ind || '|' || TABLE1.prio_level || '|' || TABLE1.comp_id || '|' || TABLE2.first_name || '|' || TABLE2.last_name || '|' || TABLE1.ext_text
from TABLE1, TABLE2
where TABLE1.cont_id = TABLE2.cont_id
and TABLE1.rec_del_ind = 'N'
and TABLE2.rec_del_ind = 'N';

Here TABLE1.ext_text is a LONG datatype.how do i export the output to a text file.
Please help
Re: Export SELECT output to text file [message #195306 is a reply to message #195212] Wed, 27 September 2006 19:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ideally, you should stop using LONGs and start using CLOBs, then you wouldn't be in this mess.

Try the following in SQL*Plus:
SET LONG 65536 LINESIZE 10000 TRIMSPOOL ON COLSEP "|" PAGESIZE 0

select TABLE1.task_id
, TABLE1.task_type_code
, TABLE1.sched_date
, TABLE1.compl_ind
, TABLE1.prio_level
, TABLE1.comp_id
, TABLE2.first_name
, TABLE2.last_name
, TABLE1.ext_text
from TABLE1, TABLE2
where TABLE1.cont_id = TABLE2.cont_id
and TABLE1.rec_del_ind = 'N'
and TABLE2.rec_del_ind = 'N';

You will need to play around with the LINESIZE parameter, finding a balance between the longest LONG, and the maximum allowable value for your OS.

If you don't have any joy with this, search the forum for "LONG TO CLOB". You will find a couple of different PL/SQL functions that will convert LONGs to CLOBs so that you can concatenate to the rest of the string.

Ross Leishman
Previous Topic: Connect By Clause in sql
Next Topic: problem in oracle 10g release 2
Goto Forum:
  


Current Time: Sat Dec 03 01:22:00 CST 2016

Total time taken to generate the page: 0.04509 seconds