Home » SQL & PL/SQL » SQL & PL/SQL » order of element in a string array (collection) (9i, 10g, 11g)
order of element in a string array (collection) [message #313448] Sat, 12 April 2008 08:21 Go to next message
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

Re: order of element in a string array (collection) [message #313462 is a reply to message #313448] Sat, 12 April 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) I don't think you can rely on this as Oracle said in
Database PL/SQL User's Guide and Reference
Chapter 5 Using PL/SQL Collections and Records
Section Choosing Which PL/SQL Collection Types to Use
Quote:
Sets and bags in other languages become nested tables in PL/SQL.

And sets and bags do not have an order.

2) Why not using array of objects/records containing the 3 values? Then there is no need to join the 3 tables.

Regards
Michel

[Updated on: Sat, 12 April 2008 10:52]

Report message to a moderator

Re: order of element in a string array (collection) [message #313482 is a reply to message #313462] Sat, 12 April 2008 21:16 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Rather than using ROWNUM to index the elements as they come out, you could write a pipelined table function to access the nested table and return the elements along with their index.

Ross Leishman
Re: order of element in a string array (collection) [message #313483 is a reply to message #313448] Sat, 12 April 2008 22:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes Michel, exactly what I was thinking, which is why I am asking the question. The manual is not clear on this point when it comes to unpacking collections in this manner.

Yes Ross, I too figure a table function is the safest way. It is disappointing however to have to write what feels like extra code that should not be needed.

Thanks you guys. I will proceed with the table function. If I find out anything interesting, I will repost it.

Kevin
Re: order of element in a string array (collection) [message #313557 is a reply to message #313448] Sun, 13 April 2008 13:35 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
why not just extend the array and store the index value as well? You can add that condition tot he join.
Re: order of element in a string array (collection) [message #313560 is a reply to message #313448] Sun, 13 April 2008 13:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I could, but java talking with oracle is a new field for the people I work with. Passing collections that are not simple items becomes more work for them. If I can get by with simple string arrays them this is better for them.

Thanks, Kevin
Re: order of element in a string array (collection) [message #313615 is a reply to message #313560] Sun, 13 April 2008 23:42 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just be aware that the work you are asking the SQL engine to do to reconstruct a collection of records from arrays of scalars is significant.

I hope the solution is viable.

Ross Leishman
Re: order of element in a string array (collection) [message #315019 is a reply to message #313448] Fri, 18 April 2008 12:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks for the reminder Ross. To any newbies following this thread, as always, one must test a solution before going live with it.

One of my old bosses used to say:

Make it work
Make it work right
Make it work fast

Kevin

Hmm.. anyone else got interesting phrases?
Re: order of element in a string array (collection) [message #315021 is a reply to message #315019] Fri, 18 April 2008 12:27 Go to previous message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Theory is when you know why it doesn't work.
Practice is when it works, but you don't know why.
We combine theory and practice;
It doesn't work, and we don't know why.


Regards
Michel
Previous Topic: Is "a" a keyword in oracle
Next Topic: regarding pragma_exception_init
Goto Forum:
  


Current Time: Thu Nov 07 17:47:30 CST 2024