Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

User_1871Jul 6 2022 — edited Jul 6 2022

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.

This post has been answered by Paulzip on Jul 6 2022
Jump to Answer
Comments
Post Details
Added on Jul 6 2022
1 comment
58 views