SQL> desc TEST
Name Null? Type

SQL> select id, geometry from test
where rownum<7
;

TEST GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

another table TESTZ is created to insert the fetched values from TEST
table

SQL> desc TESTZ
Name Null? Type

The following pl/sql program is used for fetching the required data

DECLARE

CURSOR p_cur

IS

select id,geometry from test order by rownum ;

es number;
nr number;

locus mdsys.sdo_geometry;

BEGIN

for i in p_cur
loop

locus := i.geometry ;

es:=locus.sdo_ordinates(4);
nr:=locus.sdo_ordinates(5);

insert into testz values(i.idz,es,nr);
end loop;

END;

/

ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 20

the same Script is working well for other data sets But its giving the
following Errror for the ABOVE
data set, the test table got 90,000 records

please Suggest me the required Solution to fecth the 4th and 5th
postioned values from SDO_ORDINATE_ARRAY ;

Many thanks

ZAMEER

SQL> connect test/test

Connected.

SQL> desc x3

 Name                                      Null?    Type


--------

 ID                                                 VARCHAR2(10)

 SPAT                                               SDO_GEOMETRY



SQL> insert into x3 values (1, sdo_geometry(2001, 8307, null, sdo_elem_info_array(1, 1003, 1),

  2  sdo_ordinate_array(-77.02287, 39.90437,

  3  -77.024645, 39.90409,

  4  -77.02615, 39.90331)));



1 row created.



SQL> insert into x3 values (1, sdo_geometry(2001, 8307, null, sdo_elem_info_array(1, 1003, 1),

  2  sdo_ordinate_array(-78.02287, 39.90437, -78.024645, 39.90409, -78.02615, 39.90331)));



1 row created.



SQL> commit;



Commit complete.



SQL> create or replace function get_array_mem(geom sdo_geometry, mem number default 1) return number

  2  is

  3  begin

  4    if mem<1 or mem>geom.sdo_ordinates.count()

  5    then

  6      return null;

  7    end if;

  8

  9    return geom.sdo_ordinates(mem);

 10  end;

 11  /



Function created.



SQL> select get_array_mem(spat, 2) from x3;



GET_ARRAY_MEM(SPAT,2)


             39.90437

             39.90437



SQL>