Had a situation where I was given a shape file from ESRI with EPSG 102005 which isn’t in the database.  When importing the shapefile through MapBuilder it of course flamed so I needed to figure out how to add the coordinate system to the database.

Found this on MOS –  Example Adding A New Coordinate System based on the EPSG model introduced in 10gR2 (Doc ID 395171.1) and was able to put together what I needed to import shapefile and work with it using MapBuilder and MapViewer Editor.

Here’s the SQL I used to insert new definitions into database –

/*
PROJCS[
"USA_Contiguous_Equidistant_Conic",
GEOGCS["GCS_North_American_1983",
DATUM[
"North_American_Datum_1983",
SPHEROID["GRS_1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]],
PROJECTION["Equidistant_Conic"],
PARAMETER["False_Easting",0],
PARAMETER["False_Northing",0],
PARAMETER["Longitude_Of_Center",-96],
PARAMETER["Standard_Parallel_1",33],
PARAMETER["Standard_Parallel_2",45],
PARAMETER["Latitude_Of_Center",39],
UNIT["Meter",1],
AUTHORITY["EPSG","102005"]]
*/

insert into MDSYS.SDO_COORD_OPS ( 
COORD_OP_ID, 
COORD_OP_NAME, 
COORD_OP_TYPE, 
SOURCE_SRID, 
TARGET_SRID, 
COORD_TFM_VERSION, 
COORD_OP_VARIANT, 
COORD_OP_METHOD_ID, 
UOM_ID_SOURCE_OFFSETS, 
UOM_ID_TARGET_OFFSETS, 
INFORMATION_SOURCE, 
DATA_SOURCE, 
SHOW_OPERATION, 
IS_LEGACY, 
LEGACY_CODE, 
REVERSE_OP, 
IS_IMPLEMENTED_FORWARD, 
IS_IMPLEMENTED_REVERSE) 
VALUES ( 
102005000, 
'USA_Contiguous_Equidistant_Conic', 
'CONVERSION', 
NULL, 
NULL, 
NULL, 
NULL, 
9802, 
NULL, 
NULL, 
NULL, 
NULL, 
1, 
'FALSE', 
NULL, 
1, 
1, 
1);

//8821: Latitude_Of_Origin
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8821, 
39.0, 
NULL, 
9102);

//8822: Central_Meridian
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8822, 
-96.0, 
NULL, 
9102);

//8823: Standard_Parallel_1
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8823, 
33.0, 
NULL, 
9102);

//8824: Standard_Parallel_2
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8824, 
45.0, 
NULL, 
9102);

//8826: False_Easting
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8826, 
0.0, 
NULL, 
9001);

//8827: False_Northing
insert into MDSYS.SDO_COORD_OP_PARAM_VALS ( 
COORD_OP_ID, 
COORD_OP_METHOD_ID, 
PARAMETER_ID, 
PARAMETER_VALUE, 
PARAM_VALUE_FILE_REF, 
UOM_ID) 
VALUES ( 
102005000, 
9802, 
8827, 
0.0, 
NULL, 
9001); 

// create the projected CRS
insert into MDSYS.SDO_COORD_REF_SYSTEM ( 
SRID, 
COORD_REF_SYS_NAME, 
COORD_REF_SYS_KIND, 
COORD_SYS_ID, 
DATUM_ID, 
SOURCE_GEOG_SRID, 
PROJECTION_CONV_ID, 
CMPD_HORIZ_SRID, 
CMPD_VERT_SRID, 
INFORMATION_SOURCE, 
DATA_SOURCE, 
IS_LEGACY, 
LEGACY_CODE, 
LEGACY_WKTEXT, 
LEGACY_CS_BOUNDS, 
GEOG_CRS_DATUM_ID) 
VALUES ( 
102005000, 
'USA_Contiguous_Equidistant_Conic',  
'PROJECTED', 
4530, 
NULL, 
4269, 
102005000, 
NULL, 
NULL, 
NULL, 
NULL, 
'FALSE', 
NULL, 
NULL, 
NULL, 
6269); 

//Check your work
select srid, wktext from cs_srs where srid = 102005000;

select mdsys.sdo_cs.transform( 
SDO_GEOMETRY( 2001, 4269, SDO_POINT_TYPE( -79.5, 36, NULL), NULL, NULL), 102005000) 
from dual;