RE: SQL question

Bill,

Did you try setting the linesize to something really large? Maybe you are getting line wrapping because your large columns are exceeding line length:

set lines 5000

The other possibility that Jared was hinting at was that you might have line control characters in your data that is causing a line throw.

Whenever I want data out of the database, I use the following set commands as a default:

Set lines 5000
Set pages 1000
Set trimspool on

Check the sqlplus documentation for others.

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman Sent: Monday, October 13, 2008 7:34 PM
To: oracle-l_at_freelists.org
Subject: SQL question

Greetings,

Running Oracle 10.2.0.3.0 on RHEL4, 64-bit.

Table:

foo VARCHAR2(300)
bar CHAR(1)
baz CHAR(1)

Data:

somelongishstring,A,B
shorterstring,A,C
shorter,A,D

Query:

select foo,bar,baz
from mytable
/

Output:

somelongishstring
,A,B

shorterstring
,A,C

shorter
,A,D

Desired:

somelongishstring,A,B
shorterstring,A,C
shorter,A,D

SQL> select substr(foo,0,instr(foo,' ',0,1)),bar,baz from mytable;

Does not work, because there's no space in the data, INSTR(FOO,' ') returns zero and so SUBSTR(foo,0,0) returns an empty string. > Is there any way to achieve the desired result in vanilla SQL*Plus short of the following (which looks like line noise), or resorting to PL/SQL or some other language (Perl, Java, ...)?

select foo||','||bar||','||baz
from mytable
/

Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208

zn{i

--
http://www.freelists.org/webpage/oracle-l