Forum Stats

  • 3,875,133 Users
  • 2,266,809 Discussions
  • 7,912,089 Comments

Discussions

Support extracting collection elements by index in SQL

User_1871
User_1871 Member Posts: 247 Red Ribbon

It would be helpful if Oracle could support extracting collection elements by index in SQL:

Example:

SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;

Error:
ORA-03001: unimplemented feature

------------------------------------------------------------------------------------------

@MT0 described it well in a Stack Overflow post: (https://stackoverflow.com/a/72571112/5576771)

"Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in an SQL query?

Because the syntax of extracting collection elements by index is not supported in SQL. It is not just SDO objects but any collection:

SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;

Outputs:

ORA-03001: unimplemented feature

and:

SELECT l.list(1)
FROM   (SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c') AS list FROM DUAL) l;

Outputs:

ORA-00904: "L"."LIST": invalid identifier

(Which, I think means that it is trying to parse it as a function but the error message is less helpful/obvious than the previous one.)

There are methods of getting the value but it is more complicated as you need to dereference the entire collection using a table collection expression and then filter to get the desired row:

SELECT (
         SELECT COLUMN_VALUE
         FROM   TABLE(s.shape.sdo_ordinates)
         FETCH FIRST ROW ONLY
       ) as startpoint_x
FROM  (
  select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape from dual
) s

db<>fiddle here"

------------------------------------------------------------------------------------------

Could Oracle make it easier to extract collection elements by index in SQL?

Tagged:
User_1871
1 votes

Active · Last Updated

Comments

  • User_1871
    User_1871 Member Posts: 247 Red Ribbon
    edited Jun 19, 2022 11:35AM

    For example, PostgreSQL supports this:

    select (array['a','b','c'])[1]
    
    Result:
    array
    a
    

    db<>fiddle

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond
    edited Jun 19, 2022 1:47PM

    Could Oracle make it easier to extract collection elements by index in SQL?

    What is so difficult in:

    SQL> select column_value from sys.OdciVarchar2List('a','b','c') where rownum = 1;
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    a
    
    SQL> select * from sdo_geometry('linestring(1 2, 3 4, 5 6)').sdo_ordinates where rownum = 1;
    
    COLUMN_VALUE
    ------------
               1
    
    SQL> select * from sdo_geometry('linestring(1 2, 3 4, 5 6)').sdo_ordinates offset 4 rows fetch next 1 row only;
    
    COLUMN_VALUE
    ------------
               5
    
    SQL>
    

    SY.

  • User_1871
    User_1871 Member Posts: 247 Red Ribbon
    edited Jul 1, 2022 6:28PM

    Thanks.

    Those techniques you mentioned work when we're only dealing with a single row.

    Maybe a better example is a dataset with multiple rows:

    with data as (
    select sdo_geometry('linestring(1 2, 3 4, 5 6)')       as shape from dual union all
    select sdo_geometry('linestring(7 8, 9 10, 11 12)')    as shape from dual union all
    select sdo_geometry('linestring(13 14, 15 16, 17 18)') as shape from dual)
    
    select * from data
    
    Result:
    SHAPE                                                                                                                                     -------------------------------------------------------------------------------------------------
    SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4, 5, 6))
    SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(7, 8, 9, 10, 11, 12))
    SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(13, 14, 15, 16, 17, 18))
    

    With that multi-row dataset, there doesn't seem to be a succinct way to extract collection elements by index in SQL. At least, not as succinct as:

    select 
       (shape).sdo_ordinates(1)
    from 
      data;
    

    To me, the above syntax would be ideal.

  • User_1871
    User_1871 Member Posts: 247 Red Ribbon

    It's too bad the only way to do it is with JSON:

    with data as (
    select sdo_geometry('linestring(1 2, 3 4, 5 6)')    as shape from dual union all
    select sdo_geometry('linestring(7 8, 9 10, 11 12)')  as shape from dual union all
    select sdo_geometry('linestring(13 14, 15 16, 17 18)') as shape from dual)
    
    --get the X coordinate of the second vertex
    select 
      json_value((shape).Get_GeoJson(),'$.coordinates[1][0]' returning number) as x 
    from 
      data
    
             X
    ----------
             3
             9
            15
    

    It'd be great if we had that same functionality in core Oracle/SQL.

    • I'd be reluctant to use that JSON technique in my queries. It seems like queries would get unmanageable in a hurry. And it'd be confusing to use two different numberings systems in the same query: zero-based numbering for JSON, and one-based numbering for SQL and PL/SQL.