order of element in a string array (collection) [message #313448] |
Sat, 12 April 2008 08:21 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
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
[Updated on: Sat, 12 April 2008 08:55] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|