OraFAQ Forum: SQL & PL/SQL => order of element in a string array (collection)

I have a need to get java to pass string arrays to plsql. I know how to do this, but I have a question about the order in which elements from the "nested table" on the oracle side will be returned when casting it to a rowset in sql using TABLE(CAST operators.

Here is a simple example:

create or replace type c_string_array_255 is table of varchar2(255)
/

create or replace function kev_f1 return c_string_array_255 is
   sa c_string_array_255 := c_string_array_255();
begin
   sa.extend;
   sa(sa.last) := 'abc';
   sa.extend;
   sa(sa.last) := 'def';
   sa.extend;
   sa(sa.last) := 'ghi';
   return (sa);
end;
/

select rownum,a.column_value
from table(cast(kev_f1 as c_string_array_255)) a
/

And here is output of running this code:

SQL> select rownum,a.column_value
  2  from table(cast(kev_f1 as c_string_array_255)) a
  3  /

    ROWNUM COLUMN_VALUE
---------- -------------------------------------------
         1 abc
         2 def
         3 ghi

3 rows selected.

Notice the following please:

1) elements are put into the array in the following order:

abc
def
ghi

Hence abc is element 1, def is element 2, and ghi is element 3.

2) I am using rownum to expose the index element off the array but to do this I am relying on the fact that the first row from the TABLE(CAST conversion will be element #1, the second row returned will be element #2, and so on.

My questions are these:

1) can I rely on a TABLE(CAST against a string array like this, to always return rows in the order they were packed into the array?

2) is there a way to access the index of the array directly(maybe some collection method or combination of collection methods (if so what is the syntax?)) so that I don't really care what order the elements are unpacked?

You might be asking, why I want to do this. Well as it turns out I will be passing multiple string arrays from java to plsql and I want to "join" all these arrays on their element index in order to construct a logical row that can then be used to query another table. Here is the actual code so far:

OPEN Policy_Activity_p FOR
   SELECT Policy_Number,
          Producer_Code,
          NAME,
          Read_Date,
          Process_Date,
          Idars_Document_Id
   FROM   Policy_Activity_History
        , ( select rownum rowno,a.column_value from table(cast(policy_number_p as c_string_array_30)) policy_number
        , ( select rownum rowno,a.column_value from table(cast(Producer_Code_p as c_number_array)) Producer_Code
        , ( select rownum rowno,a.column_value from table(cast(Idars_Document_Id_p as c_string_array_30)) Idars_Document
--
-- notice use of rowno to join input arrays that are pieces of a logical row
-- back into their original logical row
-- so that we can use them to query another table
--
   WHERE policy_number.rowno = Producer_Code.rowno
   and policy_number.rowno = Idars_Document.rowno
   and policy_number.column_value = Policy_Activity_History.Policy_Number
   and Producer_Code.column_value = Policy_Activity_History.Producer_Code
   and Idars_Document.column_value = Policy_Activity_History.Idars_Document_Id
      ;

Kevin