 | |
formatting output with SQL*Plus
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: formatting output with SQL*Plus -
12-10-2004
, 04:34 AM
|
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 |
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
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>
#22
| |||
| |||
|
|
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. |
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>
#23
| |||
| |||
|
|
In message <31tc22F3f14lnU1 (AT) individual (DOT) net>, Thomas Kellerer NNGNVRDSJEBN (AT) spammotel (DOT) 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. |
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.
--
New anti-spam address cmylod at despammed dot com
#24
| |||
| |||
|
|
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 |
#25
| |||
| |||
|
|
DA Morgan wrote on 09.12.2004 23:22: you could try CASTing as a CLOB or alternatively CASTing as smaller VARCHARs. For example: That gives me an "ORA-00932: inconsistent datatypes" 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 indifferent 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 |
technique. Probably not what you're looking for.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
#26
| |||
| |||
|
|
DA Morgan wrote on 09.12.2004 23:22: you could try CASTing as a CLOB or alternatively CASTing as smaller VARCHARs. For example: That gives me an "ORA-00932: inconsistent datatypes" 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 indifferent 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 |
The fingers are far faster than the mind.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
#27
| |||
| |||
|
|
DA Morgan wrote on 09.12.2004 23:22: you could try CASTing as a CLOB or alternatively CASTing as smaller VARCHARs. For example: That gives me an "ORA-00932: inconsistent datatypes" 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 indifferent 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 |
#28
| |||
| |||
|
|
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 |
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.)
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.