RE: Getting all rows of sqlplus output on one line
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <Rodd.Holman@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Oct 2006 14:02:13 -0400
If you look at it as a db problem (not Windows problem), you could try
something like this:
with T AS (
SELECT SYS_CONNECT_BY_PATH(site_id, ',') text, CONNECT_BY_ISLEAF
FROM ( select 1 verse, rownum piece, site_id from site_list_table
where definition_id in (nnnn, nnn2, nnn3) order by
site_id)
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY verse = PRIOR verse
AND piece - 1 = PRIOR piece
START WITH piece = 1
)
select text from T
/
Igor
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rodd Holman
Sent: Thursday, October 05, 2006 1:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Getting all rows of sqlplus output on one line
OK, I've been searching the docs (docs.oracle.com) and trying to figure
out how to make this work from sqlplusw.exe. I'll let it be known from
the first that I despise Windows and hate working in it. Here's the
case:
We have Business Objects as a reporting tool. One of our power users
wants to be able to enter in a list of site_id's into one of the
parameter fields. Current selection capability is *ALL* or individual
site_id. However, the tool lets you paste a ";" separated list into it.
Running the following
select site_id||';'
from site_list_table
where definition_id in (nnnn, nnn2, nnn3) order by site_id;
returns (as expected)
site_id||';'
------------
0000100;
0000102;
...
and so on.
From the UNIX/LINUX side I can select this list and paste it directly
into the browser field for site_id and it works.
From the windows side it only pastes the first value because sqlplusw
outputs with CRLF or something like that.
Does anyone have a way of returning this variable # of records in one
output string? I've done some playing with decode trying to flip it,
but it's not getting me where I want to be. The list of site_id's is
dynamic, so to specifically code the decode to do a crostab would need
me to re-write it each time the list changed. I don't want to get that
involved with this user.
I really hate Windows!!!
Thanks
Rodd
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
