Use SDE.ST_GEOMETRY functions in a custom function

Question

I have a dummy query where I generate an SDE.ST_GEOMETRY point:

select 
    shape as srid_test
from   
    (
    select
        sde.st_geometry ('point (10.01 50.76)', 4326) as shape
    from
        dual
    )

---

I've written a function that returns the SRID of the point (it's just a test function--it doesn't have a practical purpose yet).

The function works as expected.

with function test_function (shape_in in sde.st_geometry) return number is 
  srid number;
begin
--    srid := sde.st_srid (shape_in);
    select sde.st_srid (shape_in) into srid from dual;
    return srid;
end;

select 
    test_function(shape) as srid_test
from   
    (
    select
        sde.st_geometry ('point (10.01 50.76)', 4326) as shape
    from
        dual
    )

---

You'll notice that I use a query to assign the SRID to the SRID variable:

select sde.st_srid (shape_in) into srid from dual;

Instead of using a query to get the SRID, I want to simply use the ST_SRID function:

 srid := sde.st_srid (shape_in);

---

However, I'm getting an error when I switch from the query to the function:

ORA-06553: PLS-548: invalid use of operator.

with function test_function (shape_in in sde.st_geometry) return number is 
  srid number;
begin
    srid := sde.st_srid (shape_in);
--    select sde.st_srid (shape_in) into srid from dual;
    return srid;
end;

select 
    test_function(shape) as srid_test
from   
    (
    select
        sde.st_geometry ('point (10.01 50.76)', 4326) as shape
    from
        dual
    )

---

Why am I getting this error?

(Oracle 18c, 10.7.1 Geodatabase)

Answer 1

I am surprised all the SDE experts did not already chime in.

Your title shows the problem and your description provides the proper answer, so you are already all the way there.

SDE.ST_SRID is not actually a function, rather its an Oracle operator.
https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/defining-operators.html#GUID-B4A841A6-3251-478B-9805-2CB8FF75D5F4

So you cannot use user-defined operators as you would a PLSQL function - they work in the SQL and not the PLSQL context. I have never found them all that useful myself. More contextual information here https://stackoverflow.com/questions/20140306/create-user-defined-operator-with-left-right-sides

Thus your solution of using the INTO SQL statement is the right way to do this.

Now operators do wrap an existing function, so if you really want to you can get the bound function of an operator from DBA_OPBINDNGS and use it directly. In this case that is SDE.ST_GEOMETRY_OPERATORS.ST_SRID_F. So if you rewrite your statement as

with function test_function(shape_in in sde.st_geometry) 
return number 
is 
  srid number;
begin
    srid := sde.st_geometry_operators.st_srid_f(shape_in);
    return srid;
end;

select 
test_function(shape) as srid_test
from (
   select
   sde.st_geometry ('point (10.01 50.76)', 4326) as shape
   from
   dual
)

it will work as expected.