Re: parametrizing ORDER BY in PL/SQL (oracle 8.0.5)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Dec 1999 06:48:57 -0500
Message-ID: <vbe16sknuc4jg84meomitoqj1u0qi5vrg5_at_4ax.com>

A copy of this was sent to Thomas Malmberg <thomas.malmberg_at_terranova.fi> (if that email address didn't require changing) On Wed, 22 Dec 1999 09:23:01 GMT, you wrote:

>Hi,
>
>I'm trying to ORDER BY my query using a in-parameter. As we all know it
>is possible to ORDER BY using a column index. Can this index be passed
>as a parameter.
>
>Example function that compiles but still doesn't order by the parameter
>- instead it orders by column 1:
>
> FUNCTION getfoobar (
> inorderby IN NUMBER
> )
> RETURN foobar_curtype
> IS
> rc foobar_curtype;
> BEGIN
> OPEN rc FOR
> SELECT f.name, f.address, f.phone, f.department
> FROM footable f
> ORDER BY inorderby ASC;
> RETURN rc;
> END;
In Oracle8i, release 8.1, using the new enhanced dynamic sql -- this will be easy. You would:

begin

   open rc for 'select f.name, f.address, f.phone, f.department

                  FROM footable f
                 ORDER BY || inorderby || ' ASC';

end;

In the meanwhile, for 8.0.5, 2 options to consider:

• use dbms_sql instead of a ref cursor. You cannot in any way shape or form dynamically open a ref cursor in 8.0.5. dbms_sql is the only way to do dynamic sql in 8.0.5
• use decode if possible. for example:

   BEGIN

      OPEN rc FOR
         SELECT f.name, f.address, f.phone, f.department
           FROM footable f
          ORDER BY decode( inorderby, 1, f.name, 
                                      2, f.address, 
                                      3, f.phone, 
                                      4, f.departement ) ASC;
      RETURN rc;

   END;     beware if you plan in sorting by strings, dates and numbers. You'll have to coerce all three to be strings (eg: for a date column, you'll return to_char( date_column, 'yyyymmddhh24miss' ) from the decode. for a number column -- if the number is always positive you'll return to_char( number_column, '00000000000009.9999999999999' ) or whatever format makes sense for that column. negative numbers get a little trickier)....

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Wed Dec 22 1999 - 12:48:57 CET