developers

    Forum Stats

  • 3,874,058 Users
  • 2,266,667 Discussions
  • 7,911,708 Comments

Discussions

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

User_1871
User_1871 Member Posts: 247 Red Ribbon
edited Jun 8, 2022 7:29PM in SQL & PL/SQL

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.

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,935 Red Diamond
    Answer ✓

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

    SQL> SELECT SQLERRM(-1422) FROM DUAL;
    SELECT SQLERRM(-1422) FROM DUAL
           *
    ERROR at line 1:
    ORA-00904: "SQLERRM": invalid identifier
    
    SQL> SELECT SYS.STANDARD.SQLERRM(-1422) FROM DUAL;
    
    SYS.STANDARD.SQLERRM(-1422)
    --------------------------------------------------------------------------------
    ORA-01422: exact fetch returns more than requested number of rows
    
    SQL>
    

    SY.

    User_1871

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,935 Red Diamond
    Answer ✓

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

    SQL> SELECT SQLERRM(-1422) FROM DUAL;
    SELECT SQLERRM(-1422) FROM DUAL
           *
    ERROR at line 1:
    ORA-00904: "SQLERRM": invalid identifier
    
    SQL> SELECT SYS.STANDARD.SQLERRM(-1422) FROM DUAL;
    
    SYS.STANDARD.SQLERRM(-1422)
    --------------------------------------------------------------------------------
    ORA-01422: exact fetch returns more than requested number of rows
    
    SQL>
    

    SY.

    User_1871
  • User_1871
    User_1871 Member Posts: 247 Red Ribbon
    edited Jun 8, 2022 7:27PM

    @Solomon Yakobson

    Thanks! That works:

    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
    


    I wonder why the docs say, "A SQL statement cannot invoke SQLERRM"? That seems misleading.

    I posted an idea: Clarify docs: "A SQL statement cannot invoke SQLERRM"

  • ora_1978
    ora_1978 Member Posts: 523 Bronze Badge

    Yes some functions can be used in SQL and some cannot be used in PL SQL.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,935 Red Diamond

    I wonder why the docs say, "A SQL statement cannot invoke SQLERRM"? That seems misleading.

    ORACLE SQL & PL/SQL are two separate engines since day Oracle introduced PL/SQL, so same name function in SQL and PL/SQL providing same functionality are two separate sets of code. Then at some point Oracle introduced package SYS.STANDARD. I believe it was in (failed) attempt to come up with a single engine. So docs are right - there is no such SQL function SQLERRM buit there is its counterpart in package SYS.STANDARD.

    SY.

    User_1871
developers