-
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....
Any help is appreciated!
Cheers,
Keith.
-
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;
-
try
Code:
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 ;
- Chris
-
Thanks to everyone who has replied!
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:
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)...?
Cheers,
Keith.
-
USING
p_EndRow,
p_StartRow;
means
:EndRow will bind the value of p_EndRow and :StartRow will bind to p_StartRow
if you have say a third bind variable then you would have to add another variable after p_startRow in using clause
it's dynamic SQL that's why
go to tahiti.oracle.com and read about pl/sql guide about native dynamic sql, it's all explained there
-
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:
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.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|