-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|