Why does selecting an attribute of an Oracle object require an alias?

Question

My GIS software has an Oracle 18c object type called ST_GEOMETRY.

The object type has attributes:

The attributes of the spatial type represent the following information:

• Entity: The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure.
• Numpts: The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point for each separator.
• Minx, miny, maxx, maxy: The spatial envelope of the geometry
• Area: The area of the geometry
• Len: The perimeter length of the geometry
• SRID: Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the ST_Spatial_References table
• Points: Contains the byte stream of the point coordinates that define the geometry

---

I can select an object's attribute using one of two methods:

1. Use Oracle's TREAT() function.

select 
 treat(shape as sde.st_geometry).numpts as num_points
from 
 a_test_table
NUM_POINTS
----------
 37
 161

2. Or I can simply use an alias:

select 
 a.shape.numpts as num_points
from 
 a_test_table a
NUM_POINTS
----------
 37
 161

---

Question:

Why is it necessary to use an alias to select an attribute from an Oracle object?

If I were to remove the alias...

select 
 shape.numpts as num_points
from 
 a_test_table

Then I would get an error:

ORA-00904: "SHAPE"."NUMPTS": invalid identifier

• User1974: Techniques for selecting an object's attribute: You can also surround the object name in braces/brackets to indicate it's not a table alias: select (shape).sdo_gtype

Answer 1

Because Oracle made it so.

t_alias

t_alias

Specify a correlation name, which is an alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

Answer 2

It's a requirement so that the SQL parser recognizes the elements. Without the alias it gets confused with the dot notation. Note that it applies to all object types. Not just ST_GEOMETRY or SDO_GEOMETRY.

(I was not aware of the treat(... as ...) trick. But then using an alias is still simpler.