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.)
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)
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