sqloracle-databasemember-functionsoracle18coracle-spatial

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


Oracle 18c:

I've created a user-defined type and a member function that work ask expected.

The member function returns an mdsys.sdo_ordinate_array such as MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60).

create type my_sdo_geom_type as object ( 
  shape sdo_geometry, 
  member function GetOrdinates(self in my_sdo_geom_type) 
  return mdsys.sdo_ordinate_array deterministic) 
/ 
create or replace type body my_sdo_geom_type as 
  member function GetOrdinates(self in my_sdo_geom_type) 
  return mdsys.sdo_ordinate_array is 
    begin 
       return shape.sdo_ordinates; 
    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()
from
  lines

Result:
MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60)

db<>fiddle


That member function works, but what I actually want to do is return a specific ordinate using the ordinate index number, instead of returning the entire varray.

Like this:

select
  (my_sdo_geom_col).GetOrdinates(1)  <-- I used (1) instead of ()
from
  lines

Error:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETORDINATES'

Desired result:
10

In a related post, we said:

...the syntax of extracting collection elements by index is not supported in SQL.

Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL?

But, if I understand correctly, that statement refers to using an object attribute, not a member function.


Question:

Is there a way to extract a varray element by index number using the custom type's member function?

I'm aware there are other ways of getting a geometry's ordinates. I'm doing this as a learning exercise/experiment, as it relates to some ideas I've submitted to Oracle:


Solution

  • Access the ordinate value from the array in the PL/SQL scope (within the member function) rather than returning the entire array to the SQL scope and trying to access the ordinate from there (which does not work):

    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 
        return shape.sdo_ordinates(idx);
      END;
    END;
    / 
    

    Then you can use it in the query:

    select (my_sdo_geom_col).GetOrdinates(3)
    from   lines
    

    Outputs:

    (MY_SDO_GEOM_COL).GETORDINATES(3)
    30

    db<>fiddle here