Search code examples
sqloracleindexingoracle18cvarray

Convert SDO_ORDINATE_ARRAY to built-in VARRAY (to create function-based index)

Oracle 18c:

I can get SDO_ORDINATE_ARRAY values from SDO_GEOMETRY objects:

create table test_table (shape sdo_geometry);

insert into test_table (shape) values (sdo_geometry('linestring(10 20, 30 40, 50 60)'));
insert into test_table (shape) values (sdo_geometry('linestring(70 80, 90 100)'));
insert into test_table (shape) values (sdo_geometry('linestring(110 120, 130 140, 150 160, 170 180)'));

select
    (shape).sdo_ordinates as sdo_ordinate_array
from
    test_table


SDO_ORDINATE_ARRAY
------------------
MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60)
MDSYS.SDO_ORDINATE_ARRAY(70, 80, 90, 100)
MDSYS.SDO_ORDINATE_ARRAY(110, 120, 130, 140, 150, 160, 170, 180)

db<>fiddle

For what it's worth, this is what the definition of the MDSYS.SDO_ORDINATE_ARRAY type looks like in SQL Developer:

create or replace TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER

Using a function, I want to convert the SDO_ORDINATE_ARRAYs to the built-in VARRAY datatype.

Reason: I want to experiment with storing the ordinates in a non-spatial Function-Based Index. If I understand correctly, only built-in datatypes are supported by FBIs, not Oracle-supplied types or abstract datatypes like SDO_ORDINATE_ARRAY or SDO_GEOMETRY.

For example, if I were to try to create the following FBI, it would fail:

create index sdo_ordinates_idx on test_table ((shape).sdo_ordinates);

Error:

Error starting at line : 12 in command -
create index sdo_ordinates_idx on test_table ((shape).sdo_ordinates)
Error report -
ORA-02327: cannot create index on expression with datatype ADT    🡄 ADT=Abstract Datatype
02327. 00000 -  "cannot create index on expression with datatype %s"
*Cause:    An attempt was made to create an index on a non-indexable
           expression.
*Action:   Change the column datatype or do not create the index on an
           expression whose datatype is one of  VARRAY, nested table, object,
           LOB, or  REF.

So I can't create an FBI on SDO_ORDINATE_ARRAY. But I'm hoping I can convert the ordinates to a regular VARRAY instead and make an FBI on those values.

Question:

Is there a way to convert SDO_ORDINATE_ARRAYs to built-in VARRAYs? (for the purpose of a function-based index)

I would prefer to store the ordinates as a proper VARRAYs, rather than convert them to text. Reason: I believe the SDO_GEOMETRY constructor only accepts array values, not text.

Solution

  • You state:

    the definition of the MDSYS.SDO_ORDINATE_ARRAY type looks like in SQL Developer:

    create or replace TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER

    and then ask:

    Is there a way to convert SDO_ORDINATE_ARRAYs to built-in VARRAYs? (for the purpose of a function-based index)

    It is already a VARRAY, you do not need to convert it.

    However

    The error message states:

    Error report -
ORA-02327: cannot create index on expression with datatype ADT    🡄 > ADT=Abstract Datatype
02327. 00000 -  "cannot create index on expression with datatype %s"
*Cause:    An attempt was made to create an index on a non-indexable
           expression.
*Action:   Change the column datatype or do not create the index on an
           expression whose datatype is one of VARRAY, nested table, object,
           LOB, or  REF.

    In the action section it specifically states that you cannot create an index on an expression whose datatype is one of VARRAY or nested table. So what you are asking is impossible; you cannot create an index on a VARRAY.

    Finally

    You state:

    I would prefer to store the ordinates as a proper VARRAYs, rather than convert them to text. Reason: I believe the SDO_GEOMETRY constructor only accepts array values, not text.

    Looking at the source:

    select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'SDO_GEOMETRY'
ORDER BY line

    The MDSYS_SDO_GEOMETRY type has the constructors:

       ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB,
     srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
     DETERMINISTIC PARALLEL_ENABLE,
   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2,
     srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
     DETERMINISTIC PARALLEL_ENABLE,
   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB,
     srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
     DETERMINISTIC PARALLEL_ENABLE,
   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(input_clob IN clob,
     auxiliary_info IN varchar2, crs IN VARCHAR2 DEFAULT NULL)
     RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE,
   ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(input_vchar IN varchar2,
     auxiliary_info IN varchar2, crs IN VARCHAR2 DEFAULT NULL)
     RETURN SELF AS RESULT DETERMINISTIC PARALLEL_ENABLE
   CASCADE

    None of those constructors take a VARRAY argument. They all take strings (VARCHAR2 or CLOB) except for one that takes a BLOB.

    db<>fiddle here