Introduction
This blog shows you how to use Julia with both the Oracle Database and Oracle TimesTen In-Memory Database.
The same Julia source code will work against either Oracle Database 19c or Oracle TimesTen.
Julia is a general purpose programming language which is popular in machine learning and scientific computing as it is both powerful and fast. Julia has dynamic types like Python, uses garbage collection and LLVM for just in time compilation which enables performance approaching C code. Julia is one of the newest computer languages [2012] to see rapid growth [35 million downloads].
This blog covers the following topics:
- Using the Oracle TimesTen Cache LiveLab
- Installing Julia 1.8.2 in the LiveLab
- Installing the Oracle.jl library
- Connecting to either Oracle Database 19c or TimesTen Cache
- Simple queries returning resultsets
- Dropping tables via a PLSQL block
- Creating tables using SQL
- Inserting, updating and deleting rows using SQL
- Committing ACID transactions
- How Julia programs can work the same with both Oracle Database 19c and TimesTen Cache
This blog is not a tutorial on the Julia language, instead shows you how to do the most basic SQL operations using basic Julia code.
Future blogs will cover other database topics for Julia such as:
- Error handling
- Using Datasets
- Using Connection pools
- Using PLSQL
- Optimizing SQL with Julia
Using the Oracle TimesTen Cache LiveLab
The TimesTen Cache LiveLab is a VM which runs on Oracle Cloud. The VM uses Docker containers to run the Oracle Database 19c and TimesTen 22.1 Cache.
The Oracle TimesTen Cache LiveLab is here.
You need to have completed Labs 1 to 13 before you can start using Julia with TimesTen.
Oracle.jl versions
- The Oracle.jl SQL driver works with both the Oracle Database and Oracle TimesTen
- The LiveLab for TimesTen Cache uses Oracle Linux 8.6
- This blog was tested with Julia 1.8.2 [binary install] and Julia 1.9.0 DEV [built from source]
Installing Julia in your LiveLab VM container
To install Julia 1.8.2 in your TimesTen Cache LiveLab VM.
First ssh to your LiveLab VM:
Then ssh to the tthost1 Docker container in the LiveLabs VM:
Install Julia 1.8.2 in the container:
sudo yum install wget
sudo yum install gcc
wget https://julialang-s3.julialang.org/bin/linux/x64/1.8/julia-1.8.2-linux-x86_64.tar.gz
tar zxvf julia-1.8.2-linux-x86_64.tar.gz
export PATH=$PATH:/tt/livelab/julia-1.8.2/bin
This installed Julia 1.8.2 on my LiveLab container.
Install the Oracle.jl driver
Install the Oracle.jl driver from within the Julia shell:
Pkg.add(“Oracle”)
…
A Julia program that works with Oracle 19c and TimesTen 18c
Create a trivial Julia program using the Oracle.jl driver that works with both Oracle Database 19c and Oracle TimesTen.
The test.jl program does the following:
- Connects as appuser/appuser@$SVC_NAME
- Determines the version of the Oracle Instant Client
- Determines the version of the database server
- Queries a set of rows via a SQL where clause
- Disconnects from the database
import Oracle
username=”appuser”
password=”appuser”
svc = ENV[“SVC_NAME”]
conn = Oracle.Connection(username, password, svc)
v = Oracle.client_version(Oracle.context(conn))
println(“\nOracle Instant Client version $v\n”)
release, server_version = Oracle.server_version(conn)
println(“Database server version: “)
println(“release = $release”)
println(“server_version = $server_version”)
println(“\nGet some rows”)
query_stmt = Oracle.Stmt(conn, “select * from vpn_users where vpn_id = 0 and vpn_nb < :1 and rownum < 3 order by 2”)
query_stmt[:1] = 5
Oracle.query(query_stmt) do cursor
for row in cursor
print( row[“VPN_NB”] )
print( “, ” )
print( row[“DIRECTORY_NB”] )
print( “, ” )
print( row[“LAST_CALLING_PARTY”] )
println(“”);
end
end
Oracle.close(query_stmt)
Oracle.close(conn)
println(“\nBye”)
The TimesTen Cache LiveLab already has the appuser schema and the VPN_USERS tables with data in it.
Use Oracle Net Service names to connect
There are multiple ways to connect with the Oracle.jl driver.
The TimesTen Cache LiveLab configures and uses $TNS_ADMIN/tnsnames.ora to enable connection to either the Oracle Database 19c or Oracle TimesTen.
ORCLPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPDB1)
)
)
SAMPLEDB =
(DESCRIPTION =
(CONNECT_DATA =
(SERVER = timesten_direct)
(SERVICE_NAME = sampledb)
)
)
SAMPLEDBCS =
(DESCRIPTION =
(CONNECT_DATA =
(SERVER = timesten_client)
(SERVICE_NAME = sampledbcs)
)
)
This tnsnames.ora file provides three services to connect to:
- TimesTen client/server connections use TCP/IP sockets [and optionally TLS]. The TimesTen client can be on a different machine than the TimesTen server
- TimesTen direct linked connections use shared memory
- Direct linked connections are about 10x faster than TCP sockets
- The application and TimesTen MUST to be on the same machine.
- The TimesTen service name [direct linked or client/server] in tnsnames.or MUST point to a working TimesTen DSN in sys.odbc.ini
Choose which service name to connect to
The test.jl Julia program uses an environment variable [$SVC_NAME] to define the service name to connect to.
You need to define the value of $SVC_NAME to choose which of the three services to connect to.
To set the SVC_NAME in the Linux bash shell to point ot Oracle Database 19c:
To set the SVC_NAME in the Linux bash shell to point to TimesTen with a direct linked connection:
To set the SVC_NAME in the Linux bash shell to point to a TimesTen client/server connection:
Use Julia to query Oracle Database 19c or TimesTen
Run test.jl against Oracle Database 19c:
The output from test.jl when connected to Oracle Database 19c:
[oracle@tthost1 julia]$ export SVC_NAME=ORCLPDB1
[oracle@tthost1 julia]$ julia test.jl
Oracle Instant Client version Oracle.OraVersionInfo(19, 14, 0, 0, 0, 1914000000)
Database server version:
release = Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
server_version = Oracle.OraVersionInfo(19, 3, 0, 0, 0, 1903000000)
Get some rows
0, 5500 , 000000000
1, 5501 , 000000000
Bye
[oracle@tthost1 julia]$
The TimesTen Cache LiveLab uses:
- Oracle Instant Client 19c which ships with TimesTen 22.1. This is why the Client version output is 19.14
- Oracle Database 19c which is why the Server version output is 19.3
- Pre-loaded data which exists in the APPUSER.VPN_USERS table
Run test.jl against TimesTen Cache:
The output of test.jl when connected to Oracle TimesTen Cache:
[oracle@tthost1 julia]$ export SVC_NAME=sampledb
[oracle@tthost1 julia]$ julia test.jl
Oracle Instant Client version Oracle.OraVersionInfo(19, 14, 0, 0, 0, 1914000000)
Database server version:
release = Oracle TimesTen IMDB version 22.1.1.3.0
server_version = Oracle.OraVersionInfo(22, 1, 1, 3, 0, 2201010300)
Get some rows
0, 5500 , 000000000
1, 5501 , 000000000
Bye
[oracle@tthost1 julia]$
The TimesTen Cache LiveLab uses:
- Oracle Instant Client 19c which ships with TimesTen 22.1. This is why the Client version output is 19.14
- TimesTen 22.1 which is why the Server version output is 22.1.1.3
- Pre-loaded data which exists in the APPUSER.VPN_USERS table
Apart from the Database server version, the output is the same for both Oracle Database 19c and TimesTen Cache:
- The Database server Version output is supposed to be different for Oracle and TimesTen
- The output is the same for both TimesTen Direct Linked and Client/Server connections
A Julia application to modify Oracle Database 19c or TimesTen
The test2.jl program does the following:
- Connects as appuser/appuser@$SVC_NAME
- Determines the version of the database server
- Drops table T via a PLSQL block
- Creates table T via SQL
- Does a batch insert of three rows into table T
- Updates all of the rows in table T
- Deletes a row by primary key in table T
- Commits the transaction
- Queries the rows in table T after the inserts, updates and deletes
- Disconnects
import Oracle
# Define a function to show the resultset
function showRows(conn::Oracle.Connection)
local myQuery = “
select id,
addr,
price,
sold
from t”
Oracle.query(conn, myQuery) do cursor
# prints column names
println(names(cursor))
# print all of the columns in the resultset
for row in cursor
print( row[“ID”] )
print( “, ” )
print( row[“ADDR”] )
print( “, ” )
print( row[“PRICE”] )
print( “, ” )
print( row[“SOLD”] )
println( “” )
end
end
return
end
# The ‘main’ logic
username=”appuser”
password=”appuser”
svc = ENV[“SVC_NAME”]
conn = Oracle.Connection(username, password, svc)
v = Oracle.client_version(Oracle.context(conn))
println(“\nOracle Instant Client version $v\n”)
release, server_version = Oracle.server_version(conn)
println(“Database server version: “)
println(“release = $release”)
println(“server_version = $server_version”)
# Drop table t if it exists
plsql =
“begin
execute immediate ‘DROP TABLE t’;
exception when others
then null;
end;”
tableT = “
CREATE TABLE T (
ID INT NOT NULL primary key,
addr varchar2(64),
price number(9,2),
sold date default sysdate
)”
Oracle.execute(conn, plsql)
Oracle.execute(conn, tableT)
Oracle.execute(conn, “INSERT INTO t ( ID, ADDR, PRICE, sold ) VALUES ( 1, ‘1 Seasame Street’, 42.69, sysdate )”)
Oracle.execute(conn, “INSERT INTO t ( ID, ADDR, PRICE, sold ) VALUES ( 2, ‘500 Oracle Parkway’, 17.10, sysdate )”)
Oracle.execute(conn, “INSERT INTO T ( ID, ADDR, PRICE, sold ) VALUES ( 3, ‘3rd Ave’, 5.92, sysdate )”)
Oracle.commit(conn)
println(“\nData after the inserts”)
showRows(conn)
Oracle.execute(conn, “update t set addr = ‘Unknown'”)
println(“\nData after the updates”)
showRows(conn)
Oracle.execute(conn, “delete from t where id = 1”)
println(“\nData after the delete”)
showRows(conn)
# Commit the changes
Oracle.commit(conn)
Oracle.close(conn)
println(“\nBye”)
Run test2.jl against Oracle Database 19c:
The output of test2.jl when connected to Oracle Database 19c:
[oracle@tthost1 julia]$ export SVC_NAME=ORCLPDB1
[oracle@tthost1 julia]$ julia test2.jl
Oracle Instant Client version Oracle.OraVersionInfo(19, 14, 0, 0, 0, 1914000000)
Database server version:
release = Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
server_version = Oracle.OraVersionInfo(19, 3, 0, 0, 0, 1903000000)
Data after the inserts
[“ID”, “ADDR”, “PRICE”, “SOLD”]
1.0, 1 Seasame Street, 42.69, 2022-10-21T23:25:36
2.0, 500 Oracle Parkway, 17.1, 2022-10-21T23:25:36
3.0, 3rd Ave, 5.92, 2022-10-21T23:25:36
Data after the updates
[“ID”, “ADDR”, “PRICE”, “SOLD”]
1.0, Unknown, 42.69, 2022-10-21T23:25:36
2.0, Unknown, 17.1, 2022-10-21T23:25:36
3.0, Unknown, 5.92, 2022-10-21T23:25:36
Data after the delete
[“ID”, “ADDR”, “PRICE”, “SOLD”]
2.0, Unknown, 17.1, 2022-10-21T23:25:36
3.0, Unknown, 5.92, 2022-10-21T23:25:36
Bye
[oracle@tthost1 julia]$
Run test2.jl against TimesTen Cache:
The output of test2.jl when connected to TimesTen Cache:
[oracle@tthost1 julia]$ export SVC_NAME=sampledb
[oracle@tthost1 julia]$ julia test2.jl
Oracle Instant Client version Oracle.OraVersionInfo(19, 14, 0, 0, 0, 1914000000)
Database server version:
release = Oracle TimesTen IMDB version 22.1.1.3.0
server_version = Oracle.OraVersionInfo(22, 1, 1, 3, 0, 2201010300)
Data after the inserts
[“ID”, “ADDR”, “PRICE”, “SOLD”]
1.0, 1 Seasame Street, 42.69, 2022-10-21T23:23:27
2.0, 500 Oracle Parkway, 17.1, 2022-10-21T23:23:27
3.0, 3rd Ave, 5.92, 2022-10-21T23:23:27
Data after the updates
[“ID”, “ADDR”, “PRICE”, “SOLD”]
1.0, Unknown, 42.69, 2022-10-21T23:23:27
2.0, Unknown, 17.1, 2022-10-21T23:23:27
3.0, Unknown, 5.92, 2022-10-21T23:23:27
Data after the delete
[“ID”, “ADDR”, “PRICE”, “SOLD”]
2.0, Unknown, 17.1, 2022-10-21T23:23:27
3.0, Unknown, 5.92, 2022-10-21T23:23:27
Bye
[oracle@tthost1 julia]$
Apart from the Server version, the output is the same for both Oracle Database 19c and TimesTen Cache:
- The Server version output is supposed to be different for Oracle and TimesTen
- The output is the same for both TimesTen Direct Linked and Client/Server connections
Summary
- The LiveLab for Oracle TimesTen Cache runs on Oracle Cloud
- Use the Oracle.jl SQL driver to talk to Oracle databases
- The same Julia program can be used with Oracle 19c or TimesTen Cache
- Connect to the database via a service name in tnsnames.ora
Learn more SQL Language APIs
- Using Rust with Oracle Databases
- Using Node.js with Oracle Databases
- Using Python with Oracle Databases
- Using Java JDBC with UCP and Jetty
Learn more about TimesTen XE:
- Oracle TimesTen XE Home Page
- Oracle TimesTen XE Download
- Oracle TimesTen XE Docker Container
- Oracle TimesTen Classic Home Page
- Oracle TimesTen Scaleout Home Page
- Oracle TimesTen VM with Hands On Labs
- Oracle TimesTen Documentation
More TimesTen XE Blogs
- An introduction to TimesTen XE
- How fast is TimesTen XE
- How to create a database on TimesTen XE
- TimesTen XE SQL
- TimesTen XE SQL Profiles
- Using TimesTen XE on WSL
- Using client/server without config files on TimesTen XE
- Using client/server with config files on TimesTen XE
Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.