Oracle SQL and PL/SQL Developer

1. Windows 10 Pro, Windows 11 Pro, or Windows 11 Pro for Workstation (not the Home editions). See this page for the required specs of the operating system.
2. Oracle Database Express Edition (XE) 12c, 18c, or 21c - Free edition of Oracle RDBMS.
3. Oracle SQL Developer - The client interface for connecting to Oracle.

Create the Webucator Folder

The first step to setting up for your course package is to create the folder in which you will store most of your class files:

1. Create a new folder named Webucator directly on the C drive (i.e., C:\Webucator).
2. Unzip the class files for the Oracle SQL class into C:\Webucator\SQL\:
3. Unzip the class files for the Oracle PL/SQL class into C:\Webucator\PL-SQL\:

The folder structure should look like this:

• C:
  • Webucator
    • SQL
    • PL-SQL

Install the Software

Follow these instructions to install Oracle Database Express Edition (XE) and Oracle SQL Developer. Then, return to this page and follow the instructions below.

Now, it is time to get the HR schema.

1. Run the Command Prompt as administrator:
2. Run the following commands to open a SQL prompt at the setup folder:
   1. Change directory to the setup folder within the Webucator folder:

      cd \Webucator\SQL\setup\

   2. Log into SQL*Plus as system using your system password:

      sqlplus system/system@//localhost/XEPDB1

      You should get a SQL prompt:

      SQL> 

      If you get a ORA-12541: TNS:no listener error, do the following:
      1. Find and open listener.ora, which is in C:\app\Admin\product\21c\homes\OraDB21Home1\network\admin\ or a similar directory.
      2. Change the host to 0.0.0.0:
         LISTENER =
           (DESCRIPTION_LIST =
             (DESCRIPTION =
               (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
               (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
             )
           )
      3. Save the file.
      4. Open Services and start (or restart) both of the following services:
         1. OracleOraDB21Home1TNSListener
         2. OracleServiceXE
      5. Restart the command prompt and go back to step 1 (Run the Command Prompt as administrator) above.
3. Create and populate the HR Schema by running the hr_install.sql script:

   SQL> @hr_install.sql

4. To confirm the installation actually did complete, run the following query:

   SQL> SELECT * FROM Regions;

   You should get the following results:

    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
            4 Middle East and Africa
   
   4 rows selected.

Connect to the HR Schema from SQL Developer

1. Open SQL Developer.
2. Click the + icon under Connections:
3. Enter the following:
   1. Name: HR
   2. Username: HR
   3. Password: HR
   4. Hostname: localhost
   5. Port: 1521
   6. Service Name: XEPDB1
   Click Test. You should see a Success message in the bottom left of the dialog.
4. Click Connect.
5. Close the dialog.
6. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet:
7. Enter the following SQL query in the new worksheet:

   SELECT * FROM Regions;

8. Run the query by clicking on the left green triangle. You should get 4 rows in the Query Result: If the query worked, you are all set.

Congratulations. You are all set for your course.

Tip: If your HR account gets locked, see How to Unlock the HR User in XEPDB1.