GeoServer – SQL and Oracle Plugins

By David Crowther

Question:

How can you connect GeoServer to a SQL or an Oracle Database?

Answer:

Natively GeoServer does not provide the options to connect to SQL or Oracle Databases. You will need to install plugins for your GeoServer version to allow those connections.

In a previous White Paper we explored how to install the SQL Server plugin for older versions of GeoServer, enabling GeoServer to read spatial data from a SQL Server Database:

https://www.cadlinecommunity.co.uk/hc/en-us/articles/115005802929-GeoServer-SQL-Server-Extension-Installation

However that White Paper has now been superseded and the advice below details how to connect later versions of GeoServer to SQL and Oracle.

SQL Plugin:

Once GeoServer is installed and running, you can visit the GeoServer download pages to find the relevant SQL Plugin for your version of GeoServer:

http://geoserver.org/release/stable/

Download the SQL Server Plugin and extract the ZIP file. Previously you needed to download some additional drivers from the Microsoft website, unzip GZ/TAR files and copy the relevant .JAR files to certain folders in your GeoServer install.

However you can now simply use the one .JAR file that is provided in the GeoServer SQL plugin download.

Step 1 – COPY the .JAR file to your GeoServer install /WEB-INF/LIB/ folder:

Step 2 – Shutdown GeoServer and then Restart GeoServer. When you then next login and choose Add Data Store you will now have options for SQL Server:

… follow the steps in the previous White Paper - https://www.cadlinecommunity.co.uk/hc/en-us/articles/115005802929-GeoServer-SQL-Server-Extension-Installation

…. as this provides details on the Connection Parameters for a new SQL Data Store:

Oracle Plugin:

Once GeoServer is installed and running, you can visit the GeoServer download pages to find the relevant Oracle Plugin for your version of GeoServer:

http://geoserver.org/release/stable/

Download the Oracle Plugin and extract the ZIP file:

Within the Zip an oracle-readme file provides details of the next steps:

Step 1 – COPY the .JAR file to your GeoServer install /WEB-INF/LIB/ folder:

Step 2 – As per the readme instructions you will need to find the ojdbc.jar file (e.g. ojdbc6.jar) from your Oracle installation and copy that to your Geoserver /WEB-INF/LIB/ folder as well.

Step 3 – Shutdown GeoServer and then Restart GeoServer. When you then next login and choose Add Data Store you will now have options for Oracle:

Step 4 – In the new Vector Data Source details, enter the following important information:

• Workspace – the workspace to store this setting e.g. DynamicMaps
• Data Source Name – a name to identify the new data source
• Host – the server machine that Oracle is installed on
• Port – the PORT that Oracle communicates on. This is usually 1521
• Database – the name of the Oracle Database that contains your spatial data
• Schema – the location in that Oracle Database where your tables are stored
• User – the user credentials of your Oracle login
• Password – the user password of your Oracle login

Once the Oracle Data Store has been created you can now Create a NEW LAYER and connect to the Oracle Instance to choose the Table to Publish.

TIP – for an Oracle Table to correctly display as a WMS the table must have a SPATIAL INDEX. If not then an error will be shown in OpenLayers when you preview it.