This tip will answer two

questions I get very frequently. Definitely a FAQ. “How do I retrieve

and use the VARCHAR2 index of an associative array?” and “How can I

sort a PL/SQL table?” These questions pertain to PL/SQL functionality;

not SQL functionality.


I’ll answer the sort

first and I’ll kind of cheat. When I ask what kind of table they’re

using the answer is usually either an INDEX BY table with an integer

index or a nested table. The values of the table are usually a

key->value pair like this:

TYPE r_places_i_lived IS RECORD ( 
  unique_key VARCHAR2(10), 
  data_value VARCHAR2(50) ); 

TYPE a_places_i_lived IS TABLE OF r_places_i_lived 

v_places_i_lived a_places_i_lived;

NOTE: If the unique_key field is not really unique, my solution will not work. This only works when the key is unique in the PL/SQL table.


suggestion is to change the structure of the table from BINARY_INTEGER

to VARCHAR2 and make the unique_key the index variable.

Read the rest of this post.