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

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."

------------------