Oracle access varray elements in SQL

Question

I'm playing around with array support in Oracle and hit a roadblock regarding array access within a SQL query. I'm using the following schema:

create type smallintarray as varray(10) of number(3,0);
create table tbl (
   id number(19,0) not null,
   the_array smallintarray,
   primary key (id)
);

What I would like to do is get the id and the first element i.e. at index 1 of the array. In PostgreSQL I could write select id, the_array[1] from tbl t but I don't see how I could do that with Oracle. I read that array access by index is only possible in PL/SQL, which would be fine if I could return a "decorated cursor" to achieve the same result through JDBC, but I don't know if that's possible.

DECLARE
 c1   SYS_REFCURSOR;
 varr smallintarray2;
BEGIN
  OPEN c1 FOR SELECT t.id, t.THE_ARRAY from tbl t;
  -- SELECT t.THE_ARRAY INTO varr FROM table_with_enum_arrays2 t;
  -- return a "decorated cursor" with varr(1) at select item position 1
  dbms_sql.return_result(c1);
END;

score 2 · Accepted Answer · 2021-03-09T17:35:40.223

2

You can do this in plain SQL; it's not pretty, but it does work. You would prefer that Oracle had syntax to hide this from the programmer (and perhaps it does, at least in the most recent versions; I am still stuck at 12.2).

select t.id, q.array_element
from   tbl t cross apply 
       ( select column_value as array_element,
                rownum       as ord
         from   table(the_array)
       ) q
where  ord = 1
;

EDIT If order of generating the elements through the table operator is a concern, you could do something like this (in Oracle 12.1 and higher; otherwise the function can't be part of the query itself, but it can be defined on its own):

with
  function select_element(arr smallintarray, i integer)
    return number
    as
    begin
      return arr(i);
    end;
select id, select_element(the_array, 1) as the_array_1
from   tbl
/

edited Mar 09 '21 at 17:35

answered Mar 09 '21 at 14:50

1

Hi and thanks for that. I was thinking about this as well, but since Oracle didn't explicitly mention this, I was concerned that the row number might not match the actual array index. Do you know for sure that this is the case? – Christian Beikov Mar 09 '21 at 14:53
1

I assume that this would work. But is it documented anywhere that the `table` function will return elements from the collection in order so that `ord = 1` in this case is always the first element? I wasn't seeing anything in the documentation that guarantees this though I'd expect it to. – Justin Cave Mar 09 '21 at 14:53
I had the same concern as Justin Cave – Christian Beikov Mar 09 '21 at 14:54
1

I don't know for sure, because - as you said - Oracle documentation is silent on it. There has been good, long discussion on this on the OTN forum; no one has seen a case where the array elements are **not** generated in array order. – Mar 09 '21 at 14:55
@ChristianBeikov - I see that you selected a different answer as the "correct answer". It suffers from the same possible defect though, does it not? Not sure how you decided which answer is better. In any case - if order preservation with the `table` operator is a concern, you can create a PL/SQL function that selects the elements; I edited my reply to show how. – Mar 09 '21 at 17:31
@JustinCave - I added a (presumably) more robust solution; it may or may not be slower than the pure SQL approach (as "pure" as the `table()` operator allows, anyway), but it should not depend on what is and what is not documented about `table()`. – Mar 09 '21 at 17:33
Oh, it's possible to define inline functions?! That would be awesome, but it complains on 19 XE that it is missing a keyword. – Christian Beikov Mar 09 '21 at 17:44
Nevermind, it works! This solution is way superior actually! – Christian Beikov Mar 09 '21 at 17:45
@ChristianBeikov - Defining functions in the `with` clause was new in Oracle 12.1. One important thing to keep in mind is that the whole statement, in that case, must be terminated with a slash, not a semicolon. (Easy to forget!) – Mar 09 '21 at 18:02

ekochergin · Answer 2 · 2021-03-09T15:11:41.463

1

First of all, please don't do that on production. Use tables instead of storing arrays within a table.

Answer to your question is to use column as a table source

SELECT t.id, ta.*
  from tbl t,
       table(t.THE_ARRAY) ta
 order by column_value
--  offset 1 row -- in case if sometime you'll need to skip a row
fetch first 1 row only;

UPD: as for ordering the array I can only say playing with 2asc/desc" parameters provided me with results I've expected - it has been ordered ascending or descending.

UPD2: found a cool link to description of performance issues might happen

edited Mar 09 '21 at 15:11

answered Mar 09 '21 at 14:53

ekochergin

4,109
2
12
19

I actually want the first element, but this boils down to the same question as in the comments below. Is it guaranteed that the table operator retains the order of the array? – Christian Beikov Mar 09 '21 at 14:56
@ChristianBeikov I've updated the answer. The main thing: adding the "order by" clause gave me results in expected order. But again - since it is not documented anywhere - think of using plain tables instead in order to get expected results for sure – ekochergin Mar 09 '21 at 15:05
Why would you not recommend this? I like the fact that it is stored in-line. Ordering by the value is not what I want. I want the element at index 1 i.e. the first element in the array. – Christian Beikov Mar 09 '21 at 15:07
@ChristianBeikov First, It makes code less supportable for another developers that were not involved in the dev process. I have this issue right now on a legacy project. It is really hard to get where the data came from as it is being stored in a collection within a column. Second, there might be performance issues (see the link to a brilliant answer on UPD2) – ekochergin Mar 09 '21 at 15:11
@ChristianBeikov another problem would be if you ever need select over childrens (the_array) based on some criteria other than their parent id. Let's say you need to find all the childs among all the parents having some particular value - this will be both harder to write SELECT statement and much slower – ekochergin Mar 09 '21 at 15:19
@ChristianBeikov as for ordering, I got your point - I can't be sure you'll get the items in the same order you've inserted it. Only the ordering (which is not what you need). But as mathguy mentioned - at least no one has not seen an array has been generated other way as the elements have been inserted. This is another point for you to use a table instead: you can define a sorting column (lfor example "insert_date_time") and define then the ordering as you need – ekochergin Mar 09 '21 at 15:36
I really appreciate that you try to help me, but I want arrays because they are stored in-line and thus require fewer disk fetches. It seems though, that its impossible to define an index on such columns, which is a pity. – Christian Beikov Mar 09 '21 at 15:39
@ChristianBeikov it is because database can't compare such arrays. In other words, it can't say whether (0,1,2) is greater that (1,0,2). Which is another point (not from me, but from the database - (: ) to make you using tables instead. – ekochergin Mar 09 '21 at 15:47
A database like PostgreSQL supports indexing arrays by building an inverted index, which I think is pretty cool. Too bad Oracle doesn't support this properly. Thanks for all the info! – Christian Beikov Mar 09 '21 at 16:02
@ChristianBeikov Kein Problem. Immer gerne – ekochergin Mar 09 '21 at 16:05

Oracle access varray elements in SQL

2 Answers2