sqloracle-databaseplsqloracle18cvarray

Use negative number to extract element at end of varray list (in a custom member function)


Oracle 18c:

I have a custom member function that works as expected. It lets me extract an element from a varray by index.

For example, extract the first ordinate in an SDO_GEOMETRY's sdo_ordinates varray attribute using GetOrdinates(1) = 10.

CREATE TYPE my_sdo_geom_type AS OBJECT( 
  shape SDO_GEOMETRY, 

  MEMBER FUNCTION GetOrdinates(
    self IN my_sdo_geom_type,
    idx  IN NUMBER
  ) RETURN NUMBER
) 
/ 
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 SIGN(idx) = 1 THEN
    return shape.sdo_ordinates(idx);
  ELSE
    return null;        --Temporarily returns null, since I don't know how to implement that functionality yet.
  END IF;
  END;
END;
/ 
create table lines (my_sdo_geom_col my_sdo_geom_type);
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
from   lines
FIRST_ORDINATE LAST_ORDINATE
10 null

db<>fiddle

Source: Extract varray element by index number using custom type's member function

That works as expected.


Now, I want to add functionality that let's me pass a negative number to the function. A negative number would let me extract an ordinate at the end of the list. Similar to how sdo_util.get_coordinate(shape, -1) works: https://gis.stackexchange.com/a/425057/62572.

For example, GetOrdinates(-1) = 60.

I can use the sign(idx) function to determine if a number is positive, zero, or negative. But I'm not sure how to take the next step: Use the negative number to go to the end of the varray list and work backwards to get the appropriate element.


Question:

How can I use negative number to extract an element at end of the varray list?


Solution

  • You can use:

    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;
      END IF;
      END;
    END;
    / 
    

    db<>fiddle here