Re: dynamic ORDER BY values

Try,

Declaring your_cursor *without* the ORDER BY clause.

Declare a record of your_cursor%ROWTYPE.

Use PL/SQL to build a sql string *with* the desired run time ORDER BY clause.

Use dynamic sql to execute the sql string INTO your record structure.

--
Michael O'Neill


mjoneill_at_email.com
<cpfrommer_at_my-deja.com> wrote in message news:911mcq$h6k$1_at_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.