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:D) 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!

Quote:

---

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

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

Quote:

---

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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