Re: Static ORDER BY Clause

Just a thought ... haven't tried it but think it might work try constructing your query by concatinating strings together.

DECLARE
  v_query_string varchar2(100);
  v_cursorID INTEGER;
BEGIN
v_cursorID := DBMS_CURSOR.Open_Cursor;
v_query_String :=

   'SELECT * FROM Accounts WHERE state = :binvar1 '||    'ORDER BY '||vin_col1||', '||vincol2; DBMS_SQL.Parse(v_cursorID, v_query_String, DBMS_SQL.v7); DBMS_SQL.Bind_Variable(v_cursorID, ':binvar1', 'TX');

yada yada yada.

END; The key is look at v_query_String. This seems to me it would work but I don't have the time to test it. I seem to remember I've used this somewhere else but not with the order by.

Good Luck.

David Archer

In article <7pgcsm$b73$1_at_oak.prod.itd.earthlink.net>,   "Jeff" <janderson_at_iname.com> wrote:
> For performance reasons we are trying to rewrite all of our packages
to use
> bind variables so that all of our queries are static. We have been
able to
> do this everywhere except in the ORDER BY clause. According to Oracle
> Support we cannot use a bind variable in the ORDER BY clause. The
> application we are developing allows the user to select up to 2
columns out
> of 150 to sort by so we need to be able to change the columns used in
the
> ORDER BY clause. Does anyone have any ideas on how we can allow our
users to
> request different sort columns and still create a static query?
>
> Thanks,
> Jeff
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 20 1999 - 09:17:18 CDT