• Blog posts

    Bio

    By David Crowther

    GeoServer___SQL_and_Oracle_Plugins_-_1.JPG

    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/

    GeoServer___SQL_and_Oracle_Plugins_-_2.JPG

    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.

    GeoServer___SQL_and_Oracle_Plugins_-_3.JPG

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

    GeoServer___SQL_and_Oracle_Plugins_-_4.JPG

    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:

    GeoServer___SQL_and_Oracle_Plugins_-_5.JPG

    … 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:

    GeoServer___SQL_and_Oracle_Plugins_-_6.JPG

    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/

    GeoServer___SQL_and_Oracle_Plugins_-_7.JPG

    Download the Oracle Plugin and extract the ZIP file:

    GeoServer___SQL_and_Oracle_Plugins_-_8.JPG

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

    GeoServer___SQL_and_Oracle_Plugins_-_9.JPG

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

    GeoServer___SQL_and_Oracle_Plugins_-_10.JPG

    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.

    GeoServer___SQL_and_Oracle_Plugins_-_11.JPG

    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:

    GeoServer___SQL_and_Oracle_Plugins_-_12.JPG

    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

    GeoServer___SQL_and_Oracle_Plugins_-_13.JPG

    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.

    GeoServer___SQL_and_Oracle_Plugins_-_14.JPG

    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.

    GeoServer___SQL_and_Oracle_Plugins_-_15.JPG