Getting index of element in PL/SQL collection

Not sure, if this really helps, or if you think it is more elegant:

create type t_test as table of varchar2(1);/DECLARE--TYPE t_test IS TABLE OF VARCHAR2(1);  v_test t_test;  function get_index(q in t_test, c in varchar2) return number is    ind number;  begin    select min(rn) into ind from (      select column_value cv, rownum rn       from table(q)     )    where cv = c;    return ind;  end get_index;BEGIN  v_test := NEW t_test('A', 'B', 'A');  dbms_output.put_line( 'A: ' || get_index( v_test, 'A' ) );  dbms_output.put_line( 'B: ' || get_index( v_test, 'B' ) );  dbms_output.put_line( 'C: ' || get_index( v_test, 'C' ) );END;/show errorsdrop type t_test;

I don't think there is a built-in function that searches a collection. However, if you know you will need to search a collection a lot, you could build an index. Adding element to the collection will be a bit more expensive, but looking for an element will be an O(1) operation (instead of O(n) for a brute force search). For example, you could use something like this:

SQL> DECLARE  2     TYPE t_test IS TABLE OF VARCHAR2(1);  3     TYPE t_test_r IS TABLE OF NUMBER INDEX BY VARCHAR2(1);  4    5     v_test t_test;  6     v_test_r t_test_r;  7    8     FUNCTION get_index(p_test_r t_test_r,  9                        p_element VARCHAR2) RETURN NUMBER IS 10     BEGIN 11        RETURN p_test_r(p_element); 12     EXCEPTION 13        WHEN no_data_found THEN 14           RETURN NULL; 15     END get_index; 16   17     PROCEDURE add_element(p_test IN OUT t_test, 18                           p_test_r IN OUT t_test_r, 19                           p_element VARCHAR2) IS 20     BEGIN 21        p_test.extend; 22        p_test(p_test.count) := p_element; 23        p_test_r(p_element) := least(p_test.count, 24                                     nvl(get_index(p_test_r, p_element), 25                                         p_test.count)); 26     END add_element; 27  BEGIN 28     v_test := NEW t_test(); 29     add_element(v_test, v_test_r, 'A'); 30     add_element(v_test, v_test_r, 'B'); 31     add_element(v_test, v_test_r, 'A'); 32     dbms_output.put_line('A: ' || get_index(v_test_r, 'A')); 33     dbms_output.put_line('B: ' || get_index(v_test_r, 'B')); 34     dbms_output.put_line('C: ' || get_index(v_test_r, 'C')); 35  END; 36  /A: 1B: 2C: PL/SQL procedure successfully completed

You could also define a record that contains both arrays and all functions/procedures to interact with arrays would use this record type.

When in doubt, consult the documentation ;) (here)

DECLARE  TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;  aa_int  aa_type_int;  PROCEDURE print_first_and_last IS  BEGIN    DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);    DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);  END print_first_and_last;BEGIN  aa_int(1) := 3;  aa_int(2) := 6;  aa_int(3) := 9;  aa_int(4) := 12;  DBMS_OUTPUT.PUT_LINE('Before deletions:');  print_first_and_last;  aa_int.DELETE(1);  aa_int.DELETE(4);  DBMS_OUTPUT.PUT_LINE('After deletions:');  print_first_and_last;END;/

Result:

Before deletions:FIRST = 1LAST = 4After deletions:FIRST = 2LAST = 3