Oracle recommends the storage of single points using the SDO_POINT_TYPE structure. There are, of course, a number of situations where this is not possible or recommended:
1. Storage using the SDO_POINT_TYPE structure is impossible if the point had more than 3 ordinates.
2. If the geometry type of a particular SDO_GEOMETRY column in a table is MULTIPOINT and a particular observed value is a single point, multipoint.
How is it that SDO_ORDINATE_ARRAY based single points arise? Variously. The most common is that external loading software does so using a wrong parameter (some loader have switches for loading into SDO_POINT_TYPE rather than SDO_ORDINATE_ARRAY but the user forgets to set the right switch). Others (I’m guessing here) include ignorance; perhaps perceptions that a homogeneous approach to storing all geometry ordinates using the one method somehow brings benefits in terms of programming, support etc.
Conversion from SDO_ORDINATE_ARRAY to SDO_POINT_TYPE can occur in two ways:
This is an easy thing to do as the following shows.