Use a sde.st_geometry function in a PL/SQL trigger

Question

I have a PL/SQL trigger:

1 create or replace trigger "projects_to_meas_trig" before update of asset_id 
2 on projects
3 
4 for each row
5 declare
6 v_length number;
7 begin
8 if :new.asset_class = 3 then
9 select sde.st_length(shape)
10 into v_length
11 from user1.roads
12 where road_id = :new.asset_id;
13 
14 :new.to_meas := v_length;
15 end if;
16 end;

At line #9, when I try to use the st_length function, I get this error:

Error(4): PLS-00707: unsupported construct or internal error [2603]

Why can't I use the st_geometry function in a PL/SQL trigger?

Answer 1

I needed to "fully qualify the package":

select sde.st_geometry_operators.st_length_f(shape) --<-- Don't forget the _f at the end

Interestingly, just adding .st_geometry also seems to work for st_length(). I discovered this by accident:

 select sde.st_geometry.st_length(shape)

From the documentation (cached):

How To: Leverage st_geometry operators in Oracle's PL/SQL

Because the st_geometry functions are exposed as database operators, and typically consumed by way of SQL statements, leveraging the st_geometry functionality with PL/SQL requires referencing the underlying functions by fully qualifying their appropriate package and function name.

Procedure:

For example, to leverage the sde st_astext operator in PL/SQL, the call must reference the fully qualified package where the function resides. The following line of code demonstrates setting a local variable of type CLOB to the output of the st_astext function.

Code:

local_geometry_wkt := sde.st_geometry_operators.st_astext_f(in_st_geom_feature);

All geometry operators, such as st_astext, st_centroid, st_numgeometries, etc. reside in the st_geometry_operators package. All relational operators, such as st_intersects, st_within, st_contains, etc. reside in the st_relation_operators package.

Note: When referencing any st_* operator as a function, remember to include the _f clause after its name to reference the appropriate function in the package body.

Related:

• Here's a screencap of what the package looks like in an SQL client.

• Functions exposed as database operators — More succinct function calls in PL/SQL