Setting up an Oracle Database Environment

Preamble

I wrote this for one of my previous instructors who wanted to give his students an alternative to downloading a local copy of sqlplus.

Introduction

This assignment is designed to set up the development environment you will use for the rest of the course. You will use Docker Desktop to create an Oracle database container to connect to VS Code to run your SQL scripts.

For this assignment, you need to have the following:

• A Windows computer with a minimum of 16GB RAM and 100GB of free disk space running Windows 11 or Windows 10 version 2004 or later.

Procedure

Installing Windows Subsystem for Linux (WSL)

First, you must install Windows Subsystem for Linux (WSL) on your computer. Docker Desktop will use this to run its containers.

1. Open the start menu and search for ‘PowerShell’.
2. Right-click on ‘Windows PowerShell’ and select ‘Run as administrator’.
3. In the PowerShell window, run the following command to enable WSL:

   wsl --install
   

4. You will need to restart your computer when prompted.
5. Open the start menu and search for ‘Ubuntu’.
6. Click on ‘Ubuntu’ to launch the Ubuntu terminal.
7. Follow the on-screen instructions to set up your Ubuntu distribution.
8. Note down the username and password you set up during installation.
9. Close the terminal window.

Installing VS Code

You will need to install Visual Studio Code on your computer now. Visual Studio Code is a popular code editor known for its ease of use and extensibility. We will use VS Code to write our SQL scripts throughout this course.

1. In your web browser, go to the Visual Studio Code website.
2. Click on the ‘Download for Windows’ button. This should start the download of the VS Code installer automatically.

3. Once the installer has finished downloading, navigate to the .exe file in your downloads folder and double-click on it to run the installer.
4. Follow the on-screen instructions to install VS Code on your computer. We will be using the default settings for this course. You can customize the installation if you wish, but that may make it more difficult for your instructor to assist you if you encounter any problems.
5. Once the installation is complete, open VS Code by double-clicking on the icon on your desktop or by searching for it in the Start menu.
6. When loading VS Code for the first time, you may be prompted to customize your settings. You can skip this step for now by clicking ‘Mark Done’ at the bottom of the list.

Installing VS Code Extensions

Next, you will install the necessary extensions in VS Code to connect to an Oracle database and run SQL scripts.

1. Open VS Code if it is not already open.
2. On the right-hand menu, click the ‘Extensions’ icon. You can also press Ctrl+Shift+X to open the Extensions view.
3. Search for ‘Oracle’ in the Extensions view search bar.
4. Select ‘Oracle SQL Developer Extension for VSCode’ from the search results and click the ‘Install’ button.

Installing Docker Desktop

Now, you will need to install Docker Desktop on your computer. Docker Desktop is a tool that allows you to run containers or virtual machines on your computer.

1. In your web browser, go to the Docker Desktop website.
2. Click on the ‘Download for Windows’ button. This should start the download of the Docker Desktop installer automatically.
3. Once the installer has finished downloading, navigate to the .exe file in your downloads folder and double-click on it to run the installer.
4. Follow the on-screen instructions to install Docker Desktop on your computer. We will be using the default settings for this course. You can customize the installation if you wish, but that may make it more difficult for your instructor to assist you if you run into any problems.
5. Once the installation is complete, you will be prompted to restart your computer. Click on the ‘Close and Restart’ button to restart your computer.

Getting the Oracle Database Container

Now, you will pull the Oracle Database Express Edition container from the Oracle Container Registry and run it on your computer.

1. Open the start menu and search for ‘Docker Desktop’.
2. Click on ‘Docker Desktop’ to launch the Docker Desktop application.
3. Now open VS Code.
4. Open a new terminal in VS Code by clicking on ‘Terminal’ in the top menu and selecting ‘New Terminal’, or by pressing Ctrl + `.

5. In the terminal window, run the following command to pull the Oracle Database Express Edition container:

   docker pull container-registry.oracle.com/database/express:latest
   

6. Once the container has finished downloading, run the following command to start the container:

    docker run -d --name oracle-db -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=password container-registry.oracle.com/database/express:latest
   

7. To confirm that the container is running correctly, open Docker Desktop and check the ‘Containers’ tab. You should see a new container named ‘oracle-db’ with a green status indicator.

Connecting VS Code to Oracle Database

You will now connect VS Code to the Oracle Database container you created.

1. Open VS Code if it is not already open.
2. On the left-hand menu, click on the three dots (...) to expand the menu.
3. Click on Oracle Explorer
4. Next to the ‘Database’ dropdown, click on the ‘+’ icon to add a new connection.
5. Enter the following details in the connection dialogue box:
   • Hostname: localhost
   • Port: 1521
   • Service Name: XE
   • User Name: sys
   • Password: password
   • Role: SYSDBA
6. Check the ‘Save Password’ box to save the password for future connections and the ‘Set as default connection’ box to make this the default connection.
7. Click on ‘Test Connection’ to verify that the connection is successful.
8. Click on ‘Save Connection’ to create the connection details.
9. You should now see a new connection called ‘SYS.XE’ under the ‘Database’ dropdown in Oracle Explorer.

Running an SQL Script inside Docker via VS Code

Now that you have connected VS Code to the Oracle container, you will run an SQL script inside the container to verify the completion of the setup to your instructor.

1. In VS Code, right-click on the ‘SYS.XE’ connection in Oracle Explorer and select ‘Open New SQL File’.

2. This will open a new SQL file in the editor. Copy and paste the following SQL script into the file:

   DROP TABLE DB_TEST_TABLE;
   
   CREATE TABLE DB_TEST_TABLE (
      ID NUMBER PRIMARY KEY,
      NAME VARCHAR2(100),
      AGE NUMBER
   );
   
   INSERT INTO DB_TEST_TABLE (ID, NAME, AGE) VALUES (1, 'John', 25);
   INSERT INTO DB_TEST_TABLE (ID, NAME, AGE) VALUES (2, 'Jane', 30);
   
   COMMIT;
   
   SELECT * FROM DB_TEST_TABLE;
   

3. Now click the ‘Execute All’ button at the top right of the editor window to run the script.

4. You should now be moved to a new tab called ‘Results’ where you can see the script’s output. You should see a table with two rows, one for John and one for Jane.
5. If you see the table with the correct data, you have successfully set up your Oracle database environment.

Deliverable

To complete this assignment, you must submit a screenshot of the ‘Results’ tab in VS Code showing the output table of the SQL script you ran in the previous section.

Downloads

References

1. https://learn.microsoft.com/en-us/windows/wsl/install
2. https://learn.microsoft.com/en-us/windows/wsl/setup/environment#set-up-your-linux-username-and-password
3. https://code.visualstudio.com/
4. https://www.docker.com/products/docker-desktop/

Changelog

2025-01-02

• Replaced deprecated ‘Oracle Developer Tools for VS Code’ with ‘Oracle SQL Developer Extension for VSCode’
• Adds .docx for local download