Are Oracle PL/SQL arrays indexed from 0 or from 1?

Jacques Chester picture Jacques Chester · Jul 27, 2012 · Viewed 13.7k times · Source

I have in front of me a piece of code like this:

FOR row IN 1..l_RowSet(1).count 
LOOP
   l_a_variable := l_RowSet(1)(row);
END LOOP;

l_RowSet is an ApEx type -- apex_plugin_util.t_column_value_list -- defined thus:

type t_column_value_list  is table of wwv_flow_global.vc_arr2 index by pls_integer;

where wwv_flow_global.vc_arr2 is defined as

type vc_arr2 is table of varchar2(32767) index by binary_integer;

The vc_arr2 is passed back to my code from the apex_plugin_util.get_data function. The vc_arr2 is indexed by column number, not by row.

As best I can make out this means that the data is effectively stored in a 2D array, indexed by column and then by row.

When using the LOOP statement, would this be indexed from zero or from one? Because it seems to me that I ought to be able to make that LOOP redundant, ie:

l_a_variable := l_RowSet(1)(1);

But I'd need to know in advance whether to give 0 or 1 as the initial row.

I can't find a clear answer in the Oracle docs (unsurprisingly, "index" is a fairly widely-used term) and a look through SO doesn't show anybody else with the same question either.

Answer

Justin Cave picture Justin Cave · Jul 27, 2012

An associative array isn't necessarily dense. There may be an element at index 0, there may be an element at index -1, there may be an element at index 1. Or you might have elements at indexes 17, 42, and 127. The code you posted implies that the associative array is dense and that the indexes start at 1.

In the specific case of apex_plugin_util.get_data the collection should be dense and should start at 1. If the loop is actually not doing anything other than what you posted, you could replace it by fetching the last element of l_RowSet(1), i.e.

l_a_variable := l_RowSet(1)(l_RowSet(1).count);