I recently came across an Oracle 18c function that seems to be available in PL/SQL, but not in SQL:
13.63 SQLERRM Function
SQLERRM()
A SQL statement cannot invoke SQLERRM.
That got me wondering, are there certain kinds of functions that are only available in PL/SQL, not SQL?
-----------------------------------------------------------
The reason I ask:
I've written a custom function that takes SDO_GEOMETRY validation text, and returns the Oracle error code description:
Look up SDO_GEOMETRY validation error code using SQL (https://stackoverflow.com/a/72550554/5576771)
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
--Result: '13348 [Element <1>] [Ring <1>]'
)
ERROR_DESCRIPTION
-------------------
ORA-13348: polygon boundary is not closed
db<>fiddle
Out of curiosity, I had wondered if I could use the SQLERRM function right in the SELECT clause in a query, and avoid the need for a custom function.
It seems the answer is no. And I'm curious if that limitation applies to other functions too.