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

From: DanHW <danhw_at_aol.com>
Date: 30 Dec 1999 19:02:57 GMT
Message-ID: <19991230140257.02912.00000057_at_ng-co1.aol.com>

>
>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;
>
>
>
>
>
>

If it is a limited range of options that you want to use in the order by, you can use a decode statement as a controller...

   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 decode(inorderby,1, f.name, 2,f.phone, f.department) ASC;
      RETURN rc;

   END; If inorderby=1, sorts by name, =2 sorts by phone, anything else, sorts by department. You can just as well use the column name, but as a literal string, not as a true column...

Hope this helps
Dan Hekimian-Williams Received on Thu Dec 30 1999 - 20:02:57 CET