Oracle

Note

GeoServer does not come built-in with support for Oracle; it must be installed through an extension. Proceed to Installing the Oracle extension for installation details.

Oracle Spatial and Locator are the spatial components of Oracle. Locator is provided with all Oracle versions, but has limited spatial functions. Spatial is Oracle's full-featured spatial offering, but requires a specific license to use.

Installing the Oracle extension

Visit the website download page, locate your release, and download: geoserver-2.24.2-oracle-plugin.zip (nightly geoserver-2.24-SNAPSHOT-oracle-plugin.zip)

Warning

Ensure to match plugin (example 2.24.2 above) version to the version of the GeoServer instance.
Extract the contents of the archive into the WEB-INF/lib directory of the GeoServer installation.

Adding an Oracle datastore

Once the extension is properly installed Oracle appears as an option in the Vector Data Sources list when creating a new data store.

Oracle in the list of data sources

Configuring an Oracle datastore

Configuring an Oracle datastore


Option	Description
`host`	The Oracle server host name or IP address.
`port`	The port on which the Oracle server is accepting connections (often this is port 1521).
`database`	The name of the database to connect to. By default this is interpreted as a SID name. To connect to a Service, prefix the name with a `/`.
`schema`	The database schema to access tables from. Setting this value greatly increases the speed at which the data store displays its publishable tables and views, so it is advisable to set this.
`user`	The name of the user to use when connecting to the database.
`password`	The password to use when connecting to the database. Leave blank for no password.
`max connections` `min connections` `fetch size` `Connection timeout` `validate connections`	Connection pool configuration parameters. See Database Connection Pooling for details.
`Loose bbox`	Controls how bounding box filters are made against geometries in the database. See the Using loose bounding box section below.
`Metadata bbox`	Flag controlling the use of MDSYS.USER_SDO_GEOM_METADATA or MDSYS.ALL_SDO_GEOM_METADATA table for bounding box calculations, this brings a better performance if the views access is fast and the bounds are configured right in the tables default is false
`Get remarks`	Boolean flag specifies whether REMARKS metadata will be returned.

Connecting to an Oracle cluster

In order to connect to an Oracle RAC one can use an almost full JDBC url as the database, provided it starts with ( it will be used verbatim and options "host" and "port" will be ignored. Here is an example "database" value used to connect to an Oracle RAC:

(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service)))

More information about this syntax can be found in the Oracle documentation.

Connecting to a SID or a Service

Recent versions of Oracle support connecting to a database via either a SID name or a Service name. A SID connection descriptor has the form: host:port:database, while a Service connection descriptor has the format host:port/database. GeoServer uses the SID form by default. To connect via a Service, prefix the database name configuration entry with a /.

Connecting to database through LDAP

For instance if you want to establish a connection with the jdbc thin driver through LDAP, you can use following connect string for the input field database ldap://[host]:[Port]/[db],cn=OracleContext,dc=[oracle_ldap_context].

If you are using referrals, enable it by placing a jndi.properties file in geoserver's CLASSPATH, which is in geoserver/WEB-INF/classes. This property file contains:

java.naming.referral=follow

Using loose bounding box

When the Loose bbox option is set, only the bounding box of database geometries is used in spatial queries. This results in a significant performance gain. The downside is that some geometries may be reported as intersecting a BBOX when they actually do not.

If the primary use of the database is through the Web Map Service (WMS) this flag can be set safely, since querying more geometries does not have any visible effect. However, if using the Web Feature Service (WFS) and making use of BBOX filtering capabilities, this flag should not be set.

Using the geometry metadata table

The Oracle data store by default looks at the MDSYS.USER_SDO* and MDSYS.ALL_SDO* views to determine the geometry type and native SRID of each geometry column. Those views are automatically populated with information about the geometry columns stored in tables that the current user owns (for the MDSYS.USER_SDO* views) or can otherwise access (for the MDSYS.ALL_SDO* views).

There are a few issues with this strategy:

if the connection pool user cannot access the tables (because impersonation is used) the MDSYS views will be empty, making it impossible to determine both the geometry type and the native SRID
the geometry type can be specified only while building the spatial indexes, as an index constraint. However such information is often not included when creating the indexes
the views are populated dynamically based on the current user. If the database has thousands of tables and users the views can become very slow

Starting with GeoServer 2.1.4 the administrator can address the above issues by manually creating a geometry metadata table describing each geometry column. Its presence is indicated via the Oracle datastore connection parameter named Geometry metadata table (which may be a simple table name or a schema-qualified one). The table has the following structure (the table name is flexible, just specify the one chosen in the data store connection parameter):

CREATE TABLE GEOMETRY_COLUMNS(
   F_TABLE_SCHEMA VARCHAR(30) NOT NULL, 
   F_TABLE_NAME VARCHAR(30) NOT NULL, 
   F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL, 
   COORD_DIMENSION INTEGER, 
   SRID INTEGER NOT NULL, 
   TYPE VARCHAR(30) NOT NULL,
   UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
   CHECK(TYPE IN ('POINT','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINE', 'MULTIPOLYGON', 'GEOMETRY') ));

When the table is present the store first searches it for information about each geometry column to be classified, and falls back on the MDSYS views only if the table does not contain any information.

Configuring an Oracle database with JNDI

See Setting up a JNDI connection pool with Tomcat for a guide on setting up an Oracle connection using JNDI.