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....
You will have to user native dynamic SQL.
Something like this ( example taken from oracle docs )
Examples of Dynamic SQL for Records, Objects, and Collections
As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
emp_rec emp%ROWTYPE;
sql_stmt VARCHAR2(200);
my_job VARCHAR2(15) := 'CLERK';
BEGIN
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
-- process record
END LOOP;
CLOSE emp_cv;
END;
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;
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.
Here is an example:
Code:
OPEN
p_fldr_cur
FOR '
SELECT
FLDR_ID ,
FLDR_NAME
FROM
( -- OUTER
SELECT
INNER.*,
ROWNUM
AS QUERY_ROWNUM
FROM
( -- INNER
SELECT
FLDR_ID ,
FLDR_NAME
FROM
INF_CAT_FLDRS
ORDER BY
'||p_OrderBy||'
) INNER
WHERE
ROWNUM <= :EndRow
) OUTER
WHERE
OUTER.QUERY_ROWNUM >= :StartRow ';
USING
p_EndRow ,
p_StartRow ;
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).
And again, I am unfamiliar with this syntax:
Code:
USING
p_EndRow,
p_StartRow;
I don't know what it is causing to happen in the proc.
I ended up writing it like so:
Code:
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?
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)...?
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.
Originally posted by Taoism I ended up writing it like so:
Code:
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.
Reply With Quote
) that will show you how to do it correctly.
Bookmarks