Oracle-L: Execute oexec(): ORA-01467: sort key too long

From: Kris Austin-Murray <kaustin_at_advance.net>
Date: Fri, 02 Aug 2002 10:15:46 -0800
Message-ID: <F001.004AA390.20020802101546@fatcity.com>

this error is being generated in an admin report tool. the query is the following:

SQL> SELECT value.name, value.value, entry.submit_dt 2 FROM value, entry WHERE value.entry_id = entry.entry_id AND 3 entry.form_cd = 'glamour_injeanious_mps';

here are the global settings for sqlplus:

SQL> show maxdata
maxdata 6000
SQL> show arraysize
arraysize 1

this began occuring when we change one field from 2000 characters, to 4000:

SQL> desc value

 Name                                      Null?    Type
 ----------------------------------------- ----------------------------
 ENTRY_ID                                  NOT NULL NUMBER(10)
 NAME                                      NOT NULL VARCHAR2(100)
 VALUE                                              VARCHAR2(4000)

the total # of records in value:

SQL> select count(*) from value;

COUNT(*)

16408559

i've played with setting the arraysize, maxdata without luck. this query does return results from within sqlplus, but not for the reporting tool, which sits on sun solaris 2.5 box with 4 GIG of memory.

we're running Oracle8i Enterprise Edition Release 8.1.6.0.0 on sun solaris 2.6

i've searched metalink and found a doc (Note:1012366.6) that explains the ORA-1467 error. still i am without a solution.

any help out there?

thank you!
kris

+-----+------+-----+------+-----+-----+-----+------+------+------+-----+ kris austin-murray ... senior database manager, ocp ... advance internet

www.advance.net ... kaustin_at_advance.net ... 201-459-2805 +-----+------+-----+------+-----+-----+-----+------+------+------+-----+ "Darkness cannot drive out darkness; only light can do that. Hate cannot drive out hate; only love can do that." - Martin Luther King, Jr.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kris Austin-Murray
INET: kaustin_at_advance.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Aug 02 2002 - 13:15:46 CDT