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)

Yes, I actually tried `t.geometry.sdo_ordinates.COUNT` but it doesn't work. — terix2k11, Aug 10 '15 at 12:02

score 4 · Accepted Answer · edited May 23 '17 at 10:27

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

Get length of oracle.sql.array

1 Answers1