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
– terix2k11, Commented Aug 10, 2015 at 12:02

Community · Accepted Answer · 2017-05-23 10:27:28Z

5

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

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Aug 10, 2015 at 11:56

michaelbahr

4,9933 gold badges44 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get length of oracle.sql.array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related