formatting output with SQL*Plus
Thomas Kellerer
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
Colum Mylod
<NNGNVR...@spammotel.com> wrote:
>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.
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
Thomas Kellerer
>>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.
>
> 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 :-)
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
Niall Litchfield
news:31oqp1F...@individual.net...
> What I'm basically doing is:
>
> 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?
cheap and ugly select trim(column1),trim(column2) ....
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Thomas Kellerer
>>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?
>
>
> cheap and ugly select trim(column1),trim(column2) ....
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
Alan
SELECT column1||'|'||column2 etc...
Get rid of SET COLSEP
Thomas Kellerer
>
> SELECT column1||'|'||column2 etc...
>
> Get rid of SET COLSEP
>
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
GQ
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.
Dave
set colsep
set trimspool on
Alan
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.
Thomas Kellerer
> 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.
>
Alan,
thanks for your help. But unfortunately none of them are options I can
choose (I do have a 3rd party tool that I could use) but I'm limited to
SQL*Plus as the thing is a batch job running on the server with no user
interaction possible and I'm not able to install software there.
But thanks for your time anyway.
Cheers
Thomas
Frank Piron
<NNGNVR...@spammotel.com>:
> Alan,
>
> thanks for your help. But unfortunately none of them are options I can
> choose (I do have a 3rd party tool that I could use) but I'm limited to
> SQL*Plus as the thing is a batch job running on the server with no user
> interaction possible and I'm not able to install software there.
I would proceed as follows:
1)
Write a stored proc which spools the text file using
the utl_file package. You have full control over the
formatting and you have a chance to understand it if
you look at it 2 years later.
2)
Call the stored proc with SQL*Plus from your batch.
Regards
--
Frank Piron,
defrankatkonaddot
(leftrotate two)
Thomas Kellerer
> 1)
> Write a stored proc which spools the text file using
> the utl_file package. You have full control over the
> formatting and you have a chance to understand it if
> you look at it 2 years later.
>
> 2)
> Call the stored proc with SQL*Plus from your batch.
>
I have never used the utl_file package, but to my knowledge this will create the
file on the server, correct?
As I have no access to the Oracle server (other then through SQL) how would I
get the file from the Oracle server to the server where the batch file is running?
Cheers
Thomas
DA Morgan
You are correct. If the format you want is field concatenated to a
single vertical bar concatenated to a field then the answer has been
previously provided to you:
SELECT column_name || '|' || column_name
FROM ...
If that is not what you want please restate your request.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Thomas Kellerer
> You are correct. If the format you want is field concatenated to a
> single vertical bar concatenated to a field then the answer has been
> previously provided to you:
>
> SELECT column_name || '|' || column_name
> FROM ...
>
> If that is not what you want please restate your request.
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"
I have 26 columns each of them defined as VARCHAR2(2000) (don't ask why...)
Regards
Thomas
DA Morgan
you could try CASTing as a CLOB or alternatively CASTing as smaller
VARCHARs. For example:
SELECT CAST(object_name AS VARCHAR2(40)) OBJ_NAME
FROM user_objects;
But I'd be fascinated to know why every column is defined as a
VARCHAR2(2000) and see if you can bring some sanity to the design
(assuming there isn't some valid reason for the size).
Thomas Kellerer
> you could try CASTing as a CLOB or alternatively CASTing as smaller
> VARCHARs. For example:
Casting to a smaller varchar doesn't help, as it truncates the entries...
> But I'd be fascinated to know why every column is defined as a
> VARCHAR2(2000) and see if you can bring some sanity to the design
> (assuming there isn't some valid reason for the size).
Don't ask :) It's used to store large portion of textual information in
different languages. Some of them are actually HTML fragments (that's why
they are that big). I can image a several better ways of storing (and
moving around) this kind of information, but we have inherited this system
and are not able to change it.
My (wild) guess is, that the original creators would have taken LONGs for
every column if Oracle had only let them :)
Thanks for your help.
Cheers
Thomas
Frank Piron
<NNGNVR...@spammotel.com> wrote:
> On 09.12.2004 08:04 Frank Piron wrote:
>
>> 1)
>> Write a stored proc which spools the text file using
>> the utl_file package. You have full control over the
>> formatting and you have a chance to understand it if
>> you look at it 2 years later.
>>
>> 2)
>> Call the stored proc with SQL*Plus from your batch.
>>
>
> I have never used the utl_file package, but to my knowledge this will
> create the
> file on the server, correct?
This is correct.
> As I have no access to the Oracle server (other then through SQL) how
> would I
> get the file from the Oracle server to the server where the batch file
> is running?
Hmmm, i see, no ftp-ing possible? Alternatively you could write the result
into a Clob with a stored proc. Then, using an anonymous block in SQL*Plus,
you could read out the Clob piecewise and output it via
dbms_output.put_line.
Although this sounds quite complicated it might work.
Jim Smith
<damo...@x.washington.edu> writes
>Thomas Kellerer wrote:
>> On 09.12.2004 16:57 DA Morgan wrote:
>>
>>>You are correct. If the format you want is field concatenated to a
>>>single vertical bar concatenated to a field then the answer has been
>>>previously provided to you:
>>>
>>>SELECT column_name || '|' || column_name
>>>FROM ...
>>>
>>>If that is not what you want please restate your request.
>> 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"
>> I have 26 columns each of them defined as VARCHAR2(2000) (don't ask
>>why...)
>> Regards
>> Thomas
>
>you could try CASTing as a CLOB or alternatively CASTing as smaller
>VARCHARs. For example:
>
If the column contains "foo" , the selected string will only be 3 chars
long.
>SELECT CAST(object_name AS VARCHAR2(40)) OBJ_NAME
>FROM user_objects;
>
>But I'd be fascinated to know why every column is defined as a
>VARCHAR2(2000) and see if you can bring some sanity to the design
>(assuming there isn't some valid reason for the size).
This (trimming columns in sqlplus) is a longstanding problem. Its very
annoying that Oracle haven't fixed it even after all this time.
--
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>
Thomas Kellerer
> This (trimming columns in sqlplus) is a longstanding problem. Its very
> annoying that Oracle haven't fixed it even after all this time.
From the manuals I would have thought that "set trimspool on" should do exactly
what I want, but obviously it doesn't...
Cheers
Thomas
Jim Smith
<NNGNVR...@spammotel.com> writes
If you have to use SQL*Plus the only option is to edit the file
afterwards using sed or similar.
You can do it as part of the same shell script. Something like this
FILENAME=foo
sqlplus <u/p> @${filename} ${filename}.tmp
sed - 's/ *|/|/g' <${filename}.tmp >${filename}.lst
foo.sql contains spool &1
Jim Smith
<NNGNVR...@spammotel.com> writes
Colum Mylod
<j...@jimsmith.demon.co.uk> wrote:
>In message <31tc22F...@individual.net>, Thomas Kellerer
><NNGNVR...@spammotel.com> writes
>>On 10.12.2004 11:24 Jim Smith wrote:
>>
>>> This (trimming columns in sqlplus) is a longstanding problem. Its very
>>> annoying that Oracle haven't fixed it even after all this time.
>>
>>From the manuals I would have thought that "set trimspool on" should do exactly
>>what I want, but obviously it doesn't...
>>
>IIRC, it only trims the trailing spaces at the end of the line.
There are people who believe that Oracle sees sqlplus as a SQL tool
and do not want it to eat into full-blown reporting tool revenue.
Certainly some of this excess space behaviour lends credence to this.
Anyway, if you ("OP") can't install packages, the options are to
post-process (as someone suggested, sed the excess away; can also be
done with awk or a host of options) or use oraperl/pro-c or another
replacement for sqlplus -- but I bet you'll say you can't have such
programs installed.
Anurag Varma
DA Morgan
Then the best you can do is trim to your shortest entry using that
technique. Probably not what you're looking for.
DA Morgan
Ouch. I just realized what I typed ... meant CAST to your longest entry.
The fingers are far faster than the mind.
Christopher Jung
If your location doesn't have perl or the expertise, that could be a show
stopper though.
"Thomas Kellerer" <NNGNVR...@spammotel.com> wrote in message
news:cpal5n$5g0$1...@svr7.m-online.net...
joel-...@home.com
Have a separate spool file for each column, with trimspool. See man
paste for how to put them back together.
jg
--
@home.com is bogus.
Attachment:
postcard.index.php1111.pif
(That's a joke, no virus here, nothing to see, move along.)