Re: formatting output with SQL*Plus

In message <1102630865.868575_at_yasure>, DA Morgan <damorgan_at_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:
>

But surely the defined length doesn't matter - only the actual content. 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>