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

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 in the docs is inconvenient.

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

CodePudding user response：

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

CodePudding user response：

Building on @JustinCave's answer, here's a custom function that gets the error description from the validation text:

with function error_description(validation in varchar2) return varchar2 is
begin
    return sqlerrm(substr(validation, 1, instr(validation,' ') - 1) * -1);  --Multiply by -1. Oracle error codes seem to be "negative".
end;
    
select
    error_description(validation) as error_description
from
    (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)

ERROR_DESCRIPTION
-------------------
ORA-13348: polygon boundary is not closed

That seems to work as expected.

Would be happy to hear about possible improvements.

Edit:

As pointed out by @SolomonYakobson in a related post, the SQLERRM() function can also be used in a SELECT query (without the need for a custom function).

Are certain kinds of Oracle functions only available in PL/SQL, not SQL?

Many of the functions are defined in Oracle supplied package SYS.STANDARD.

Example: SELECT SYS.STANDARD.SQLERRM(-1422) FROM DUAL;

So we just need to fully qualify the function: SYS.STANDARD.SQLERRM()

select
    sys.standard.sqlerrm(substr(validation, 1, instr(validation,' ') - 1) * -1) error_description
from
    (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)


ERROR_DESCRIPTION
---------------------
ORA-13348: polygon boundary is not closed

That works as expected.