Re: SELECT stmt to file with tab delimiters

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 08 Dec 2007 10:00:17 -0800
Message-ID: <1197136815.884080@bubbleator.drizzle.com>

joel garry wrote:

> On Dec 5, 3:56 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> Occidental wrote:
>>> Suppose you have a SELECT stmt of form:
>>> SELECT Field1, Field2, Field3
>>> FROM Table1
>>> WHERE etc
>>> What would it take to generate a file from the output this stmt, where
>>> the fields are tab-delimited? I'm working with an Oracle guy who
>>> claims it is impossible. In mysql it is easy
>>> SELECT Field1, Field2, Field3
>>> INTO OUTFILE 'filename'
>>> FIELDS TERMINATED BY '\t'
>>> LINES TERMINATED BY '\n'
>>> FROM Table1
>>> WHERE etc
>> SELECT col1 || chr(09) || col2
>> FROM ....;
>>
>> PS: They are columns ... not fields.

> 
> But what if the data has embedded tabs?
> 
> jg
> --
> @home.com is bogus.
> Bad DBA!  Bad, Bad, Bad DBA!  http://www.channelregister.co.uk/2007/12/04/admin_steals_consumer_records/

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t (

   2 col1 VARCHAR2(20),
   3 col2 VARCHAR2(20));

Table created.

SQL> INSERT INTO t VALUES ('A' || CHR(09) || 'B', 'X' || CHR(09) || 'Y');

1 row created.

SQL> SELECT * FROM t;

COL1                 COL2
-------------------- --------------------
A       B                  X    Y

SQL> SELECT col1 || CHR(09) || col2

   2 FROM t;

COL1||CHR(09)||COL2

---

A B X Y

SQL>

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Dec 08 2007 - 12:00:17 CST