Getting all rows of sqlplus output on one line

From: Rodd Holman <Rodd.Holman_at_gmail.com>
Date: Thu, 05 Oct 2006 12:44:59 -0500
Message-ID: <4525449B.805@gmail.com>

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
--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 05 2006 - 12:44:59 CDT