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)

• Gabriel De Luca: Seems like the srid := part is equivalent to into srid, so, what happens if: srid := select sde.st_srid( shape_in) from dual; ?
• User1974: @GabrielDeLuca Good idea. I tried srid := (select sde.st_srid (shape_in) into srid from dual);. But, unfortunately, I got an error: ORA-00905: missing keyword.

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.

• User1974: For those of us that are unfamiliar, it looks like we can see a listing of the functions by navigating to SDE.PACKAGES in an Oracle client: i.sstatic.net/JmvJW.png