Re: dynamic ORDER BY values

Suppose I do it that way (dynamically build the SQL as a VARCHAR2 and pass it to the dbms_sql package for execution). I have a couple questions to help me understand the implications of using dbms_sql (I'm a Java programmer... kinda new to the oracle stuff).

1. Am I right in thinking that the query will never be compiled into memory and every time the function executes Oracle will have to build, load, parse and excecute the query?
2. Am I also right in thinking that if the query was executed without the help of the dbms_sql then it would be compiled into memory only once and then re-executed every time without the extra overhead?

Thanks a lot for your help.

Chad

In article <912vvc$ges$1_at_nnrp1.deja.com>,   denevge_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 11 2000 - 13:00:36 CST