Adding EPSG:3857 / 900913 to Oracle's mdsys.cs_srs

Question

I am working with an Oracle Standard database at version 11.2.0.1.0. I also have access to XE version 11.2.0.2.0.

The later version has both SRIDs 900913 (Google Mercator) and 3857 (official Web Mercator), but the earlier version does not. I read that SRSs are easily inserted into Oracle's mdsys.cs_srs table, so I selected the values for 900913 from the later DB and inserted into the earlier DB.

The insert reported success, but no matter how many times I update it the WKTEXT3D field in 11.2.0.1.0 remains null. If I try a coordinate transformation from a single point (both 2D and 3D) in 4326 (WGS84 Lat/Lon) to 900913 I am told "Parsing error on source or destination coordinate system WKT, or incompatible coordinate systems", then "Check the validity of the WKT for table entries, and check if the requested transformation is valid".

I can use the same transformation query (below) in 11.2.0.2.0 without issue.

Does anyone know

1. Why the WKT extracted from 11.2.0.2.0 doesn't seem valid in 11.2.0.1.0?
2. How I can adjust it to make it valid?
3. If there is some fundamental issue in 11.2.0.1.0 that makes this transformation invalid?

Any input on these issues verrrrrry much appreciated.

Edit: Following MelitaKennedy's suggestion I started trying to track down the 'Popular Visualisation Pseudo-Mercator' projection in my 11.2.0.2.0 DB and I think I found it in MDSYS.SDO_COORD_OPS. However, it references a COORD_OP_METHOD_ID (MDSYS.SDO_COORD_OP_METHODS table) which defines several 'methods'. I assume that each of these methods relates to a function within Oracle, so maybe it simply isn't possible to create the entire CRS / Projection package in a pre-11.2.0.2.0 DB?

Transform Query:

  select
    sdo_cs.transform(
      sdo_geometry(
        2001,
        4326,
        null,
        sdo_elem_info_array(1,1,1),
        sdo_ordinate_array(-132.525665, 45.432552)
      ),
      sdo_dim_array(
          sdo_dim_element('LONG', -180.0, 180.0, 0.5),
          sdo_dim_element('LAT', -90, 90, 0.5)
      ),
    900913)
  from
    dual;

900913 Insert:

insert into mdsys.cs_srs (cs_name, srid, auth_srid, wktext, wktext3d)
  values (
    'Google Mercator',
    900913,
    900913,
    'PROJCS["Google Mercator", GEOGCS [ "WGS 84", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137.0, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.0174532925199433]], PROJECTION ["Popular Visualisation Pseudo-Mercator"], PARAMETER ["Latitude_of_origin", 0.0], PARAMETER ["Central_Meridian", 0.0], PARAMETER ["False_Easting", 0.0], PARAMETER ["False_Northing", 0.0], UNIT ["Meter", 1.0]]',
    'PROJCS["Google Mercator", GEOGCS["WGS 84", DATUM["World Geodetic System 1984", SPHEROID["WGS 84", 6378137.0, 298.257223563, AUTHORITY["EPSG", "7030"]], TOWGS84[0, 0, 0, 0, 0, 0, 0], AUTHORITY["EPSG", "6326"]], PRIMEM["Greenwich", 0.000000, AUTHORITY["EPSG","8901"]], UNIT["degree (supplier to define representation)", 0.0174532925199433, AUTHORITY["EPSG", "9122"]], AXIS["Lat", NORTH], AXIS["Long", EAST], AUTHORITY["EPSG", "4326"]], PROJECTION ["Popular Visualisation Pseudo-Mercator"], PARAMETER ["Latitude_of_origin", 0.0], PARAMETER ["Central_Meridian", 0.0], PARAMETER ["False_Easting", 0.0], PARAMETER ["False_Northing", 0.0], UNIT["metre", 1.0, AUTHORITY["EPSG", "9001"]], AXIS["X", EAST], AXIS["Y", NORTH], AUTHORITY["", "900913"]]'
  );

Answer 1

check this out:

I collected the information to include the projection EPSG:3857 in Oracle Spatial

https://www.inf.unibz.it/dis/wiki/doku.php?id=students:minnerebner:oracle:addingsrid