Home » SQL & PL/SQL » SQL & PL/SQL » Can I sort value in array ?
Can I sort value in array ? [message #186535] Tue, 08 August 2006 06:47 Go to next message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
Hi all,

Anybody know about how to sort value in array (just like table type)


Thanks,

Re: Can I sort value in array ? [message #186568 is a reply to message #186535] Tue, 08 August 2006 09:13 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
SQL> CREATE OR REPLACE TYPE typ_arr AS TABLE OF VARCHAR2(10);
  2  /

Type created.

SQL>
SQL> DECLARE
  2    v_arr       typ_arr := typ_arr('C', 'B', 'E', 'D');
  3    v_arr_sort  typ_arr;
  4  BEGIN
  5    select cast(multiset(
  6                  select * from table(v_arr)
  7                  order by 1) as typ_arr)
  8    into v_arr_sort from dual;
  9
 10    for i_idx in v_arr_sort.first..v_arr_sort.last
 11    loop
 12      dbms_output.put_line(v_arr_sort(i_idx));
 13    end loop;
 14  END;
 15  /
B
C
D
E

PL/SQL procedure successfully completed.
Re: Can I sort value in array ? [message #186718 is a reply to message #186535] Wed, 09 August 2006 04:02 Go to previous messageGo to next message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
I try to use this way in my package but it cannot.
So I think maybe it can use only sql table not plsql table , right ?
Re: Can I sort value in array ? [message #186726 is a reply to message #186535] Wed, 09 August 2006 04:25 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
It should work in a package with a nested table type as shown. What is the collection type you're using?
Re: Can I sort value in array ? [message #186733 is a reply to message #186535] Wed, 09 August 2006 04:50 Go to previous messageGo to next message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
Associative Array.
So maybe it support only Nested Table Type as you said.
Re: Can I sort value in array ? [message #186754 is a reply to message #186535] Wed, 09 August 2006 06:26 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Yes for this approach to work the collection type should be defined in the database.

This article demonstrates ways to sort in an associative array. I prefer the nested table approach though as it is compact, uses SQL only and can perform more sophisticated sorting if required.
Re: Can I sort value in array ? [message #186858 is a reply to message #186535] Wed, 09 August 2006 22:24 Go to previous message
nasdaq
Messages: 8
Registered: April 2006
Location: bangkok
Junior Member
Thank you so much, hobbes.
Your website is useful for me.
Previous Topic: Is there any limit for stored procedure's arguments?
Next Topic: How to manipulate and insert records from flat fil???
Goto Forum:
  


Current Time: Tue Feb 04 21:54:47 CST 2025