1.. _vector.oci: 2 3Oracle Spatial 4============== 5 6.. shortname:: OCI 7 8.. build_dependencies:: OCI library 9 10This driver supports reading and writing data in Oracle Spatial (8.1.7 11or later) Object-Relational format. The Oracle Spatial driver is not 12normally built into OGR, but may be built in on platforms where the 13Oracle client libraries are available. 14 15When opening a database, its name should be specified in the form 16"OCI:userid/password@database_instance:table,table". The list of tables 17is optional. The database_instance portion may be omitted when accessing 18the default local database instance. 19 20If the list of tables is not provided, then all tables appearing in 21ALL_SDO_GEOM_METADATA will be treated by OGR as layers with the table 22names as the layer names. Non-spatial tables or spatial tables not 23listed in the ALL_SDO_GEOM_METADATA table are not accessible unless 24explicitly listed in the datasource name. Even in databases where all 25desired layers are in the ALL_SDO_GEOM_METADATA table, it may be 26desirable to list only the tables to be used as this can substantially 27reduce initialization time in databases with many tables. 28 29If the table has an integer column called OGR_FID it will be used as the 30feature id by OGR (and it will not appear as a regular attribute). When 31loading data into Oracle Spatial OGR will always create the OGR_FID 32field. 33 34Driver capabilities 35------------------- 36 37.. supports_create:: 38 39.. supports_georeferencing:: 40 41SQL Issues 42---------- 43 44By default, the Oracle driver passes SQL statements directly to Oracle 45rather than evaluating them internally when using the ExecuteSQL() call 46on the OGRDataSource, or the -sql command option to ogr2ogr. Attribute 47query expressions are also passed through to Oracle. 48 49As well two special commands are supported via the ExecuteSQL() 50interface. These are "**DELLAYER:<table_name>**" to delete a layer, and 51"**VALLAYER:<table_name>**" to apply the SDO_GEOM.VALIDATE_GEOMETRY() 52check to a layer. Internally these pseudo-commands are translated into 53more complex SQL commands for Oracle. 54 55It is also possible to request the driver to handle SQL commands with 56:ref:`OGR SQL <ogr_sql_dialect>` engine, by passing **"OGRSQL"** 57string to the ExecuteSQL() method, as name of the SQL dialect. 58 59Caveats 60------- 61 62- The type recognition logic is currently somewhat impoverished. No 63 effort is made to preserve real width information for integer and 64 real fields. 65- Various types such as objects, and BLOBs in Oracle will be completely 66 ignored by OGR. 67- Currently the OGR transaction semantics are not properly mapped onto 68 transaction semantics in Oracle. 69- If an attribute called OGR_FID exists in the schema for tables being 70 read, it will be used as the FID. Random (FID based) reads on tables 71 without an identified (and indexed) FID field can be very slow. To 72 force use of a particular field name the OCI_FID configuration 73 variable (i.e. environment variable) can be set to the target field 74 name. 75- Curved geometry types are converted to linestrings or linear rings in 76 six degree segments when reading. The driver has no support for 77 writing curved geometries. 78- There is no support for point cloud (SDO_PC), TIN (SDO_TIN) and 79 annotation text data types in Oracle Spatial. 80- It might be necessary to define the environment variable NLS_LANG to 81 "American_America.UTF8" to avoid issues with floating point numbers 82 being truncated to integer on non-English environments. 83- For developers: when running the driver under the memory error 84 detection tool Valgrind, specifying the database_instance, typically 85 to localhost, or with the TWO_TASK environment variable seems to be 86 compulsory, otherwise "TNS:permission denied" errors will be 87 reported) 88 89Creation Issues 90--------------- 91 92The Oracle Spatial driver does not support creation of new datasets 93(database instances), but it does allow creation of new layers within an 94existing database. 95 96Upon closing the OGRDataSource newly created layers will have a spatial 97index automatically built. At this point the USER_SDO_GEOM_METADATA 98table will also be updated with bounds for the table based on the 99features that have actually been written. One consequence of this is 100that once a layer has been loaded it is generally not possible to load 101additional features outside the original extents without manually 102modifying the DIMINFO information in USER_SDO_GEOM_METADATA and 103rebuilding the spatial index. 104 105Layer Creation Options 106~~~~~~~~~~~~~~~~~~~~~~ 107 108- **OVERWRITE**: This may be "YES" to force an existing layer (=table) 109 of the same desired name to be destroyed before creating the 110 requested layer. The default value is "NO" 111- **TRUNCATE**: This may be "YES" to force the existing table to be 112 reused, but to first truncate all records in the table, preserving 113 indexes or dependencies. The default value is "NO". 114- **LAUNDER**: This may be "YES" to force new fields created on this 115 layer to have their field names "laundered" into a form more 116 compatible with Oracle. This converts to upper case and converts some 117 special characters like "-" and "#" to "_". The default value is 118 "NO". 119- **PRECISION**: This may be "YES" to force new fields created on this 120 layer to try and represent the width and precision information, if 121 available using NUMBER(width,precision) or VARCHAR2(width) types. If 122 "NO" then the types NUMBER, INTEGER and VARCHAR2 will be used 123 instead. The default is "YES". 124- **DIM**: This may be set to 2 or 3 to force the dimension of the 125 created layer. Prior to GDAL 2.2, 3 is used by default. Starting with 126 GDAL 2.2, the dimension of the layer geometry type is used by 127 default. 128- **SPATIAL_INDEX**: This may be set to FALSE to disable creation of a 129 spatial index when a layer load is complete. By default an index is 130 created if any of the layer features have valid geometries. The 131 default is "YES". Note: option was called INDEX in releases before 132 GDAL 2 133- **INDEX_PARAMETERS**: This may be set to pass creation parameters 134 when the spatial index is created. For instance setting 135 INDEX_PARAMETERS to SDO_RTR_PCTFREE=0 would cause the rtree index to 136 be created without any empty space. By default no parameters are 137 passed causing a default R-Tree spatial index to be created. 138- **ADD_LAYER_GTYPE**\ =YES/NO: This may be 139 set to NO to disable the constraints on the geometry type in the 140 spatial index, through the layer_gtype keyword in the PARAMETERS 141 clause of the CREATE INDEX. Layers of type MultiPoint, 142 MultiLineString or MultiPolygon will also accept single geometry type 143 (Point, LineString, Polygon). Defaults to YES. 144- **DIMINFO_X**: This may be set to xmin,xmax,xres values to control 145 the X dimension info written into the USER_SDO_GEOM_METADATA table. 146 By default extents are collected from the actual data written. 147- **DIMINFO_Y**: This may be set to ymin,ymax,yres values to control 148 the Y dimension info written into the USER_SDO_GEOM_METADATA table. 149 By default extents are collected from the actual data written. 150- **DIMINFO_Z**: This may be set to zmin,zmax,zres values to control 151 the Z dimension info written into the USER_SDO_GEOM_METADATA table. 152 By default fixed values of -100000,100000,0.002 are used for layers 153 with a third dimension. 154- **SRID**: By default this driver will attempt to find an existing row 155 in the MDSYS.CS_SRS table with a well known text coordinate system 156 exactly matching the one for this dataset. If one is not found, a new 157 row will be added to this table. The SRID creation option allows the 158 user to force use of an existing Oracle SRID item even it if does not 159 exactly match the WKT the driver expects. 160- **MULTI_LOAD**: If enabled new features will be created in groups of 161 100 per SQL INSERT command, instead of each feature being a separate 162 INSERT command. Having this enabled is the fastest way to load data 163 quickly. Multi-load mode is enabled by default, and may be forced off 164 for existing layers or for new layers by setting to NO. The number of 165 rows in each group is defined by MULTI_LOAD_COUNT. To load one row at 166 a time, set MULTI_LOAD to NO. 167- **MULTI_LOAD_COUNT**: Define the number of features on each ARRAY 168 INSERT command, instead of the default 100 item defined by 169 MULTI_LOAD. Since each array insert will commit a transaction, this 170 options shouldn't be combined with ogr2ogr "-gt N". Use "-gt 171 unlimited" preferably when using MULTI_LOAD_COUNT. The default is 172 100. If neither MULTI_LOAD nor MULTI_LOAD_COUNT are specified, then 173 the loading happens in groups of 100 rows. 174- **FIRST_ID**: Define the first numeric value of the id column on the 175 first rows. It's also work as a open option when used to append or 176 update an existing dataset. 177- **NO_LOGGING**: Define that the table and the geometry will be create 178 with nologging attributes. 179- **LOADER_FILE**: If this option is set, all feature information will 180 be written to a file suitable for use with SQL*Loader instead of 181 inserted directly in the database. The layer itself is still created 182 in the database immediately. The SQL*Loader support is experimental, 183 and generally MULTI_LOAD enabled mode should be used instead when 184 trying for optimal load performance. 185- **GEOMETRY_NAME**: By default OGR creates new tables with the 186 geometry column named ORA_GEOMETRY. If you wish to use a different 187 name, it can be supplied with the GEOMETRY_NAME layer creation 188 option. 189 190Layer Open Options 191~~~~~~~~~~~~~~~~~~ 192 193- **FIRST_ID**: See Layer Create Options comments on FIRST_ID. 194- **MULTI_LOAD**: See Layer Create Options comments on MULTI_LOAD. 195- **MULTI_LOAD_COUNT**: See Layer Create Options comments on 196 MULTI_LOAD_COUNT. 197- **WORKSPACE**: Define what user workspace to use. 198 199Example 200~~~~~~~ 201 202Simple translation of a shapefile into Oracle. The table 'ABC' will be 203created with the features from abc.shp and attributes from abc.dbf. 204 205:: 206 207 % ogr2ogr -f OCI OCI:warmerda/password@gdal800.dreadfest.com abc.shp 208 209This second example loads a political boundaries layer from VPF (via the 210:ref:`OGDI driver <vector.ogdi>`), and renames the layer from the cryptic 211OGDI layer name to something more sensible. If an existing table of the 212desired name exists it is overwritten. 213 214:: 215 216 % ogr2ogr -f OCI OCI:warmerda/password \ 217 gltp:/vrf/usr4/mpp1/v0eur/vmaplv0/eurnasia \ 218 -lco OVERWRITE=yes -nln polbndl_bnd 'polbndl@bnd(*)_line' 219 220This example shows using ogrinfo to evaluate an SQL query statement 221within Oracle. More sophisticated Oracle Spatial specific queries may 222also be used via the -sql commandline switch to ogrinfo. 223 224:: 225 226 ogrinfo -ro OCI:warmerda/password -sql "SELECT pop_1994 from canada where province_name = 'Alberta'" 227 228Credits 229~~~~~~~ 230 231I would like to thank `SRC, LLC <http://www.extendthereach.com/>`__ for 232its financial support of the development of this driver. 233