Connecting to the Oracle Server

As the department's Oracle database is a protected host, we must first tunnel into the department before accessing the database. The tunnel must remain open throughout the duration you want to connect to the database.

Depending on whether you are working from Syzygy or your own local computer, step 1 will vary. Step 2 will remain the same.

Step 1: Tunnelling to the department server

Option 1: If you are working from Syzygy...

  1. Go to the launcher view of Jupyter. This is the view with the sidebar that lists all the files in your account (see screenshot below). If you do not see the launcher view of Jupyter, click on the Jupyter logo in the top left corner of the screen.
    A screenshot showing the Launcher view of Jupyter
  2. Click on Terminal.
  3. In the resulting Terminal window, type the following and press enter. Be sure to replace CWL with your actual CWL id.
    ssh -l CWL -L 127.0.0.1:1522:dbhost.students.cs.ubc.ca:1522 remote.students.cs.ubc.ca
  4. Enter your CWL password when prompted.

  5. After clicking enter, you may be prompted with a message that looks like the following:
    The authenticity of host 'remote.students.cs.ubc.ca (<some IP address>)' can't be established.
    ED25519 key fingerprint is <some series of numbers and letters>.
    Are you sure you want to continue connecting (yes/no/[fingerprint])?


    Type yes and press enter.
  6. You must keep this terminal window open while you access the database. When you are finished or want to stop working, type exit into the terminal window and hit enter to close the session.
  7. Move to step 2 to test that your connection works.

Option 2: If you are working from a local Jupyter Installation...

This section is for anyone who has installed a version on Jupyter on their computer and wants to use this local installation to access the UBC CS department's Oracle database. It assumes that you have already installed Jupyter, cx_Oracle, and the Oracle Instant Client. If you have not done these things, please refer to the "Programming Environment" page on Canvas for instructions on how to do so.

Windows
  1. If you do not have XManager installed on your computer, download and install a copy.
  2. Create an XShell session in XManager by clicking on the leftmost button (the one with a + sign).
  3. In Connection set the values as per the screenshot below.
    Screenshot of launching an XShell session in XManager
  4. In Authentication, set the values as per the screenshot below (you can choose to use SSH authentication instead of password but in terms of getting things set up and running quickly, password might be a better choice). Use your own CWL username and password.
    Screenshot of how to set the Authentication page in XShell
  5. In Tunneling, click add and fill in the information as per the screenshot below.
    Screenshot of how to set the Tunneling page in XShell.
  6. Check the "Forward X11 connection" box. Forward the connection to Xmanager.
    Screenshot of the overall Tunneling screen in XShell.
  7. Click "Ok".
  8. Launch your connection by clicking on "Oracle" in the sidebar in XManager.
  9. Move to step 2 to test that your connection works.
Mac or Unix
  1. Open Terminal and run:

    ssh -l username -L localhost:1522:dbhost.students.cs.ubc.ca:1522 remote.students.cs.ubc.ca

    Be sure to replace username with your actual CWL username.

  2. Move to step 2 to test that your connection works.

Step 2: Test your connection

Assuming you have not removed the tables created by the bookbiz.sql file from an earlier assignment, you should be able to test your connection by making a query on one of the bookbiz tables.

First, use conda or pip to install the "oracledb" package (documentation). You should also follow the environment setup steps under the Programing Environment page on Canvas.

Try running the following in a Jupyter cell. Be sure to replace username with ora_CWLID (no @stu required) and password with "a" followed by your student number (e.g., a12345678).


dsn = oracledb.makedsn("localhost", 1522, service_name="stu")
connection = oracledb.connect(user="username", password="pwd", dsn=dsn, encoding="UTF-8")

cur = connection.cursor()
for row in cur.execute("select * from titles"):
    print(row)
cur.close()

Update: since cx_Oracle no longer works properly on new machines, we recommend you use oracledb in place of cx_Oracle.

Note: If you get an error cx_Oracle error. DPI-1047: Cannot locate a 64-bit Oracle Client library, please check out this post. Warning: If you are following the first answer and you are using Jupyter Notebook, make sure you are copying those dll files to the directory where you installed Anaconda (e.g., inside the directory named "Anaconda3 (64-bit)"). This is because sometimes you have different Python in your computer, and you want to make changes to the one that you are currectly using in the Jupyter Notebook.

If you are using a Mac, you can move the entire instant_client folder and make sure to set the lib_dir to that location. For example, if your instant_client is in your Downloads folder, the code added should be cx_Oracle.init_oracle_client(lib_dir= r"Downloads")