1

I've made a custom version of Oracle's GetVertices() function. The solution involves creating a user-defined type and a function.

Here is the code (working):

CREATE TYPE vertex_type_cust AS object
(
  x  NUMBER,
  y  NUMBER,
  z  NUMBER,
  w  NUMBER,
  id NUMBER );

CREATE TYPE vertex_set_type_cust AS TABLE OF vertex_type_cust;

CREATE OR replace FUNCTION getvertices_cust(geometry mdsys.sdo_geometry)
  RETURN vertex_set_type_cust
IS
  i      NUMBER;
  dims   NUMBER;
  coords NUMBER;
  result vertex_set_type_cust;
  dim mdsys.sdo_dim_array;
  is_zero BOOLEAN;
  etype   NUMBER;
BEGIN
  result := vertex_set_type_cust();
  -- handle the POINT case here
  IF (geometry.sdo_ordinates IS NULL) THEN
    result.extend;
    result(1) := vertex_type_cust(geometry.sdo_point.x, geometry.sdo_point.y, geometry.sdo_point.z,NULL,1);
    RETURN result;
  END IF;
  -- all other cases here
  coords := geometry.sdo_ordinates.count;
  dims := geometry.get_dims;
  IF (dims = 0) THEN
    RETURN result;
  END IF;
  coords := coords/dims;
  FOR i          IN 0 .. coords-1
  LOOP
    result.extend;
    IF (dims = 2) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(2*i+1), geometry.sdo_ordinates(2*i+2), NULL,NULL,i+1);
    ELSIF (dims = 3) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(3*i+1), geometry.sdo_ordinates(3*i+2), geometry.sdo_ordinates(3*i+3) ,NULL,i+1);
    ELSIF (dims = 4) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(4*i+1), geometry.sdo_ordinates(4*i+2), geometry.sdo_ordinates(4*i+3), geometry.sdo_ordinates(4*i+4), i+1);
    END IF;
  END LOOP;
  RETURN result;
END;

Test data:

create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);

insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )

select
    a.line_id,
    b.id as vertex_id,
    b.x, 
    b.y
from
    a_sdo_geometry_tbl a
cross join
    table(getvertices_cust(a.shape)) b            --<<-- the query uses the custom function
order by 
    a.line_id, b.id;

Problem:

I'm testing this solution with online Oracle environments because I don't have CREATE TYPE privileges in my company's Oracle db.

I'm able to get the above code working when I run it in Oracle's free testing environment (19c):

However, when I try to run the code with db<>fiddle, I get an error when I create the function:


Question:

How can I avoid getting that error when creating the custom function with db<>fiddle?

I know there is a version difference between Oracle Live and db<>fiddle (19c vs. 18c). But I would be surprised if that was the problem. This doesn't seem like a version issue to me.

1 Answer 1

3

Add a / after the function definition.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4031e203694674dbc764b5290dd93bdb

3
  • What’s the purpose of the slash? I haven’t needed to use it in Toad or SQL Developer.
    – User1974
    Sep 26, 2021 at 22:25
  • 1
    But in SQL*PLUS for example we need to use it after PL/SQL block. Oct 2, 2021 at 1:46
  • 1
    @User1973 you'd have to ask the developers of DBFiddle. The slash character is not part of Oracle SQL or PL/SQL language syntax. Some client tools use it as a "run" command or statement separator, but I see no reason why DBFiddle would need either. Oct 3, 2021 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.