Connecting LibreOffice to an Autonomous Database (Shared)

November 25, 2022 | 5 minute read
Text Size 100%:

LibreOffice is a free and open-source productivity suite with origins going back to the commercial Star Office suite of applications. It's a popular and feature rich set of applications for word processing, spreadsheet calculations, slide decks and many more. LibreOffice is very often bundled in Linux distributions making it easy to install the package including spell checkers and other dependencies. Linux distributions focusing on the desktop tend to include more recent versions. Although this article focuses on Linux, the steps should also apply to LibreOffice on Windows (with a few exceptions).

Oracle Autonomous Database on shared Exadata Infrastructure is a fully automated database service that makes it easy for all organizations to develop and deploy application workloads regardless of complexity, scale, or criticality. Autonomous Database’s converged engine supports diverse data types, simplifying application development and deployment from modeling and coding to ETL, database optimization, and data analysis.

Introduction

This article describes how to connect LibreOffice on Linux x86-64 to an mTLS protected Autonomous Database 19c for query processing and data visualisation. If this sounds unfamiliar to you please refer to the reference section at the bottom of the article for more information. The blog entry features Autonomous Database 19c as of October 2022. LibreOffice is deployed on Ubuntu 22.04 in Oracle Cloud Infrastructure (OCI) using a GNOME desktop environment. The OCI network configuration in place allows the cloud VM to access port 1522, a prerequisite for using Autonomous Database protected by Transport Layer Security (TLS). All of the client VM's packages have been updated to the latest and greatest version and it is assumed that LibreOffice was installed from the distribution's package sources. When preparing this article dpkg reported LibreOffice version 7.3.7. Ubuntu's package manager should have installed JDK 11 as part of the LibreOffice installation, if not, please install it using sudo apt-get install openjdk-11-jdk.

Before you can connect LibreOffice to Autonomous Database you need to ensure libreoffice-base is installed. If it is not present, install it using sudo apt-get install libreoffice-base

Installing and Configuring Java DataBase Connectivity Drivers

LibreOffice Base uses JDBC to connect to databases, greatly simplifying the task of configuring an Autonomous Database with a Wallet. Wallets are required when connecting to an Autonomous Database using encrypted network transport in form of Transport Layer Security (TLS). Mutual TLS encryption greatly enhances security.

In the first step head over to https://www.oracle.com/database/technologies/appdev/jdbc.html and download "Zipped JDBC driver (ojdbc10.jar) and Companion Jars". This download option matches the release of the Autonomous Database. Unzip the archive in a convenient location, for example $HOME/java or a common location should multiple users require them. For the purpose of this article the JDBC drivers are located in /opt/java/ojdbc10-full.

Now it's time to start LibreOffice. Once done, navigate to Tools -> Options and select Advanced under the LibreOffice node to enter Java options. Select JDK 11, provided by Ubuntu from the list. Next hit the "class path" button to the right of the JDK selection panel and add the following archives you just unzipped to the list:

  • ojdb10.jar
  • ons.jar
  • osdt_cert.jar
  • osdt_core.jar

Click OK and then apply to make the changes permanent. LibreOffice must be restarted for the changes to take effect.

Configuring the Autonomous Database as a Data Source

With the class path for JDK 11 set it's time to configure the data source. In the first step download the Wallet for your Autonomous Database and transfer it to the client VM. The article assumes that the contents of the Wallet file was unzipped to ${HOME}/tns.

Start the LibreOffice launcher again and click on "Create: base database" to create a connection to your data source.

The Database Wizard guides you through the configuration. On the "select database" secren tick the radio box labelled "connect to an existing database". From the drop down select JDBC - this is important! Please don't select "Oracle JDBC": the Oracle JDBC wizard doesn't allow you to set an EZConnect String.

The next screen prompts you for the JDBC Connection String. The easiest way to connect to the Autonomous Database is to provide the EZConnect string. It takes the following form:

jdbc:oracle:thin:@serviceName?TNS_ADMIN=/path/to/tns/admin

Simply substitute serviceName with your connection name, for example ORCL_medium where ORCL is the name of your Autonomous Database. The suffix (medium) indicates the resource consumer class. More details about connecting to the Autonomous Database can be found in the reference section below.

It's important not to forget to append the location to the unzipped Wallet files. In this example the unzipped wallet resides in /home/someUser/tns so the entire EZ connect string reads

jdbc:oracle:thin:@ORCL_medium?TNS_ADMIN=/home/someUser/tns

Note that you don't have to enter "jdbc:" as this is pre-filled for you in the wizard interface.

The JDBC driver class should be set to "oracle.jdbc.driver.OracleDriver". You can confirm if the JDBC configuration worked by hitting the "test class" button.

Click next to advance to the next screen where you enter your (database) username. Make sure to tick the "password required" checkbox but refrain from testing the connection as this potentially runs into errors, corrupting the configuration you entered so far. Click next to proceed to the final screen.

Select "register the database for me" so it appears as a data source later on in LibreOffice calc. For now deselect "create tables using the table wizard". You are now prompted to save the database connection information in a file.

Verify the Connection

If you didn't deselect "open the database for editing" in the previous screen you are taken to the database where you are prompted to enter username and password. Otherwise open the newly created file using File -> Open.

On the left-hand side you'll see options to create tables, queries, forms, and reports. The easiest way to ensure everything works as expected is to select Queries. You will be prompted to supply username and password before the connection is established. If you entered everything correctly you are now connected to the Autonomous Database.

an image of the query editor in LibreOffice base

Summary

Connecting a mTLS protected Autonomous Database on Shared Exadata Infrastructure to LibreOffice is quite simple provided you use the "plain JDBC" driver that allows you to pass an EZConnect string referencing the Wallet location.

References

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

Four Facets of Database Connectivity for Java Applications

Nirmala Sundarappa | 5 min read

Next Post


Making Eclipse MicroProfile Long Running Actions Easy with MicroTx

Todd Little | 7 min read