Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use a parameter with Order By?

706510Jun 17 2009 — edited Jul 9 2009
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 6 2009
Added on Jun 17 2009
4 comments
898 views