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.