Function to get a collection element by index: Handling zero index as argument

User_1871

I have a custom member function that gets a varray collection element by index (as an experiment [link]).

CREATE TYPE BODY my_sdo_geom_type AS
  MEMBER FUNCTION GetOrdinates(
    self IN my_sdo_geom_type,
    idx  IN NUMBER
  ) RETURN NUMBER
  IS
  BEGIN 
  IF idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
    return shape.sdo_ordinates(idx);
  ELSIF -idx BETWEEN 1 AND shape.sdo_ordinates.COUNT THEN
    RETURN shape.sdo_ordinates(shape.sdo_ordinates.COUNT + 1 + idx);
  ELSE
    RETURN NULL; --Handles the case where the argument is zero,
  END IF;
  END;
END;
/ 
insert into lines (my_sdo_geom_col) 
  values (my_sdo_geom_type(sdo_geometry('linestring(10 20, 30 40, 50 60)')));

select (my_sdo_geom_col).GetOrdinates( 1) as first_ordinate,
       (my_sdo_geom_col).GetOrdinates(-1) as last_ordinate,
       (my_sdo_geom_col).GetOrdinates( 0) as zero_ordinate
from   lines

FIRST_ORDINATE	LAST_ORDINATE  ZERO_ORDINATE
--------------  -------------  -------------
            10             60           null

Full script: db<>fiddle

Source: https://stackoverflow.com/a/72884698/5576771

The member function accepts:

• Positive numbers. Example: 1 returns the first element.
• Negative numbers. Example: -1 returns the last element.
• 0 returns null.

That approach to handling a zero argument seems intuitive to me.

With that said, the null-approach is inconsistent with other functionality I've seen in Oracle functions.

For example, sdo_util.get_coordinate(shape, 0) will return the last vertex, not null:

with data (shape) as (
select sdo_geometry('linestring(10 20, 30 40, 50 60)') from dual)

select
  json_object(sdo_util.get_coordinate(shape, 0)) as vertex_zero
from
  data

--Returns the last vertex.

VERTEX_ZERO
-------------------------------------------------------------------------------------------------------------
{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":50,"Y":60,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}
--                                                 ^^     ^^
--                                                 The coordinates of the last vertex.

db<>fiddle

Question:

Is one approach more correct than the other? Should an argument of zero return null or the last element? Or something else, such as an error?

Thanks.

Paulzip

Personally, I would either raise an exception (ORA-06532: Subscript outside of limit) or return null for index = 0. Returning the last varray element makes zero sense to me, here. Mapping negatives as an offset from the end makes sense in iteration scenarios.

So I concur with your solution.

Paulzip

Personally, I would either raise an exception (ORA-06532: Subscript outside of limit) or return null for index = 0. Returning the last varray element makes zero sense to me, here. Mapping negatives as an offset from the end makes sense in iteration scenarios.

So I concur with your solution.