RE: SQL question
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <wjwagman@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Oct 2008 08:07:45 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William Wagman
Sent: Monday, October 13, 2008 7:34 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx
(530) 754-6208
zn{i
--
//www.freelists.org/webpage/oracle-l
Other related posts:
