13

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
Jacques Chester
  • 628
  • 1
  • 4
  • 13
  • PL/SQL doesn't have a concept named "array". It has two (or three, depending on what you choose to count in) data types similar to "array" (as "array" is defined for example in C or FORTRAN): the ASSOCIATIVE ARRAY and the VARRAY. (Or you may choose to count NESTED TABLE here as well). The types you are looking at are ASSOCIATIVE ARRAYS. However, the data type that is REALLY like "arrays" is VARRAY in PL/SQL. VARRAY indexing is from 1, rather than from 0. Associative arrays don't even have the concept of being indexed "from" something; they are just .. well... associative arrays. –  Dec 07 '18 at 15:59

1 Answers1

10

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);
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks. I've updated the question to better explain what get_data does. It *is* a 2D collection, by column then row. Given that PL/SQL arrays can be sparse I'll need to dig some more to see what it's returning to me "live". – Jacques Chester Jul 27 '12 at 04:08
  • 1
    @JacquesChester - `apex_plugin_util.get_data` returns a collection of type `t_column_value_list` which is a multi-dimensional array. Updated the question and removed the 1D/ 2D discussion from my answer. – Justin Cave Jul 27 '12 at 04:28