Re: dynamic ORDER BY values

I think you indeed need to user dbms_sql. but instead of setting the order by parameter in a bind variable, you have to specify it in the string that's given to the dbms_sql package.

Hope this helps
gert

In article <912otk$a3u$1_at_nnrp1.deja.com>,   cpfrommer_at_my-deja.com wrote:
> 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.
>

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