Re: Dynamic Sorts in Pl/Sql

> Patrick Casey wrote:
>
> We are trying to create a procedure that will be able to sort fields
> dynamically. We want the same fields for each query, but at some times
> we want it sorted by one field, and sometimes by another.
>
> We created the following procedure:
>
> PROCEDURE testSort
> (in_sort_field IN VARCHAR2,
> in_sort_order IN VARCHAR2 DEFAULT 'asc',
> out_cursor OUT pjcCursor)
> IS
> BEGIN
> if in_sort_order = 'asc' then
> OPEN out_cursor FOR
> SELECT cat_itm_id, cat_itm_nm FROM ITP_CATALOG_ITEM
> ORDER BY in_sort_field ASC;
> else
> open out_cursor FOR
> select cat_itm_id, cat_itm_nm FROM itp_catalog_item
> order by in_sort_field DESC;
> end if;
> END;
>
> It allows us to pass in a field name to sort by. Now, it works fine on
> ASC, but it never works on DESC. We think this is because Oracle
> really sees this query as
>
> OPEN out_cursor FOR
> SELECT cat_itm_id FROM ITP_CATALOG_ITEM
> ORDER BY 'in_sort_field' ASC; <-- notice quotes around field name

   In Oracle8i you can use native dynamic SQL

• begin script --
• SQL*Plus script

VARIABLE CUR REFCURSOR DECLARE
   TYPE pjcCursor IS REF CURSOR;
   l_cur pjcCursor;
PROCEDURE testSort(in_sort_field IN VARCHAR2

                 , in_sort_order IN VARCHAR2 DEFAULT 'asc'
                 , out_cursor OUT pjcCursor)

      'SELECT ename, sal FROM emp '
   || 'ORDER BY ' || in_sort_field || ' ' || in_sort_order; END testSort;

--
BEGIN
   testsort('sal DESC, ename','ASC', l_cur);
--   testsort('ename','desc', l_cur);
   :CUR := l_cur; -- To see via SQL*Plus 
END;
/

PRINT CUR
-- end script --


Valery Yourinsky
-- 
Oracle8 Certified DBA