CODEWITHSUNDEEP
oracle-database
Christian Beikov
Christian Beikov

Reputation: 16400

Oracle access varray elements in SQL

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;

Upvotes: 0

Views: 2003

Answers (3)

gouessej
gouessej

Reputation: 4076

You can use the undocumented function named SYS_OP_CEG to do that in Oracle SQL:

SELECT SYS_OP_CEG
       ( sys.odcivarchar2list('Dave','Dee','Dozy','Beaky','Mick','Titch')
       , 3 ) AS third_item
FROM   dual;

SYS_OP_CEG takes a varray and a one-based index as parameters. The example above uses a varray of varchar2 and prints Dozy which is the third element.

It works with numbers too:

SELECT SYS_OP_CEG
       ( sys.odcinumberlist(5, 10, 84, -12, 9999, 6666)
       , 3 ) AS third_item
FROM   dual;

Yes it's less straightforward than PostgreSQL and surprisingly, it produces an Oracle error ORA-31009 when you pass a negative integer.

P.S: I've just tested with Oracle 19 but it's expected to work with Oracle 9.2 and above.

Upvotes: 0

user5683823
user5683823

Reputation:

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
/

Upvotes: 2

ekochergin
ekochergin

Reputation: 4129

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

Upvotes: 1

Related Questions