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

Thread: Sorting a cursor in stored procedure(Urgent pls)

Hybrid View

  1. #1
    Join Date
    Nov 2001
    Posts
    5

    Unhappy

    Dear experts,

    I've created a cursor in the stored procedure in the following way,
    ...
    Open vCursor for
    SELECT * FROM tableA
    ....

    The question is, I want to know, if I want to provide the user the flexibility of sorting this cursor by
    different column, how can I sort this cursor?

    It seems that I can't sort like the following
    SELECT * FROM vCursor<----NOT a table
    ORDER BY (xyz)<----column name that user desired.

    do anyone get any idea? Pls advise!!!!
    Carlos

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    There is no possibility to change order of rows in already opened cursor.
    If I had to solve that I would add another parameter to the procedure and pass the name of column in it. Then I'd use dynamic SQL to force the cursor to be ordered by the column passesd through the parameter:
    Code:
      1  create or replace procedure get_emps(p_order VARCHAR2) is
      2    type t_emp_cursor is ref cursor;
      3    emp_cursor t_emp_cursor;
      4    r_emp emp%rowtype;
      5  begin
      6    open emp_cursor for 'select * from emp order by '||p_order;
      7    loop
      8      fetch emp_cursor INTO r_emp;
      9      exit when emp_cursor%notfound;
     10      dbms_output.put_line(r_emp.empno||' '||r_emp.ename);
     11    end loop;
     12* end;
    
    Procedure created.
    
    scott@oracle> set serveroutput on size 65536
    scott@oracle> exec get_emps('EMPNO')
    7369 SMITH
    7499 ALLEN
    7521 WARD
    7566 JONES
    7654 MARTIN
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7844 TURNER
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7934 MILLER
    
    PL/SQL procedure successfully completed.
    
    scott@oracle> exec get_emps('ENAME');
    7876 ADAMS
    7499 ALLEN
    7698 BLAKE
    7782 CLARK
    7902 FORD
    7900 JAMES
    7566 JONES
    7839 KING
    7654 MARTIN
    7934 MILLER
    7788 SCOTT
    7369 SMITH
    7844 TURNER
    7521 WARD
    
    PL/SQL procedure successfully completed.
    
    scott@oracle>
    HTH,
    Ales

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Another possibility :

    SCOTT@po7:SQL>select * FROM dept;

    DEPTNO DNAME LOC DATE_CREATED
    --------- -------------- ------------- -------------------
    10 ACCOUNTING NEW YORK 03-NOV-01
    20 RESEARCH DALLAS 04-NOV-01
    30 SALES CHICAGO 05-NOV-01
    40 OPERATIONS BOSTON 06-NOV-01
    98 RIO RIO 07-NOV-01
    97 SP SAO PAULO 08-NOV-01

    6 rows selected.

    SCOTT@po7:SQL>variable x varchar2(40);

    SCOTT@po7:SQL>exec :x := 'LOC';

    PL/SQL procedure successfully completed.

    SCOTT@po7:SQL>get prog
    1 select *
    2 from dept d
    3 order by decode(:x,
    4 'DEPTNO', to_char(d.deptno, 'FM0009'),
    5 'DNAME', d.dname,
    6 'LOC', d.loc,
    7 'DATE_CREATED', to_char(d.date_created, 'YYYYMMDD')
    8* )

    SCOTT@po7:SQL>@prog

    DEPTNO DNAME LOC DATE_CREA
    --------- -------------- ------------- ---------
    40 OPERATIONS BOSTON 06-NOV-01
    30 SALES CHICAGO 05-NOV-01
    20 RESEARCH DALLAS 04-NOV-01
    10 ACCOUNTING NEW YORK 03-NOV-01
    98 RIO RIO 07-NOV-01
    97 SP SAO PAULO 08-NOV-01

    6 rows selected.


    SCOTT@po7:SQL>exec :x := 'DATE_CREATED';

    PL/SQL procedure successfully completed.


    SCOTT@po7:SQL>@prog

    DEPTNO DNAME LOC DATE_CREA
    --------- -------------- ------------- ---------
    10 ACCOUNTING NEW YORK 03-NOV-01
    20 RESEARCH DALLAS 04-NOV-01
    30 SALES CHICAGO 05-NOV-01
    40 OPERATIONS BOSTON 06-NOV-01
    98 RIO RIO 07-NOV-01
    97 SP SAO PAULO 08-NOV-01

    6 rows selected.

    SCOTT@po7:SQL>

    []s
    Chiappa

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