If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Limiting results and using a dynamic column name??

I did a search on this forum and found an excellent answer on how to limit results from an oracle query, but is it possible to build the results using a dynamic column in the ORDER BY section?

Code:

CREATE OR REPLACE PACKAGE misPkg AS
TYPE return_cur IS REF CURSOR;
PROCEDURE getFldrsLim2(p_offset IN NUMBER,
p_limit IN NUMBER,
p_order IN VARCHAR,
p_fldr_cur IN OUT return_cur);
END misPkg;
/
CREATE OR REPLACE PACKAGE BODY misPkg AS
PROCEDURE getFldrsLim2(p_offset IN NUMBER,
p_limit IN NUMBER,
p_order IN VARCHAR,
p_fldr_cur IN OUT return_cur) IS
BEGIN
OPEN p_fldr_cur FOR
SELECT *
FROM (SELECT ROWNUM AS rn,
t1.fldr_id,
t1.fldr_name
FROM inf_cat_fldrs t1
-- p_order isn't working
-- if I use a 'real' column hardcoded it works
ORDER BY p_order)
WHERE rn BETWEEN p_offset
AND p_limit;
END getFldrsLim2;
END misPkg;

Is there a way to pass a column name in and have the ORDER BY use that as a valid column name?

For example, I display a webpage with muliple columns, I can allow the user to click different column headings to return results sorted by a different column....

BEGIN
OPEN p_fldr_cur FOR
'SELECT *
FROM (SELECT ROWNUM AS rn,
t1.fldr_id,
t1.fldr_name
FROM inf_cat_fldrs t1
ORDER BY ' || p_order ||')
WHERE rn BETWEEN p_offset
AND p_limit';
END getFldrsLim2;
END misPkg;

Originally posted by chrisrlong I'm not sure where you found that code, but it is wrong. What you want to do is search for 'Windowing' and you will find many posts (many from me) that will show you how to do it correctly.

- Chris

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

I have no idea what these do though:

Code:

: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).

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:

Code:

: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:

Code:

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.

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.