Re: SELECT stmt to file with tab delimiters

On Dec 8, 10:00 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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_rec...
>
> 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
>

Now truncate the table, and show the sqlloader command file for loading that data with embedded tabs in the file into the table.

Since you don't seem to think there is an unhandled problem with delimiters in the data...

jg

--
@home.com is bogus.
Cartoon of dog peeing wins major journalism award:
http://www.signonsandiego.com/uniontrib/20071208/news_1m8breen.html
http://www.signonsandiego.com/news/features/breen/archiveindex.html