SRID 0 ↔ NULL mismatch between NTS and Oracle causes geometry read/write failures

[indrajitjadeja]

Description

NetTopologySuite.IO.Oracle does not normalize SRID semantics between
NetTopologySuite and Oracle Spatial.

• NetTopologySuite: SRID = 0 → unknown / unset
• Oracle Spatial: SDO_SRID = NULL → unknown / unset
  SDO_SRID = 0 is invalid unless registered in MDSYS.CS_SRS

Because of this mismatch:

1. Write path failure
   OracleGeometryWriter persists SRID = 0 as SDO_SRID = 0.
   Oracle rejects this during spatial validation.

2. Read path failure
   OracleGeometryReader assumes SDO_SRID is non-null and throws when it encounters NULL.

Both scenarios fail even though “unknown SRID” is valid in both systems.

---

Reproduction – Oracle SQL -- Validation Failure (SRID = 0)

CREATE TABLE test_geom (
    id   NUMBER PRIMARY KEY,
    geom MDSYS.SDO_GEOMETRY
);

INSERT INTO test_geom (id, geom)
VALUES (
    1,
    MDSYS.SDO_GEOMETRY(
        2001,
        0,
        MDSYS.SDO_POINT_TYPE(1, 1, NULL),
        NULL,
        NULL
    )
);

COMMIT;

-- Validation using SDO_GEOM
SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom, 0.005)
FROM test_geom;

Result

ORA-13199: SRID 0 does not exist

SELECT SDO_UTIL.TO_WKTGEOMETRY(geom)
FROM test_geom;

Result

ORA-13199: SRID 0 does not exist

---

Reproduction – Oracle SQL -- Valid Case (SRID = NULL)

DELETE FROM test_geom;

INSERT INTO test_geom (id, geom)
VALUES (
    2,
    MDSYS.SDO_GEOMETRY(
        2001,
        NULL,
        MDSYS.SDO_POINT_TYPE(1, 1, NULL),
        NULL,
        NULL
    )
);

COMMIT;

-- Both functions succeed
SELECT
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom, 0.005),
    SDO_UTIL.TO_WKTGEOMETRY(geom)
FROM test_geom;

Result

TRUE | POINT (1 1)

---

Reproduction - C# -- Write Failure (ODP.NET)

using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

var connectionString = "User Id=...;Password=...;Data Source=...;";

// Create NTS geometry with SRID = 0 (unknown in NTS)
var point = new Point(1, 1);

// Convert to Oracle SDO_GEOMETRY
var writer = new OracleGeometryWriter();
OracleObject oracleGeometry = writer.Write(point);

using var conn = new OracleConnection(connectionString);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = @"
    INSERT INTO test_geom (id, geom)
    VALUES (:id, :geom)";

// id parameter
cmd.Parameters.Add("id", OracleDbType.Int32).Value = 1;

// geom parameter (IMPORTANT PART)
var geomParam = cmd.Parameters.Add("geom", OracleDbType.Object);
geomParam.UdtTypeName = "MDSYS.SDO_GEOMETRY";
geomParam.Value = oracleGeometry;

cmd.ExecuteNonQuery();

-- Validation after insert
SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(geom, 0.005)
FROM test_geom
WHERE id = 1;

Result

ORA-13199: SRID 0 does not exist

---

Reproduction - C# Read Failure (ODP.NET)

using NetTopologySuite.IO;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

using var conn = new OracleConnection(connectionString);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT geom FROM test_geom WHERE id = 2";

using var reader = cmd.ExecuteReader();
reader.Read();

var oracleGeom = reader.GetValue(0);
var ntsReader = new OracleGeometryReader();

// ❌ Throws when SDO_SRID is NULL
var geometry = ntsReader.Read((SdoGeometry)oracleGeom);

Exception

System.InvalidOperationException: Nullable object must have a value.
   at System.Nullable`1.get_Value()
   at NetTopologySuite.IO.OracleGeometryReader.Read(SdoGeometry geom)

---

Root Cause

• OracleGeometryWriter writes SRID = 0 verbatim into SDO_SRID

• OracleGeometryReader accesses SDO_SRID.Value without NULL checks

Oracle treats NULL as the only valid “unknown SRID”.

Expected Behavior

Automatic SRID normalization:

Direction	Value
NTS → Oracle	SRID = 0 → SDO_SRID = NULL
Oracle → NTS	SDO_SRID = NULL → SRID = 0

Proposed Fix

Writer

sdoGeometry.Sdo_Srid = geometry.SRID == 0 ? null : geometry.SRID;

Reader

geometry.SRID = (int)sdoGeometry.Sdo_Srid ?? 0;

---

Environment

• NetTopologySuite.IO.Oracle (latest)
• Oracle Database 19c / 21c / 23ai

[indrajitjadeja]

@FObermaier could you please look into this?

[indrajitjadeja]

@FObermaier Could you please look into this?

[FObermaier]

@indrajitjadeja:
Does https://github.com/NetTopologySuite/NetTopologySuite.IO.Oracle/compare/fix/issue_27 resolve your issue?
Or would you prever a property boolean Treat0asNull { get; set; } to handle this issue?

[indrajitjadeja]

@FObermaier I validated your fix: it resolves the read-path issue (NULL ↔ 0). However, the issue still reproduces on the write path—OracleGeometryWriter is still writing SRID = 0 instead of NULL.

Given that, I’d prefer enabling Treat0asNull so that any SRID value of 0 is treated as NULL on write (i.e., if SRID isn’t provided, or is set to 0, it should be written as NULL).

[indrajitjadeja]

@FObermaier Could you please provide an ETA for the fix?