Hello,

I'm hitting the old problem that I need to spool data into a text file, but
SQL*Plus insists on padding the fields to the length of the column definition.

The problem is that I cannot concatenate the columns as I get an "ORA-01489:
result of string concatenation is too long" when doing so.

What I want is a file that has the following format

value1|value2|value3

but what I get is:

value1 |value2 |value3

The columns are all VARCHAR2(2000)

What I'm basically doing is:

set term off
set echo off
set feedback off
set heading off
set define off
set timing off
set linesize 32767
set pagesize 0
set colsep '|'
set newpage none
set trimspool on

spool output.txt

SELECT column1,column2, column3
FROM my_table;

spool off

Is there any way to remove the trailing spaces in the column values?

I'm using Oracle 8.1.7.4.0 on HP/UX

Any input is greatly appreciated!!

Cheers
Thomas

On Wed, 08 Dec 2004 18:08:49 +0100, Thomas Kellerer
<NNGNVRDSJEBN (AT) spammotel (DOT) com> wrote:

There is a way which I've used to make the dbms_metadata.get_ddl
package spool out a decent usable (as DDL SQL) piece of code, this
might help you. Look for the put_line PL./SQL procedure which has a
"compress" 2nd argument to squeeze out the irritating space-to-EOL
chars.

I got this from http://adp-gmbh.ch/ora/misc/oru_10028.html

It's all clean readable code. You can modify this package, then select
your stuff into a varchar2, then put_line(that varchar2, boolean)
where boolean is TRUE for that "compression", which you can readily
fix to your taste. Does require some PL/SQLing but that's Just Another
Language really :-)

HTH


--
New anti-spam address cmylod at despammed dot com

On 08.12.2004 19:15 Colum Mylod wrote:
Hi Colum,

thanks for your reply. This sounds like a good idea, but unfortunately we are
not allowed to create new procedures in the database.

Cheers
Thomas

"Thomas Kellerer" <NNGNVRDSJEBN (AT) spammotel (DOT) com> wrote

cheap and ugly select trim(column1),trim(column2) ....


--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

Niall Litchfield wrote on 08.12.2004 19:26:
I tried trim() already, but it does not work
SQL*Plus pads the resulting _value_ to the length of the column. If I do
e.g. the following:


SELECT trim(column1)||'<',trim(column2)
FROM my_table;

I get the following (. to denote a space)

value1<......................,value2.............. .........

So the value itself if trimmed (as can be verified by the < sign right
after it) but the SQL*Plus puts spaces in the output to get this "table
like" formatting.

Cheers
Thomas

"Thomas Kellerer" <NNGNVRDSJEBN (AT) spammotel (DOT) com> wrote

SELECT column1||'|'||column2 etc...

Get rid of SET COLSEP

Alan wrote on 08.12.2004 20:22:
As I have already pointed out in my original post: I get the following
error when doing so:
"ORA-01489: result of string concatenation is too long"

Thanks
Thomas

You don't really need to create a procedure in the database ...
You can do it within an anonymous block (in a sqlplus session)
For example
set serveroutput on
declare
cursor c_val is
select memb1,memb2 from family;
v_col1 varchar2(2000);
v_col2 varchar2(2000);
begin
open c_val;
fetch c_val into v_col1,v_col2;
while c_val%found loop
dbms_output.put_line(v_col1 ||'|'||v_col2);
fetch c_val into v_col1,v_col2;
end loop;
end;
/
Note that you could also use utl_file for larger amounts of data.

"Thomas Kellerer" <NNGNVRDSJEBN (AT) spammotel (DOT) com> wrote

set colsep
set trimspool on

"Thomas Kellerer" <NNGNVRDSJEBN (AT) spammotel (DOT) com> wrote

Sorry, missed that. This probably won't help you, but when I run into this,
I have used the following solutions:

1. I take the results as they are, paste it into Word or Excel, and just
find/replace <space><space> with <space> until it's done. I suppose you
could use vi to do the same.

2. I'll use a 3rd party tool, such as Cognos Impromptu or Lotus Approach.

3. I get one of the COBOL programmers to process it for me.

Maybe someone knows the "right" way.