Oracle

Oracle#

System Authentication#

Hint

If you want to use system authentication to access Oracle, username & password should not present in the connection string.

Oracle Connection#

import connectorx as cx
conn = 'oracle://username:password@server:port/database'        # connection token
query = 'SELECT * FROM table'                                   # query string
cx.read_sql(conn, query)                                        # read data from Oracle

Oracle TNS Alias (DNS) Connection#

import connectorx as cx
conn = 'oracle://username:password@alias_name?alias=true'        # connection token
query = 'SELECT * FROM table'                                   # query string
cx.read_sql(conn, query)                                        # read data from Oracle

Oracle-Pandas Type Mapping#

Oracle Type

Pandas Type

Comment

Number(*,0)

int64, Int64(nullable)

Number(*,>0)

float64

Float

float64

BINARY_FLOAT

float64

BINARY_DOUBLE

float64

VARCHAR2

object

CHAR

object

NCHAR

object

NVarchar2

object

DATE

datetime64[ns]

TIMESTAMP

datetime64[ns]

TIMESTAMP WITH TIME ZONE

datetime64[ns]

Performance (db.r5.4xlarge RDS)#

Modin and Turbodbc does not support read_sql on Oracle

  • Time chart, lower is better.

time chart

  • Memory consumption chart, lower is better.

memory chart

In conclusion, ConnectorX uses 3x less memory and 3x less time compared with Pandas.

Development Setup#

To load the database seed data into the database, you will need sqlplus.

Install it from Oracle’s website’s InstantClient page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html.

On Linux, this can be done by:

wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-sqlplus-linux.x64-23.7.0.25.01.zip
unzip instantclient-sqlplus-linux.x64-23.7.0.25.01.zip
mkdir /opt/oracle/instantclient_23_7/
mv instantclient_23_7/*.so /opt/oracle/instantclient_23_7/
mv instantclient_23_7/* /usr/bin/
export LD_LIBRARY_PATH=/opt/oracle/instantclient_23_7:$LD_LIBRARY_PATH
sqlplus -h

Run the Oracle tests with:

cat scripts/oracle.sql | sqlplus $ORACLE_URL_SCRIPT

cargo test --features all  --test test_oracle -- --ignored
Contents