Question on Error: ORA-01467 : Sort key too long ...

Hello, out there !

We're using Oracle 7 Server Release 7.2.2.3.0 on a Sun running UNIX System V Release 4.0 ...

We've defined a table ....

Name                            Null?    Typ
------------------------------- -------- ----
OID                             NOT NULL NUMBER(38)
PROD_ID                         NOT NULL VARCHAR2(80)
STORE_ID                        NOT NULL NUMBER(38)
CREATION_TIME                   NOT NULL DATE
STATUS                          NOT NULL NUMBER(38)
DELETED                         NOT NULL NUMBER(38)
LAST_MOD_TIME                   NOT NULL DATE
NAME                            NOT NULL VARCHAR2(128)
DEPT                            NOT NULL VARCHAR2(30)

... and a view on this table ...

Name                            Null?    Typ
------------------------------- -------- ----
OID                                      NUMBER
PROD_ID                                  VARCHAR2(80)
STORE_ID                                 NUMBER
CREATION_TIME                            DATE
STATUS                                   NUMBER
DELETED                                  NUMBER
LAST_MOD_TIME                            DATE
NAME                                     VARCHAR2(128)
DEPT                                     VARCHAR2(30)

Why defining a view with almost all the attributes defined in the table ? I don't know - this did someone else before my time ;)

Okay this is the environment. And now my problem ...

Running SQL-Plus and entering following Select ... select * from "viewname" where STORE_ID = 111 and DELETED = 0 and STATUS = 1
and (upper(NAME) like '%PR%' or upper(SDESC2) like '%PR%' or upper(LONGDESC)
like '%PR%';

I receive the error "ORA-01467 : Sort key too long".

The attribute creating this error must be "NAME". Leaving this attribute out
of this select-command it works. The select also works if I replace '%PR%'
with '%WORD%' ...

The help from oracle about "ORA-01467" is wonderful ;(

Anyone out who may help me ???

Please answer to michael.maretzke_at_primus.baynet.de

Thanx

Michael. Received on Thu Aug 06 1998 - 04:35:49 CDT