Insert a NULL or empty st_geometry attribute in Oracle

Summary

Instructions provided describe how to use SQL to insert NULL or empty st_geometry attributes in Oracle.

Procedure

1. Create a table with two attributes, a varchar, and st_geometry attributes.
   
   

   Code:
   SQL> CREATE TABLE geom_test 
     2  (geom_description  VARCHAR2(64),
     3   shape             sde.st_geometry);
   
   Table created.

   
   
2. Insert four rows: the first row with a NULL geometry, the second and third rows with an empty geometry using two methods, and the fourth row with a valid geometry (a point).
   
   

   Code:
   SQL> INSERT INTO geom_test VALUES
     2  ('A NULL geometry',NULL);
   
   1 row created.
   
   SQL> INSERT INTO geom_test VALUES
     2  ('An empty geometry',
     3  sde.st_geometry('point empty',0));
   
   1 row created.
   
   SQL> INSERT INTO geom_test VALUES
     2  ('An empty geometry',
     3  sde.st_geometry(0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL));
   
   1 row created.
   
   SQL> INSERT INTO geom_test VALUES
     2  ('A geometry', 
     3  sde.st_geometry('point (10 10)',0));
   
   1 row created.

   
   
3. Validate the results of the rows inserted by selecting the count of the number of rows that meet each condition.
   
   

   Code:
   SQL> SELECT COUNT(*)
     2  FROM geom_test
     3  WHERE shape IS NULL;
   
     COUNT(*)
   ----------
            1
   
   SQL> SELECT COUNT(*)
     2  FROM geom_test
     3  WHERE sde.st_isempty(shape) = 1;
   
     COUNT(*)
   ----------
            2
   
   SQL> SELECT COUNT(*)
     2  FROM geom_test
     3  WHERE sde.st_isempty(shape) = 0;
   
     COUNT(*)
   ----------
            1