DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Limiting results and using a dynamic column name??

  1. #1
    Join Date
    Mar 2002
    Posts
    22

    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.

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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;

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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;

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Mar 2002
    Posts
    22
    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:
    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.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width