Create JDBC Data Source or JDBC URL database connection to an Oracle Pluggable Database

Read Time:2 Minute, 20 Second

JDBC URLs used in JDBC database connections in IDEs like JDeveloper and in JDBC Data Source definitions in Java EE servers such as WebLogic Server will typically use the Oracle Database SID as the name of the database.

As Tim Hall writes, the syntax for the JDBC URL is as follows:

# Syntax
jdbc:oracle:thin:@[HOST][:PORT]:SID
jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

where HOST is the IP address or the hostname known in a DNS or local hosts file for the server on which the database is running, port is the SQL*Net port and SID and SERVICE are self explanatory. So if I know the name of the database service for the PDB, I can create the JDBC connection to it.

After creating a DBaaS instance in the Oracle Public Cloud, an 12.1.0.2 Oracle Database instance with two Pluggable Databases, I would like to create a JDBC Data Source from an JCS instance in the same identity domain – using the DBaaS instance name MyJCSDB for the hostname – and also a JDBC Connection from SQL Developer or JDeveloper running on my local laptop. The Data Source and the JDBC Connection should both connect with the HR schema in the DEMOS PDB in this database instance (with SID equal to ORCL). The situation I want to achieve is shown in the next figure.

The SID for the database is ORCL, the hostname is myjcsdb, the port number is 1521 and the service name for the DEMOS PDB is demos.lucasjellema.oraclecloud.internal.

In DBaaS Monitor, I can find the JDBC URL for my DEMOS PDB (from Database | Manage | select Connection Details in hamburger menu for PDB :

The Connection Details are shown:

It is equal to jdbc:oracle:thin:@//myjcsdb:1521/demos.lucasjellema.oraclecloud.internal – at least when you connect from within the identity domain in the Oracle Public Cloud where the hostname myjcsdb is known. For external parties, the URL should be: jdbc:oracle:thin:@//<public IP address>:1521/demos.lucasjellema.oraclecloud.internal.

The SID is not used at all. The service name is – and for the DBaaS instance it is composed as: <PDB NAME>.<identitydomain>.oraclecloud.internal. You can just insert your PDB name and your identity domain and you are in business.

Creation of the JDBC Data Source on the JCS instance now looks like this (in the dialog in FMW Control):

And the Database Connection is configured like this in JDeveloper and SQL Developer – using the public IP address instead of the hostname:

Resources

Tim Hall’s Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1) – https://oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1#jdbc-connections-to-pdbs

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

lucas.jellema@amis.nl

https://www.amis.nl/