Skip to main content

Oracle


Connecting to Oracle server from Delta using jdbc

JDBC Connection string

In the connection strings below, replace "userid" with your UWinID, and "password" with your campus password.

Code to test your connection:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class JDBCVersion
{
  public static void main (String args[]) throws SQLException
  {
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:userid/password@oracle.cs.uwindsor.ca:1521:cs01");
    Connection conn = ods.getConnection();

    // Create Oracle DatabaseMetaData object
    DatabaseMetaData meta = conn.getMetaData();

    // gets driver info:
    System.out.println("JDBC driver version is " + meta.getDriverVersion());
  }
}

The classpath for the Oracle JDBC archive will vary depending on the operating system and which version of the Oracle client you installed. The classpath in the following commands are those required to compile and run from a terminal on cs.uwindsor.ca.

javac -classpath /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar  JDBCVersion.java 
java -classpath /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar:.  JDBCVersion

Connecting to Oracle server from Delta using Python

  • Create a Python Virtual Environment
    •  mkdir oraclepy
    •  cd oraclepy/
    •  python -m venv ./venv
  • Pip install cx_oracle
    • pip install cx_oracle --upgradeimage.png
  • Use the following code db_config.py
import cx_Oracle
import sys
import os
import getpass

user = os.getlogin()

dsn = os.environ.get("PYTHON_CONNECT_STRING", "oracle.cs.uwindsor.ca/cs01")

pw = os.environ.get("PYTHON_PASSWORD")
if pw is None:
    pw = getpass.getpass("Enter password for %s: " % user)

Using the following code, you connect to the database 
import cx_Oracle
import db_config

con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)
print("Database version:", con.version)
Run the Code: before running the code, set the environment variables using export image.png Check the following URL for more information: Tutorial cx_Oracle
You can embed the export variables in shell script and call your Python code from the same script 
export ORACLE_HOME=/opt/oracle/instantclient_21_8
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_8:$PATH
export TNS_ADMIN=/opt/oracle/instantclient_21_8/network/admin/
To exit the Python virtual env, type deactivate and press enter. you will be back at the Linux prompt