Re: A suggestion needed for ORA-01467 sort key too long error please.....

regarding the error, please refer to

http://asktom.oracle.com/pls/ask/f?p=4950:8:824310::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:508222056084,%7Bsort%7D%20and%20%7Bkey%7D%20and%20%7Btoo%7D%20and%20%7Blong%7D

As you're using a DISTINCT, you are sorting the whole SELECT-clause

On 15 May 2002 10:10:43 -0700, kgrigg_at_diamonddata.com (Kelly) wrote:

>Hello all,
>
>I'm doing some PL/SQL for an Oracle generated web application.
>
>I have a loop where I generate a sql string...and throw it into
>a owa_util.cellsprint (v_sql) call....
>
>This worked, until I had to modify the sql just a little bit,
>to send one more piece of information to a function that the
>sql calls. The web page doesn't work anymore...so, I called it by
>hand from SQL*Plus...and got the ORA-01467 error.
>
>I've done some reading...seems to be something like that the
>sort key cannot be longer than like 40% or so of a oracle block.
>
>this appears to be assoc. with an ORDER BY or GROUP BY clause.
>
>I tried taking out my ORDER BY clause...but, got same error.
>
>Here is a sample of the v_sql string I am generating:
>
>SELECT DISTINCT a.name "Room", decode(generic_structure.check_date(to_date
>('MAY 15, 2002 05:00 AM','MON DD, YYYY HH:MI AM'),a.room_id),0,
>vtc_change_rooms_available.room_links(0,a.room_id,33,'MAY 15, 2002','05:00 AM'
>,102),vtc_change_rooms_available.room_links(generic_structure.check_date
>(to_date('MAY 15, 2002 05:00 AM','MON DD, YYYY HH:MI AM'),a.room_id))),
>decode(generic_structure.check_date(to_date('MAY 15, 2002 05:15 AM','MON DD,
>YYYY HH:MI AM'),a.room_id),0,vtc_change_rooms_available.room_links(0,a.room_id
>,33,'MAY 15, 2002','05:15 AM',102),
>vtc_change_rooms_available.room_links(generic_structure.check_date
>(to_date('MAY 15, 2002 05:15 AM','MON DD, YYYY HH:MI AM'),a.room_id))),
>decode(generic_structure.check_date(to_date('MAY 15, 2002 05:30 AM',
>'MON DD, YYYY HH:MI AM'),a.room_id),0,vtc_change_rooms_available.room_links
>(0,a.room_id,33,'MAY 15, 2002','05:30 AM',102),
>vtc_change_rooms_available.room_links(generic_structure.check_date
>(to_date('MAY 15, 2002 05:30 AM','MON DD, YYYY HH:MI AM'),a.room_id))),
>
>...etc....
>
>FROM room a, room_equipment b WHERE a.occup_limit >= 33 AND a.room_id =
>b.room_id (+) AND b.equipment_id in ( 9) ORDER BY a.name
>
>Like I said, I only added one more variable to send to the room_links function
>call...and it started this...
>
>And I don't understand how the sort key can be so long, I'm only
>sorting on a.room_id...which is a simple number....
>
>This thing is used to generate an HTML table with a red or green image
>depending if the room in question is open at that time..when clicked, a <FORM>
>with information is submitted..etc.
>
>Any suggestions out there? Would opening this sql up as a REF cursor,
>and creating the HTML table with a cursor loop take care of this
>problem?
>
>Thank you,
>
>Kelly
>
>
>--
>
>
>------------------
>
>"Oh, you hate your job? Why didn't you say so? There's a support
>group for that. It's called EVERYBODY, and they meet at the bar."
>
>------------------

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Wed May 15 2002 - 12:18:19 CDT