SELECT stmt to file with tab delimiters

#1

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

#2

Occidental wrote:

Quote:

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

On Dec 5, 6:18 pm, Occidental <Occiden... (AT) comcast (DOT) net> wrote:

Quote:

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

Another solution involves the COLSEP (Column Separator) in SQLPLUS.

Look up the SET command for the COLSEP option.

Ed

#4

On Dec 5, 3:56 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

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/200...sumer_records/

#5

joel garry wrote:

Quote:

On Dec 5, 3:56 pm, DA Morgan <damor... (AT) psoug (DOT) 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/200...sumer_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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On Dec 8, 10:00 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

joel garry wrote:
On Dec 5, 3:56 pm, DA Morgan <damor... (AT) psoug (DOT) 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/200...s_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/uniont..._1m8breen.html
http://www.signonsandiego.com/news/f...hiveindex.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SELECT stmt to file with tab delimiters

comp.databases.oracle.misc comp.databases.oracle.misc

SELECT stmt to file with tab delimiters - 12-05-2007 , 05:18 PM

Re: SELECT stmt to file with tab delimiters - 12-05-2007 , 05:56 PM

Re: SELECT stmt to file with tab delimiters - 12-05-2007 , 10:07 PM

Re: SELECT stmt to file with tab delimiters - 12-07-2007 , 07:05 PM

Re: SELECT stmt to file with tab delimiters - 12-08-2007 , 12:00 PM

Re: SELECT stmt to file with tab delimiters - 12-10-2007 , 12:14 PM