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

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:

Oracle Spatial Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)
Oracle Idea: Support extracting collection elements by index in SQL

CodePudding user response：

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

(MY_SDO_GEOM_COL).GETORDINATES(3)
30

db<>fiddle here