sqloracleerror-codeoracle18coracle-spatial

Look up SDO_GEOMETRY validation error code using SQL


I have a query that validates an SDO_GEOMETRY in Oracle 18c:

select
    sdo_geom.validate_geometry_with_context(   
        sdo_geometry ('polygon ((676832.320 4857578.086, 665287.423 4857578.086, 665277.423 4878109.585, 
                                 676832.320 4878119.585, 676842.320 4857588.086))', 26917)
                                     , 0.005) as validation
from
    dual


VALIDATION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
-----------------------------
13348 [Element <1>] [Ring <1>]

(1 row selected.)

enter image description here

db<>fiddle

The query produces an error code in a text column, but it doesn't describe what the code means.

I am able look up the error manually in the docs: 82 ORA-12700 to ORA-19400

ORA-13348: polygon boundary is not closed

Cause: The boundary of a polygon does not close.

Action: Alter the coordinate values or the definition of the SDO_GTYPE or SDO_ETYPE attribute of the geometry.

But manually looking up those error codes is inconvenient.

Is there a way to enhance the query so that it returns the full error description? (get the description from the database)


Solution

  • Assuming you can parse the string to pull out the error message, you can pass it to sqlerrm to get the text of the error (note that you're apparently getting a positive value, you'd need to negate that value to pass it to sqlerrm). I would assume that you could just look for everything before the first space to get the error number but I don't have a huge sample set to work with.

    declare
      l_message varchar2(1000);
    begin
      l_message := sqlerrm( -13348 );
      dbms_output.put_line( l_message );
    end;
    /
    

    will print

    ORA-13348: polygon boundary is not closed