Home » SQL & PL/SQL » SQL & PL/SQL » order by procedure parameter
order by procedure parameter [message #19382] Wed, 13 March 2002 13:15 Go to next message
Julia
Messages: 30
Registered: December 1999
Member
I created procedure, which I want to sort output parameters in custom order.
Order1 can be 1,2 - column number,
Order2 - 'asc' or 'desc'.
create or replace procedure sort_order(order1 number, order2 varchar2)
as
begin
declare
cursor my_sort_cur IS
select DATA_OBJECT_ID, OBJECT_NAME
from all_objects
order by order1
-- order by order1 || ' ' order2;
my_rec my_sort_cur%rowtype;
begin
for my_rec in my_sort_cur
loop
DBMS_OUTPUT.PUT_LINE ('ID: '|| TO_CHAR(my_rec.DATA_OBJECT_ID));
DBMS_OUTPUT.PUT_LINE('NAME: '|| my_rec.OBJECT_NAME);
end loop;
end;
end sort_order;

Even in a simple situation with order by order1, it doesn't work properly. If I just do order by 1 I have one result, but when I call sort_order(1,'asc') I have a different one. Plus, when I do "order by order1 || ' '|| order2", it doesn't work correctly + doesn't react on order2 variable.
Can I use "execute immediate" inside cursor? If yes, the how?
Any other thoughts?
Re: order by procedure parameter [message #19383 is a reply to message #19382] Wed, 13 March 2002 13:48 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
create or replace procedure sort_order(order1 number, order2 varchar2)
as
type r is ref cursor;
my_sort_cur r;
l_object_id all_objects.DATA_OBJECT_ID%type;
l_object_name all_objects.OBJECT_NAME%type;
begin
open my_sort_cur for 'select DATA_OBJECT_ID, OBJECT_NAME
from all_objects
order by '||to_char(order1)||' '||order2;
loop
fetch my_sort_cur into l_object_id,l_object_name;
exit when my_sort_cur%notfound;

DBMS_OUTPUT.PUT_LINE ('ID: '|| TO_CHAR(l_OBJECT_ID));
DBMS_OUTPUT.PUT_LINE('NAME: '|| l_OBJECT_NAME);
end loop;

end sort_order;
Previous Topic: Exceptio "e_no_deptno" never gets executed????
Next Topic: code should be fairly simple but it is not working?
Goto Forum:
  


Current Time: Thu Mar 28 05:34:39 CDT 2024