Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT *
FROM   ALL_TYPES
WHERE  OWNER = 'MDSYS'
AND    TYPECODE = 'COLLECTION'
AND    TYPE_NAME LIKE '%V%'


OWNER
TYPE_NAME
TYPE_OID
TYPECODE
ATTRIBUTES
METHODS
PREDEFINED
INCOMPLETE
FINAL
INSTANTIABLE
PERSISTABLE
SUPERTYPE_OWNER
SUPERTYPE_NAME
LOCAL_ATTRIBUTES
LOCAL_METHODS
TYPEID


MDSYS
RDF_LONGVARCHARARRAY
0xC9CB6416B1CC7DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
RDF_VARCHARARRAY
0xC9CB6416B1C87DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
SDO_VPOINT_TYPE
0x5300427C0DBE4C3BE03400400B401354
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
ST_CURVE_ARRAY
0x4BA3494AB3F4E5B6E040578CB2057856
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
T_VARCHAR2_ARRAY
0xC9CB6416BFBC7DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
V81_INDEX_OBJ_ARRAY
0xC9CB64167B237DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
V81_NT_IND_TYPE
0xC9CB64167B277DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x

MDSYS
VERTEX_SET_TYPE
0xC9CB64167B847DCFE0532783E80A2303
COLLECTION
0
0
NO
NO
YES
YES
YES
null
null
null
null
0x


WITH cte (id, x, y) as (
  SELECT 1, 100, 101 FROM DUAL UNION ALL
  SELECT 1, 200, 201 FROM DUAL UNION ALL
  SELECT 2, 300, 301 FROM DUAL UNION ALL
  SELECT 2, 400, 401 FROM DUAL UNION ALL
  SELECT 2, 500, 501 FROM DUAL UNION ALL
  SELECT 3, 600, 601 FROM DUAL UNION ALL
  SELECT 3, 700, 701 FROM DUAL UNION ALL
  SELECT 3, 800, 801 FROM DUAL UNION ALL
  SELECT 3, 900, 901 FROM DUAL
)
SELECT id, x, y
FROM   cte


ID
X
Y


1
100
101

1
200
201

2
300
301

2
400
401

2
500
501

3
600
601

3
700
701

3
800
801

3
900
901


WITH cte (id, x, y) as (
  SELECT 1, 100, 101 FROM DUAL UNION ALL
  SELECT 1, 200, 201 FROM DUAL UNION ALL
  SELECT 2, 300, 301 FROM DUAL UNION ALL
  SELECT 2, 400, 401 FROM DUAL UNION ALL
  SELECT 2, 500, 501 FROM DUAL UNION ALL
  SELECT 3, 600, 601 FROM DUAL UNION ALL
  SELECT 3, 700, 701 FROM DUAL UNION ALL
  SELECT 3, 800, 801 FROM DUAL UNION ALL
  SELECT 3, 900, 901 FROM DUAL
)
SELECT id,
       CAST(
         COLLECT(
           MDSYS.VERTEX_TYPE(x, y, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, id)
           ORDER BY x, y
         )
         AS MDSYS.VERTEX_SET_TYPE
       )
FROM   cte
GROUP BY id
WITH cte (id, x, y) as (
  SELECT 1, 100, 101 FROM DUAL UNION ALL
  SELECT 1, 200, 201 FROM DUAL UNION ALL
  SELECT 2, 300, 301 FROM DUAL UNION ALL
  SELECT 2, 400, 401 FROM DUAL UNION ALL
  SELECT 2, 500, 501 FROM DUAL UNION ALL
  SELECT 3, 600, 601 FROM DUAL UNION ALL
  SELECT 3, 700, 701 FROM DUAL UNION ALL
  SELECT 3, 800, 801 FROM DUAL UNION ALL
  SELECT 3, 900, 901 FROM DUAL
)
SELECT id,
       CARDINALITY(
         CAST(
           COLLECT(
             MDSYS.VERTEX_TYPE(x, y, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, id)
             ORDER BY x, y
           )
           AS MDSYS.VERTEX_SET_TYPE
         )
       )
FROM   cte
GROUP BY id


ID
CARDINALITY(CAST(COLLECT(MDSYS.VERTEX_TYPE(X,Y,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,ID)ORDERBYX,Y)ASMDSYS.VERTEX_SET_TYPE))


1
2

2
3

3
4

OWNER	TYPE_NAME	TYPE_OID	TYPECODE	PREDEFINED	INCOMPLETE	FINAL	INSTANTIABLE	PERSISTABLE	SUPERTYPE_OWNER	SUPERTYPE_NAME	LOCAL_ATTRIBUTES	LOCAL_METHODS	TYPEID
MDSYS	RDF_LONGVARCHARARRAY	0xC9CB6416B1CC7DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	RDF_VARCHARARRAY	0xC9CB6416B1C87DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	SDO_VPOINT_TYPE	0x5300427C0DBE4C3BE03400400B401354	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	ST_CURVE_ARRAY	0x4BA3494AB3F4E5B6E040578CB2057856	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	T_VARCHAR2_ARRAY	0xC9CB6416BFBC7DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	V81_INDEX_OBJ_ARRAY	0xC9CB64167B237DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	V81_NT_IND_TYPE	0xC9CB64167B277DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x
MDSYS	VERTEX_SET_TYPE	0xC9CB64167B847DCFE0532783E80A2303	COLLECTION	NO	NO	YES	YES	YES	null	null	null	null	0x

ID	X	Y
1	100	101
1	200	201
2	300	301
2	400	401
2	500	501
3	600	601
3	700	701
3	800	801
3	900	901

ID	X	Y
1	100	101
1	200	201
2	300	301
2	400	401
2	500	501
3	600	601
3	700	701
3	800	801
3	900	901

ID	X	Y
1	100	101
1	200	201
2	300	301
2	400	401
2	500	501
3	600	601
3	700	701
3	800	801
3	900	901