This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,153 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

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

User_1871
User_1871 Member Posts: 248 Red Ribbon
edited Jul 6, 2022 2:28PM in SQL & PL/SQL

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.

Tagged:

Best Answer

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Jul 6, 2022 6:34PM Answer ✓

    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.

    User_1871

Answers

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Jul 6, 2022 6:34PM Answer ✓

    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.

    User_1871