Re: ORA-01467: sort key too long

I have run into the same problem on a database with a 2K block size. Although in my query the sort key was the varchar2(2000) column. I found that with a 2K block size the maximum length that a varchar2 column could be to perform the sort was 1976 characters. Once I set my varchar2 columns to 1976 the sort worked fine.

Tansel Ozkan wrote in message <34CD18BF.6C95_at_openix.com>...
>Hello everyone,
>
>I am having a strange problem. A query that I am running on our
>production database is giving me "ORA-01467: sort key too long" error
>message. One of the selected fields is datatype varchar2(2000). The
>query returns 20 records with the varchar2(2000) field occupying full
>2000 characters. To create the same environment on the development
>database, I inserted new records such that for a given where condition
>the query returns about 20 records with that field occupying 2000
>characters. However, when we run the same query on the development
>database, we are not getting this error message. Any ideas??
>
>Thanks..
>
>Tansel
>
>The query is as follows:
>
>SELECT
> "PROFILE_NOTES"."PROFILE_NO",
> "PROFILE_NOTES"."NOTES_NO",
> "PROFILE_NOTES"."NOTES", <------ varchar2(2000)
> "PROFILE_NOTES"."AGENT_NAME",
> "PROFILE_NOTES"."CREATE_ID",
> "PROFILE_NOTES"."CREATE_DATE",
> "PROFILE_NOTES"."MAINT_USER_ID",
> "PROFILE_NOTES"."MAINT_DATE",
> "PROFILE_NOTES"."SOURCE_NO",
> "PROFILE_NOTES"."PRODUCT_NO",
> "PRODUCT"."PRODUCT_NAME",
> "SOURCE"."SOURCE_NAME"
> FROM "PROFILE_NOTES",
> "PRODUCT",
> "SOURCE"
> WHERE ( ( "PROFILE_NOTES"."PROFILE_NO" = 137483 ) ) and
> ( profile_notes.source_no = source.source_no (+)) and
> ( profile_notes.product_no = product.product_no (+))
>ORDER BY "PROFILE_NOTES"."CREATE_DATE" ASC
Received on Sun Feb 01 1998 - 00:00:00 CST