title: How to insert points coming from sub-query as an object of SDO_GEOMETRY in Oracle spatial database?
body: <p>Say, I have a table named <code>buildings</code> which could be created by the following query:</p>
  <pre><code>create table buildings(
    building_id number primary key,
    building_name varchar2(32),
    shape sdo_geometry
  );
  </code></pre>
  <p>I can insert a rectangle into it by the following query:</p>
  <pre><code>insert into buildings values(
    4, -- index
    'Reading Room', -- building_name
    sdo_geometry(
      2003, --SDO_GTYPE: dltt - 2(2D)0(linear referencing)03(polygon)
      8307, --SDO_SRID: coordinate system
      null, --SDO_POINT: it is for point inserting, if the next two field = null, then it could not be null.
      sdo_elem_info_array( --SDO_ELEM_INFO:
      1, --SDO_STARTING_OFFSET: indicates from which index of the next param of SDO_GEOMETRY would be considered, starts from 1.
      1003, --SDO_ETYPE: 1(exterior, interior  - 2)003(this digits usually comes from SDO_GTYPE)
      3),  --SDO_INTERPRETATION: 1 - simple polygon, 2 - polygon connecting arcs, 3 - rectangle, 4 - circle etc.
      sdo_ordinate_array(
        24.916312, 91.832393,
        24.916392, 91.832678
      ) --SDO_ORDINATES: co-ordinates of the geometry
                                -- two corner points of the main diagonal
    )
  );
  </code></pre>
  <p>Here, two geodetic points came from real data as an object of <code>sdo_ordinate_array</code>. The following two points are inserted directly in the above query:</p>
  <ol>
  <li>24.916312, 91.832393</li>
  <li>24.916392, 91.832678</li>
  </ol>
  <p>Now, I want to insert these two points coming from two different sub-query.</p>
  <p>Sub-queries would be like the following:</p>
  <pre><code>SELECT 180+SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.X, 
    180-SDO_GEOM.SDO_CENTROID(c.shape, m.diminfo).SDO_POINT.Y
    FROM buildings c, user_sdo_geom_metadata m 
    WHERE m.table_name = 'BUILDINGS' AND m.column_name = 'SHAPE' 
    AND c.building_name = 'IICT';
  </code></pre>
  <p>So, the result of the query would be like:</p>
  <pre><code>         X          Y
  ---------- ----------
  24.9181097 91.83097409 
  </code></pre>
  <p>How can I convert this result to comma separated value, like: <code>24.9181097, 91.83097409</code>?</p>
  <p>So that I could replace the following code:</p>
  <pre><code>sdo_ordinate_array(
      24.916312, 91.832393,
      24.916392, 91.832678
  ) --SDO_ORDINATES: co-ordinates of the geometry
  </code></pre>
  <p>with:</p>
  <pre><code>sdo_ordinate_array(
      (/*sub-query*/),
      (/*another-subquery*/)
  ) --SDO_ORDINATES: co-ordinates of the geometry
  </code></pre>
  <p>I had google it and explored several blogs but had no luck.</p>
  <h3>N.B.:</h3>
  <p>The title seemed inappropriate, but the straightforward versions of the sub-queries return the object of <code>SDO_GEOMETRY</code>. If you explored on oracle spatial queries, then it is clear to you that I just retrieve the value of X and Y from the returned object.</p>