dynamic ORDER BY values

From: <cpfrommer_at_my-deja.com>
Date: Mon, 11 Dec 2000 04:49:29 GMT
Message-ID: <911mco$h6j$1@nnrp1.deja.com>

Hi all. I'm wondering if anyone knows a way to dynamically ORDER BY different columns in a SQL query within a PL/SQL function.

I have a function that I am calling from Java, basically it just returns a CURSOR from a query. I want to be able to specify which column to sort by on the Java side.

Is there any way to dynamically ORDER BY different columns? Here's the very basics of what I'm trying to do:

FUNCTION function_name(p_sortOrder IN NUMBER)

RETURN REF CURSOR
IS

results REF CURSOR;
BEGIN
OPEN results FOR

         SELECT
             u.last_name,
             u.first_name,
             u.company_name
         FROM
             user u
         ORDER BY
             p_sortOrder;

     return results;

END function_name;

My apologies if the syntax isn't perfect, but you get the idea.

I looked into possibly using the DBMS_SQL package to parse and bind it as a variable, but I'm not sure if that will work and it also seems like a bit of overkill (performance hit as well?).

Any help would be greatly appreciated...

Chad

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 10 2000 - 22:49:29 CST