RE: Getting all rows of sqlplus output on one line

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 5 Oct 2006 14:00:23 -0400
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0F40EA21@ENYC11P32005.corpny.csfb.com>

Rodd,

Have you tried running sqlplus from a DOS prompt, instead of sqlplusw? You can copy any arbitrary region to the clipboard. It's easiest if you click the upper-left-hand corner of the DOS window, select Properties, and then check QuickEdit mode, in which clicking and dragging selects, and right-clicking copies.

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Rodd Holman Sent: Thursday, October 05, 2006 1:45 PM To: oracle-l_at_freelists.org
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
--

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

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 05 2006 - 13:00:23 CDT