Re: Problem in SQL....??

A copy of this was sent to BHAVESH GOSAR <BHAVESH.GOSAR_at_Sun.COM> [Quoted] (if that email address didn't require changing) On Thu, 28 Oct 1999 14:49:12 -0700, you wrote:

>Hi,
>
>I have the foll. table
>
>Tab T1 ( x1 varchar2(8), x2 varchar2(2), x3 varcahr2(2),
> x4 number(38), x5 varchar2(2000)
> )
>
 

a single string selected from SQL cannot exceed 2000 bytes in 7.x and 4000 bytes in 8.x.

in plsql you can make strings upto 32k. these strings cannot be select'ed in SQL queries. those strings are limited to 2000/4000 characters.

when you remove the order by -- you get back some rows (that are less then 2000/4000 bytes) until you hit a row that exceeds this limit.

>When I do a...
>
>set linesize 2500
>
>select x1 || '~' || x2 || '~' || x3 || '~' || x4 || rtrim(x5) || '~'
> from t1
>order by x1, x2, x3, x4
>
>It gives the foll. error...
>=========
>ERROR:
>ORA-01489: result of string concatenation is too long
>=========
>
>When I remove the Order by Clause...
>It gets some rows and gives me the above error...
>
>I checked ORacle MAnuals...and it syas the MAximum length can be
>32 KB for a string..
>
>Any Ideas...workaround...??
>
>Thanks,
>Bhavesh Gosar

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation