How to use a parameter with Order By?
706510Jun 17 2009 — edited Jul 9 2009I am trying to pass in a parameter to a procedure (like 'ColumnName ASC, ColumnName DESC') to use with ORDER BY, but I can't get it to order on the passed in parameter? I have read a few posts but non of my attempts have worked.
Procedure getEquipmentList(io_cursor OUT ref_cursor_Equipment), orderString IN VARCHAR2, RecordStart IN NUMBER, RecordEnd IN NUMBER) IS
Equipment_Cursor refCursor_Equipment;
Use_Order NUMBER := 1;
BEGIN
OPEN Equipment_Cursor
FOR SELECT *
FROM TABLE(
PACKAGE_EQUIPMENT.getRecordSetOfEquipment(
CURSOR(
SELECT * FROM (SELECT idEquipment, strEquipmentName, deletedFlag, Row_Number() OVER (ORDER BY DECODE(Use_Order, 1, orderString)) AS r FROM tblEquipment) WHERE r >= RecordStart AND r <= RecordEnd)));
io_cursor := Equipment_Cursor;
END;
If I replace orderString in the DECODE with say strEquipmentName it orders correctly. If I try 'strEquipmentName' then it does not work. Any ideas would be helpful.