DBFiddle: Custom type and function (ORA-24344)

I’m attempting to create 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; 

Scenario:

(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):

  • https://livesql.oracle.com/
  • Create function: screenshot
  • Query using the function: screenshot

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

  • db<>fiddle session (18c)
  • ORA-24344: success with compilation error

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 cause of the problem. This doesn’t seem like a version issue to me.