how to extract points attribute in st_geometry object in oracle

Question

I would like to extract shape data from a table. I can get entity,numpts,minx,miny all the attributes except POINTS which is is BLOB. I would like to get the data as varchar. How can I extract points attrribute's value in sd.st_geometry column ?

SOLUTION:I did extract POINTS attribute as treat(shape as sde.st_geometry).points as points. Thanks.

This is my table

CREATE TABLE X
(
  OBJECTID      INTEGER                         NOT NULL,
  GRID_ID       VARCHAR2(17 BYTE),
  H3_10_INT_ID  VARCHAR2(256 BYTE),
  CITY_NAME        VARCHAR2(30 BYTE),
  DISTRICT_NAME      VARCHAR2(30 BYTE),
  NEIGHBOURHOOD_NAME VARCHAR2(50 BYTE),
  SHAPE         SDE.ST_GEOMETRY
)
LOB ("SHAPE"."POINTS") STORE AS SECUREFILE (
  TABLESPACE  TBS_X
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  CACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE TBS_X
PCTUSED    0
PCTFREE    0
INITRANS   4
MAXTRANS   255
STORAGE    (
            INITIAL          400K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

This is one sample of the table.

Insert into X (OBJECTID, GRID_ID, H3_10_INT_ID, CITY_NAME, DISTRICT_NAME, NEIGHBOURHOOD_NAME) Values (133, '8a1ec8000537fff', '622038258059280383', 55498, 'Tekirdag', 'Suleymanpasa', 'Osmanli'); COMMIT;

You can see the sample shape object when I double click

SELECT objectid,sde.st_entity(SHAPE),sde.st_numpoints(SHAPE),
sde.st_minx(SHAPE),sde.st_miny(SHAPE),sde.st_maxx(SHAPE),sde.st_maxy(SHAPE),sde.st_minz(SHAPE),sde.st_maxz(SHAPE),sde.st_maxm(SHAPE),sde.st_area(SHAPE),
sde.st_length(SHAPE),
sde.st_srid(SHAPE)
FROM X

Answer 1

If you have the sample data:

CREATE TABLE X
(
  OBJECTID           INTEGER           NOT NULL,
  SHAPE              ST_GEOMETRY
);

INSERT INTO x (objectid, shape)
VALUES (
  1,
  ST_LINESTRING(
    ST_POINT_ARRAY(
      ST_POINT(1, 2, 4326),
      ST_POINT(3, 4, 4326),
      ST_POINT(5, 6, 4326)
    )
  )
);

Then you can use:

SELECT objectid,
       TREAT(x.shape AS ST_LINESTRING).ST_NUMPOINTS() AS numpoints,
       p.*
FROM   x
       CROSS JOIN LATERAL (
         SELECT p.st_x() AS x,
                p.st_y() AS y
         FROM   TABLE(TREAT(x.shape AS ST_LINESTRING).ST_POINTS()) p
       ) p

Which outputs:

OBJECTID	NUMPOINTS	X	Y
1	3	1	2
1	3	3	4
1	3	5	6

fiddle