Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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...Received on Mon Dec 11 2000 - 20:54:39 CST
> 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.