In an earlier article, I showed how to quickly get going with an Oracle Database 23c Free instance in a Gitpod workspace – cloud based, ephemeral, quick start, zero install environment. As part of that workspace, you have access to both SQL*Plus and SQLcl as command line interface to work with this database. However, many of us like to work with a GUI – especially the free SQL Developer tool that over the years we have come to embrace. In this article I will show how to work with a locally installed SQL Developer environment – running on your laptop – against this cloud based Oracle Database 23c Free instance in a Gitpod workspace.
First – make sure you have got SQL Developer up and running on your local environment (starting from the SQL Developer downloads page if you do not yet have it). You also need to have VS Code on your local environment; this will be the conduit for the SSH connection over which SQL Developer will communicate with the database in the remote Workspace.
Second, start a Gitpod Workspace with the Oracle Database 23c Free instance – by following the steps in this article or simply by opening this link (that will launch the Gitpod workspace for you).
With the Gitpod workspace open and the database up and running an accessible locally in the workspace from SQLcl, you need to bring the remote workspace to your laptop. Or rather: you create an SSH connecti0n through your local VS Code that will forward any local communication with port 1521 to the remote workspace. The steps to make this happen:
1. Open the Command Palette in the Gitpod Workspace (on my Windows machine I do this using CTRL SHIFT P ) and select “Gitpod: Open in VS Code”.
Alternatively, expand the File menu and click on Gitpod: Open in VS Code
2. Accept the browser popup that notifies you of opening Visual Studio Code from the browser:
3. Click on Open when Visual Studio Code prompt you to “Allow an extension to open this URI?”
4. Click on Copy to save the temporary password for the SSH connection to the clipboard:
5. When this popup appears:
paste the password from the clipboard to the field and press enter:
6. VS Code now opens and shows the same files and the same terminals as you saw before in the browser based VS Code environment:
Open the ports tab
Here you can see the “magic” to connect the local port 1521 to the port 1521 exposed in the remote Gitpod workspace. Any attempt to access localhost:1521 on your laptop is now intercepted by VS Code and forwarded over the SSH connection to the Gitpod workspace. I think that is brilliant!
7. Time now to open SQL Developer
8. When it has started, you can define a new database connection – click on the plus icon and select New Database Connection
Then provide the connection details:
- username: dev
- password: DEV_PW
- Hostname: localhost
- Port: 1521
- Service Name: FREEPDB1
For convenience sake, you can check the box for Save Password.
Click on Test to verify these connection details:
It is not spectacular in a visual way but the Status: Success message is reassuring. Now click Connect.
9. At this point, SQL Developer is connected to the DEV schema in the FREEPDB1 database running in your cloud based Gitpod workspace. And you can work against it just like you work with any Oracle Database 23c Free instance running anywhere.
Here you see a telltale sign for 23c: select sysdate – at long last without including “FROM DUAL”.
This is the situation now created:
Resources
In an earlier article : how to quickly get going with an Oracle Database 23c Free instance in a Gitpod workspace – cloud based, ephemeral, quick start, zero install environment.
