What does OBJECT_TYPE(…) do in a SELECT query?

Question

Regarding the following query from a related post: Collapse vertex rows into nested table type (aggregated 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,
       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

I'm unfamiliar with using an object type this way: MDSYS.VERTEX_TYPE(...).

What does MDSYS.VERTEX_TYPE(...) achieve in that context? Is the type being used to construct an object, similar to how a constructor function would be used?

Seems like something not generally to be copied [since](https://docs.oracle.com/database/121/SPATL/sdo_util-getvertices.htm#SPATL1237): "The VERTEX_SET_TYPE and VERTEX_TYPE types are intended for use by Oracle only." — Damien_The_Unbeliever, Jun 14 '22 at 07:52
@Damien_The_Unbeliever Yeah, I've asked Oracle Spatial to clarify why they think it would be such a big problem: [Why shouldn't we use VERTEX_SET_TYPE and VERTEX_TYPE?](https://community.oracle.com/tech/apps-infra/discussion/4499664/why-shouldnt-we-use-vertex-set-type-and-vertex-type). — User1974, Jun 14 '22 at 08:16

MT0 · Accepted Answer · 2022-06-14T08:22:35.170

What does MDSYS.VERTEX_TYPE(...) achieve in that context? Is the type being used to construct an object, similar to how a constructor function would be used?

It is a call to the constructor of the object and creates a new instance of that object with (assuming a default constructor) the instance's attributes set to the values of the arguments of the constructor.

(If the call is to a non-default constructor then the instance will be created and the attributes initialized according to the logic defined in that constructor; in which case you either need to read the source code or the documentation to work out what that logic is. A default constructor is simplest because it just sets the attributes in the order they were defined in the type declaration.)

For example:

CREATE TYPE test_type IS OBJECT(
  a VARCHAR2(10),
  b VARCHAR2(10),
  c VARCHAR2(10)
);

CREATE TYPE test_array_type IS TABLE OF test_type;

Then:

test_type('a_value', 'b_value', 'c_value')

Calls the default constructor of the test_type object and sets the new instance's:

a attribute to 'a_value';
b attribute to 'b_value'; and
c attribute to 'c_value'.

If you then use:

test_array_type(
  test_type('a1', 'b1', 'c1'),
  test_type('a2', 'b2', 'c2'),
  test_type('a3', 'b3', 'c3')
)

Then you create 3 instances of test_type and those 3 instances are arguments for the test_array_type's constructor and an instance of test_array_type will be returned that contains those 3 elements.

Calling MDSYS.VERTEX_TYPE(...) or MDSYS.VERTEX_SET_TYPE(...) is exactly the same process. You are calling the constructor of a type defined in the MDSYS schema and creating instances of those types with the new instance's attributes set to the arguments of the constructor function.

What does OBJECT_TYPE(…) do in a SELECT query?

1 Answers1