Limiting results and using a dynamic column name??

[chrisrlong]

Originally posted by Taoism
Heh, you are correct in the code not working... I got the code from this thread (which was code you had posted), but for whatever reason, I managed to lose the outer loop when I re-wrote it. I did figure that one out 'on my own' so to speak before I checked back here and found your reply

What's worse is that I specifically say in that thread that the code you ended up copying would not work with an ORDER BY

Originally posted by Taoism
I have no idea what these do though:

:EndRow
:StartRow

Binded variables of some sort I assume? But I see no reference to them getting bound (sorry, still new to Oracle and PL/SQL).

And again, I am unfamiliar with this syntax:

USING
  p_EndRow,
  p_StartRow;

These 2 pieces go together. In the statement, as you noted, those were bind variable. The USING is what populated them.

Originally posted by Taoism
I ended up writing it like so:

       OPEN p_fldr_cur FOR
    'SELECT rn,
            fldr_id,
            fldr_name
       FROM(   -- outer
         SELECT inner.*,
                ROWNUM AS rn
           FROM(   -- inner
             SELECT fldr_id,
                    fldr_name
               FROM inf_cat_fldrs
              ORDER BY '||p_order||'
           ) inner
      WHERE ROWNUM <= '||p_limit||'
       ) outer
    WHERE outer.rn >= '||p_offset||' ';

Am I losing something by not using the code blocks I ask about above?

Yes, you are adding more entries to the SGA unnecessarily. Each set of values for the limit and offset will result in a completely new statement while adding no benefit. The optimizer will act no differently whether the value are hard-coded or bound, so the bound version is preferable.

Originally posted by Taoism
Also, does doing this 'dynamic column' sorting, cause the proc to not be fully compiled before execution? I.E. would I be better off writing a proc per column (horror)...?

Absolutely not. There is virtually no difference in hard-coding a statement vs. dynamically generating a statement. As a matter of fact, there are so many advantages to dynamically generating SQL that it is now my default methodology. I almost never hard-code any SQL statements anymore.

- Chris