Java

XE

For local development you should setup Oracle XE (eXpress Edition). You need an oracle account, then you can download it from here.

The most comfortable way to run it as needed is using docker. You can build your own docker image from the downloaded RPM using the instructions and dockerfile from oracle. The following commands will build and start Oracle XE 18.4.0 on your machine:

git clone https://github.com/oracle/docker-images.git
cd docker-images/OracleDatabase/SingleInstance/dockerfiles
./buildDockerImage.sh -x -v 18.4.0
docker run -d -p 1521:1521 --name=oracle-xe --restart=always -e ORACLE_PWD=«my-sys-pwd» oracle/database:18.4.0-xe

Please note that the buildDockerImage.sh will take a long time. Further after docker run has passed you need to give time for your new container to startup and setup the Oracle XE DB. So be patient and give it some time. (In case the build of the docker-image fails reproducibly and you want to give up with the Dockerfiles from Oracle you can also try this inofficial docker-oracle-xe solution. However, this is not recommended and may lead to other problems.).

Starting with XE 18c you need to be aware that oracle introduced a multi-tenant architecture. Hence xe refers to the root CDB while you typically want to connect to the PDB (pluggable database) and XE ships with exactly one of this called xepdb1. To connect to your local XE database you need to use xepdb1 as the Service Name (typically in SQL Developer). The hostname should be localhost and the port is by default 1521 if you did not remap it with docker to something else. In order to create schema users, use sys as Username and change Role to SYSDBA.

Hint: If you happen to end up connected to xe instead of xepdb1 in some case (e.g. in sqlplus), you may switch using this statement:

ALTER SESSION SET CONTAINER = XEPDB1;

The JDBC URL for your Oracle XE Database is:

jdbc:oracle:thin:@//localhost:1521/xepdb1

To locally connect as sysdba without password use the following command (connect / as sysdba is not working anymore):

sqlplus sys/Oracle18@localhost/XE as sysdba

Driver

The oracle JDBC driver is available in maven central. Oracle JDBC drivers usually are backward and forward compatible so you should be able to use an older driver with a newer Oracle DB, etc. Your dependency for the oracle driver should look as follows:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc10</artifactId>
  <version>${oracle.driver.version}</version>
</dependency>

For the most recent Oracle DB 19 the property oracle.driver.version should be 19.8.0.0. The number in the artifactId correlates to the minimum Java Version so for Java8 artifactId should be ojdbc8 instead. It is fine to use ojdbc10 with Java11 or higher.

Pooling

In order to boost performance JDBC connections should be pooled and reused. If you are using Oracle RDBMS and do not plan to change that you can use the Oracle specific connection pool "Universal Connection Pool (UCP)" that is perfectly integrated with the Oracle driver. According to the documentation, UCP can even be used to manage third party data sources. Like the JDBC driver also the UCP is available in maven central. The dependency should look like this:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ucp</artifactId>
  <version>${oracle.ucp.version}</version>
</dependency>

with property oracle.ucp.version analogue to oracle.driver.version.

Configuration is done via application.properties like this (example):

#Oracle UCP
# Datasource for accessing the database
spring.datasource.url=jdbc:oracle:thin:@192.168.58.2:1521:xe
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.datasource.user=MyUser
spring.datasource.password=ThisIsMyPassword
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.schema=MySchema

spring.datasource.type=oracle.ucp.jdbc.PoolDataSourceImpl
spring.datasource.factory=oracle.ucp.jdbc.PoolDataSourceFactory
spring.datasource.factory-method=getPoolDataSource
spring.datasource.connectionFactoryClassName=oracle.jdbc.pool.OracleDataSource
spring.datasource.validateConnectionOnBorrow=true
spring.datasource.connectionPoolName=MyPool
spring.datasource.jmx-enabled=true

# Optional: Set the log level to INTERNAL_ERROR, SEVERE, WARNING, INFO, CONFIG, FINE, TRACE_10, FINER, TRACE_20, TRACE_30, or FINEST
# logging.level.oracle.ucp=INTERNAL_ERROR
# Optional: activate tracing
# logging.level.oracle.ucp.jdbc.oracle.OracleUniversalPooledConnection=TRACE

#Optional: Configures pool size manually
#spring.datasource.minPoolSize=10
#spring.datasource.maxPoolSize=40
#spring.datasource.initialPoolSize=20

Resources: FAQ, developer’s guide, Java API Reference. For an in-depth discussion on how to use JDBC and UCP, see the Oracle documentation Connection Management Strategies for Java Applications using JDBC and UCP.

Note: there is a bug in UCP 12.1.0.2 that results in the creation of thousands of java.lang.Timer threads over hours or days of system uptime (see article on stackoverflow). Also, Oracle has a strange bug fixing / patching policy: instead of producing a fixed version 12.1.0.3 or 12.1.0.2.x, Oracle publishes collections of *.class files that must be manually patched into the ucp.jar! Therefore, use the newest versions only.

Messaging

In case you want to do messaging based on JMS you might consider the Oracle JMS also called Oracle Streams Advanced Queuing, or Oracle Advanced Queuing, or OAQ or AQ for short. OAQ is a JMS provider based on the Oracle RDBMS and included in the DB product for no extra fee. OAQ has some features that exceed the JMS standard like a retention time (i.e. a built-in backup mechanism that allows to make messages "unread" within a configurable period of time so that these messages do not have to be resent by the sending application). Also, OAQ messages are stored in relational tables so they can easily be observed by a test driver in a system test scenario. Capgemini has used the Spring Data JDBC Extension in order to process OAQ messages within the same technical transaction as the resulting Oracle RDBMS data changes without using 2PC and an XA-compliant transaction manager - which is not available out of the box in Tomcat. This is possible only due to the fact that OAQ queues and RDBMS tables actually reside in the same database. However, this is higher magic and should only be tried if high transaction rates must be achieved by avoiding 2PC.

General Notes on the use of Oracle products

Oracle sells commercial products and receives licence fees for them. This includes access to a support organization. Therefore, at an early stage of your project, prepare for contacting oracle support in case of technical problems. You will need the Oracle support ID of your customer [i.e. the legal entity who pays the licence fee and runs the RDBMS] and your customer must grant you permission to use it in a service request - it is not legal to use a your own support ID in a customer-related project. Your customer pays for that service anyway, so use it in case of a problem!

Software components like the JDBC driver or the UCP may be available without a registration or fee but they are protected by the Oracle Technology Network (OTN) License Agreement. The most important aspect of this licence agreement is the fact that an IT service provider is not allowed to simply download the Oracle software component, bundle it in a software artefact and deliver it to the customer. Instead, the Oracle software component must be (from a legal point of view) provided by the owner of the Oracle DB licence (i.e. your customer). This can be achieved in two ways: Advise your customer to install the Oracle software component in the application server as a library that can be used by your custom built system. Or, in cases where this is not feasible, e.g. in a OpenShift environment where the IT service provider delivers complete Docker images, you must advise your customer to (legally, i.e. documented in a written form) provide the Oracle software component to you, i.e. you don’t download the software component from the Oracle site but receive it from your customer.

Fix for TNS-Listener issues

When switching networks (e.g. due to VPN) you might end up that your local Oracle XE stopps working with this error:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

While a reboot resolves this problem, it is a huge pain to reboot every time this error occurs as this wastes a lot of time. Therefore we suggest the following fix:

  • Go to your oracle installation and open the folder product/«version»/dbhomeXE/network/admin.

  • Edit the file listener.ora and change the value of the property HOST from your qualified hostname to localhost (HOST = localhost).

  • Edit the file tnsnames.ora and change the value of the HOST properties (two occurences) from your qualified hostname to localhost (HOST = localhost).

  • Reboot your machine or (on windows) restart the service OracleServiceXE via services.msc.

  • Now this problem should be gone forever and you can continue your work.

On older XE versions until 11g you could run the following SQL (sqlplus / as sysdba @reset_tns_listener.sql):

WHENEVER SQLERROR EXIT;
ALTER SYSTEM SET local_listener = '(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))';
ALTER SYSTEM REGISTER;
EXIT;

↑ Up: Oracle RDBMS | ⌂ Home: devonfw guide | Next: MS-SQL-Server →

Last updated 2023-11-20 10:37:01 UTC