How to connect SQLDeveloper to an edition (EBR) on Autonomous Database (ATP)

On the Oracle Autonomous Database there are some restrictions on what is possible and what not. In generell Edition Based Redefinition (EBR) can be used on the Oracle Cloud Infrastructure. EBR is now part of Oracles Maximum Availability Architecture (MAA). It is possible to enable schemas to use editions, to create editionable objects, editioning views and cross edition triggers.

One thing that doesn’t work on Autonomous Transaction Database (ATP) – and I assume on the other Autonomous DBs as well – is to create a custom service using DBMS_SERVICE. This is not a feature directly needed for EBR. But such a service can be connected to a specific edition. When doing so this service acts as a separate layer for all kind of connects from the application or even from development tools like SQL Developer. This is super helpful. I wrote about this a couple of years ago: https://svenweller.wordpress.com/2015/06/16/working-with-editions-part-2/

For most tools like sql*plus or JDBC there are alternatives to specify an edition at connect time. Unfortunately so not for SQL Developer. Until I found out how to make it work there.

Installation steps

Here are the steps how to workaround that problem. This assumes a recent version of SQL Developer (I think at least 19.x is needed). I tested it with 20.4. More specifically the JDBC driver 18.3 is needed.

  1. Download the wallet zip file with the credentials from your cloud service control
  2. Unzip the wallet. It includes several files, among others the tnsnames.ora that defines the cloud connection strings.
  3. Create a connection in SQL Developer, but instead of a cloud connect to the zip file, use a custom JDBC connection
  4. The connect string needs the name of the alias as defined in the tnsnames.ora of the zip file and two custom parameters TNS_ADMIN and oracle.jdbc.editionName.
    Here is a template for the full string:
    jdbc:oracle:thin:@<tnsalias>?TNS_ADMIN=<path to the unzipped wallet folder> &oracle.jdbc.editionName=<Edition Name>

    If the path is a windows path, then the “\” needs to be doubled (escaped). For example TNS_ADMIN=”C:\\Users\\MyAccount\\Documents\\Tools\\sqldev connections\\Wallet_DEMOEBR”

Additional comments

The TNS_ADMIN parameter is a feature of the 18.3 JDBC thin driver (see What’s New in 19c and 18c JDBC and UCP). Using this parameter the sqlnet.ora file that otherwise defines the location of the wallet is not needed anymore. You also don’t need a TNS_ADMIN environment variable.

The new jdbc driver now has a ojdbc.properties file. It does not work to add oracle.jdbc.editionName to that file. The jdbc properties are needed already before the file is called.

Using the same syntax (&parameter=value) you can add any jdbc property to the connection string.

We can also add custom tns aliases to the tnsnames.ora file in the wallet location. This could make sense, for example if we like to lessen the number of retries in case a connect is not possible.

Several alternative approaches did not work. For example to use the ORA_EDITION environment variable. At least under windows this was not considered as a local environment parameter in combination with cloud connect. It would have been a pretty bad solution anyway, because all connections are then influenced by it.

One major difference compared to a service name is that when using JDBC if the target edition does not exist we get an error message “edition does not exist”. Whereas with a service name the connection silently falls back to the default edition. This can be a good or a bad thing, depending on your specific needs. Personally I prefer to get the error message.

One thought on “How to connect SQLDeveloper to an edition (EBR) on Autonomous Database (ATP)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.