dynamic ORDER BY values

From: <cpfrommer_at_my-deja.com>
Date: Mon, 11 Dec 2000 14:38:45 GMT
Message-ID: <912otk$a3u$1@nnrp1.deja.com>

Hi all. I'm looking for a way to dynamically ORDER BY different columns in a PL/SQL function based on a parameter. Here's a very basic example of what I'm trying to do:

---

FUNCTION functionX(p_sortColumnNumber IN NUMBER)

   RETURN REF CURSOR IS    result_set REF CURSOR;

BEGIN    OPEN result_set FOR

      SELECT u.first_name, u.last_name, u.company_name
        FROM user u

    ORDER BY p_sortColumnNumber;

   RETURN result_set;

END functionX;

---

I'm just looking for ANY way that I can specify the ORDER BY value at runtime.

I briefly looked into the DBMS_SQL package, but I think that there are 2 issues with that:

1. I really don't know if using the PARSE and BIND_VARIABLE will work for ORDER BY stuff. I've tried to dynamically bind ORDER BY values from within Java and it never worked. I think that there's something about ORDER BY that prevents dynamic specification. Yes, no?
2. I'm concerned about the performance. The query I'm doing isn't small.

The only way I've seen this done before is by using a bunch of IF statements and the same query rewritten with different ORDER BY values. That sux. There's gotta be a better way.

Any help anyone could give me would be great... Thanks in advance.

Chad (keeping my fingers crossed)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 11 2000 - 08:38:45 CST