I have a PL/SQL trigger:

1    create or replace trigger "projects_to_meas_trig" before update of asset_id 
2    on projects
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;
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?


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:

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.


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.


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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.