Get length of oracle.sql.array

Question

On an Oracle DB I have a table with SDO_GEOMETRY objects. I would like to query the database for those polygons with less than x edges. In theory this would be easy with a query like

SELECT * FROM myTable t WHERE LENGTH(t.geometry.sdo_ordinates) < x

Obviously the LENGTH funtion is defined for char and the type of t.geometry.sdo_ordinates is oracle.sql.ARRAY so that doesn't work. Shouldn't there be a trivial way to SELECT the length or an array in Oracle? Somehow I'm unable to get the syntax right.

PS: I kind of solved my search with the following query, still the original questerion remains, isn't there an array size/length function?

SELECT * FROM myTable t WHERE LENGTH(t.geomety.Get_WKT()) < (x * c)

Answer 1

No, there is no simple sql function that counts the elements of an array.

However as mentioned here, another idea is a PL/SQL script.

create or replace function get_count(ar in SDO_ORDINATE_ARRAY) return number is
begin
   return ar.count;
end get_count;

---

t.geometry.sdo_ordinates.COUNT is a PL/SQL attribute that can be used within functions/procedures. Thus that is not a function useable in plain SQL.

Attribute:

value.someAttribute

Function:

doSomething(value)

Clarification: Functions have return values, procedures don't. Source