Oracle ATP Always Free Database, connected to DBeaver

Oracle gives its database away for free in the cloud as Always Free Autonomous Database. In contrast to Amazon's AWS Free Tier that ends after 12 months, Oracle's offer does not expire. The creation of an Oracle Cloud account and a free tier database is easily done with a few mouse clicks. Valid payment information, like from a credit card, has to be provided but Oracle promises not to charge without asking to transform the account into a paid version. This is another difference to AWS. Once an Amazon subscriber exceeds free tier limits, he will be automatically charged. We will deal with AWS in this blog soon.

When we have created our free Oracle Cloud database, we could instantly develop a web application using APEX. But this is not the route this blog wants to go and there are plenty of sources on the web for that. To make productive use of a database as a developer, we still need a connection on our local computer and a tool. SQL Developer would be the logical choice but again, we don't want to go that route as this blog is not supposed to be a pure Oracle blog.

A bit of research led to DBeaver. It's free, fast, connects to almost any database and runs on Win, Mac and Linux. Sounds good? I got really amazed about this tool, especially how fast it responds in contrast to other programs. But how connect it to our Oracle Cloud database? Oracle permits only encrypted connections to their cloud services. The required keystores come with the wallet that is provided to download from the connections tab of the database. A password has to be provided at download. Note the password.


Besides the wallet we need the Oracle Instant Client for the actual platform of our local computer. It's basically a .zip file that needs to be unpacked and its content must be made available on the operating system. Follow the instructions for your OS.

The unzipped content of the wallet will be placed in the /network/admin folder of the instant client. The result will look like this:


With version 18 Oracle introduced a feature that eases the wallet based connection configuration. The file ojdbc.properties links the keystore files with the JDBC driver. The relater entries just have to be activated. The password that was provided at download of the wallet has to be entered here.

In the tnsnames.ora file, there are predefined connections to our ATP database. For our purposes, we can use the first one with the _high suffix, or any other.











Now we can create a new database connection in DBeaver. In the Driver Manager, create a new Oracle driver.

In a previous version of this article I advised to use the Generic driver instead of Oracle. This was because there were issues with some pre-populated Oracle driver wallet properties that confused the Easy Connect Plus mechanism. Result with the Generic driver was, some Oracle specific database objects and features like triggers and packages did not show. To get the full Oracle experience with DBeaver, use the Oracle driver setup.



  1. Add the jdbc driver's jar file (ojdbc8.jar) from your instant client directory.
  2. Find the driver class.
  3. Click on the drop down to enter the class in the property on top
  4. Enter a URL template like this:

    jdbc:oracle:thin:@[service name]?TNS_ADMIN=[your TNS_ADMIN directory (place of tnsnames.ora)]

    For everything after the @sign you can also use a placeholder string like connectionInfo and replace that later in the connection definition for each connection based on this driver setting.
  5. Port 1522 (not really required as the port information comes from the tnsnames entry)
  6. Give the driver a useful name like Oracle ATP
Then create a new database connection based on the defined driver:


The JDBC URL contains all information except for the credentials. The admin user is likely the only one we have at this time so we use this for username. The password is the one we had to provide (and hopefully memorized) once we created the ATP database instance. With Test Connection we should get a success message.

If there are error messages complaining about missing or incorrect key files, check that the Driver Properties oracle.net.wallet_location and oracle.net.wallet_password are empty. If not, clear them (and possibly restart DBeaver).


We have a connection to our local database client to the Oracle ATP cloud database! Oracle put a lot of effort into their web based SQL Developer. But I always prefer a native tool running locally with access to my local file system to organize scripts and without browser latency.


Here are some very helpful Articles I used for the research on this blog post:

Java Connectivity with ATP - Oracle TechNet
What's New in 19c and 18c JDBC and UCP ? - Oracle Blog

Next steps will be to create dedicated database users besides the Admin user and populate them with application tables.

Comments

Post a Comment

Popular posts from this blog

Prototype for a Serverless Business Application

Image
 There are a lot of great tutorials out there on how to build applications based on AWS and in particular on Amplify. But I wanted to try out by myself how much effort it is, to build an application that is a bit closer to a real business desktop app meeting the following requirements: Serverless architecture Based on an RDBMS (relational database) Performing CRUD operations on entities Authentication and authorization Sound exception handling The result is an application serving currently one entity that can be queried and updated. Here's the architectural diagram: The question may be raised, if serverless and using Amplify , why not going NoSQL with DynamoDB? Here are some points that I find important: The majority of software projects is not greenfield. Complex relational data models exist with three digit numbers of tables, numerous (SQL based) use cases and interfaces that can hardly be migrated to a NoSQL data store For business applications in fields like ERP or financials, ...
Read more

Build a Secure Database Driven Web Application with Amplify and React: Part 1 - Setup

Image
In my recent post I have published a demo CRUD application utilizing React.js, AWS Amplify and PostgreSQL on Aurora. For everyone who did not want to sign up and take a look for any reason, here's a screenshot: In the next posts, I want to document how easy it is to build a secure serverless web application on top of Amplify using React as a frontend. AWS account AWS command line tools including amplify are installed and connected to the account Installed Node.js (version >=17) and React.js (version >=18.2) An IDE of choice like VS code Initialize the Project To set up the project, have a code editor ready and an terminal window with command prompt. First, we initialize the React application and give it a name like ardemo for Amplify-React Demo.      > npx create-react-app ardemo A directory with the project name will be created, we change to that and can run the intial React application right away:   > cd ardemo > npm start  By default, the node ...
Read more
Image
Time Flies - AWS Aurora serverless has grown up Maybe you remember my demo application leveraging AWS Aurora Serverless v1 on Postgres, using the built in data API and a React frontend. AWS has announced v1 deprecated a while ago and after a postponement, the end of v1 is likely coming by end of next month (March 2025). The AWS community was not so amazed about having to migrate to Aurora v2 as it lacked two important features:  Scaling down to zero which is important for low budget organizations or projects. The data API, providing a highly convenient way to communicate with an RDBMS without any hassle in drivers and connection pooling Now here's the good news: AWS has added both features to Aurora v2 in the meantime, and there is even more: The data API request limit of 1000 requests per second does was removed for v2 . Due to the improved architecture, up- and downscaling performs faster. In particular, scaling up from 0 (the wake up scenario) is faster and results in improved...
Read more