Unable to use an Oracle BEFORE or AFTER trigger which reference st_geometry attributes with ArcGIS

Description

When using an Oracle before or after insert trigger on a table with an st_geometry attribute, and the trigger body specification attempts to reference the geometry, and the insert is being performed by ArcGIS; the geometry is NULL.

The following example demonstrates an Oracle before insert trigger that references the geometry being inserted. The trigger specification obtains the st_geometry attribute being inserted (an intersection point) to validate if the geometry intersects another st_geometry attribute (centerlines). If there is no intersection, the trigger raises an error indicating that the insert is invalid and does not pass the required topological relation, which is that all intersection points must intersect a street centerline.

Code:
CREATE OR REPLACE TRIGGER intersection_validation 
  BEFORE INSERT ON intersections FOR EACH ROW

  DECLARE
  
    row_cnt  INTEGER;

  BEGIN

    SELECT COUNT(*) INTO row_cnt FROM centerlines 
    WHERE sde.st_intersects(shape,:NEW.shape) = 1;

    IF row_cnt = 0 THEN
      RAISE_APPLICATION_ERROR(-20000, 'The intersection point does not
                                       intersect a street centerline.');
    END IF;

END;
/

Cause

Using Oracle's Call Interface to bind a type's attribute values containing an Oracle LOB, such as the st_geometry points attribute, when performing an insert requires using the Oracle LOB locator. When performing the insert, the LOB locator address is provided by way of the RETURNING clause as part of the insert statement. The OCI application then uses the LOB locator address to set the value for the LOB attribute and Oracle writes the value to the database, but Oracle does not write the LOB attribute value until after performing the initial insert into the table and after any dependent triggers on the table are executed.

The following is an example of the SQL being executed by ArcGIS when inserting a row into a table containing an st_geometry attribute.

Code:
INSERT INTO TB.sewer SEWER (OBJECTID,SEG_ID,SYMBOL,PIPE_SIZE,SLOPE,SHAPE) 
VALUES
 ( :a1, :a2, :a3, :a4, :a5, :st1) RETURNING 
  SEWER.SHAPE.points INTO :points_out

Workaround