As databases evolve to be more autonomous and cloud-friendly, developers need efficient and reliable methods to connect and interact with them. This post will provide an overview on how to connect and query Oracle's Autonomous Database (ADB) from some popular programming languages and some of the differences in each implementation.
Prerequisites
Before diving into the database connections from specific languages, you obviously need to have the language-specific support for each available Oracle database binding. Typically, this means having the language and related tools installed on your system (intepreter/compiler, package manager, etc). For some driver connections, you will also need the Oracle libraries, which are included in Oracle DB installations or can be obtained via the Oracle Instant Client.
Oracle Instant Client enables development and deployment of applications that connect to Oracle Database, either on-premise or in the Cloud. The Instant Client libraries provide the necessary network connectivity and advanced data features to make full use of Oracle Database. The libraries are used by the Oracle APIs of popular languages and environments including Python, Node.js, Go, PHP and Ruby, as well as providing access for Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), JDBC OCI, ODBC and Pro*C applications.
Language bindings for Oracle often rely on the Oracle Instant Client libraries to facilitate connections to Oracle databases, including ADB, either via Oracle Call Interface (OCI) or Oracle Database Programming Interface for C (ODPI-C) (more on these below).
Note: if you're a Mac developer, at the time of this writing, there is no native version of the Oracle Instant Client libraries for Apple silicon based (ARM-based M1/M2) Macbooks. Developers with these type of machines will need to use Rosetta 2 to bridge this gap (until ARM based libraries are released).
A wide range of Oracle database bindings
Once you've set up your Autonomous Database on Oracle Cloud Infrastructure (OCI), the next step is establishing a connection from your application. The landscape of drivers and bindings available for this purpose is as diverse as it is robust:
Oracle database language bindings
The array of options spans from open-source initiatives to Oracle-supported drivers. These various connectors offer unique advantages and cater to different scenarios. Some are community-driven efforts, reflecting the innovation and collaborative spirit of open-source culture. Others come with the endorsement and support of Oracle, assuring compatibility and often comprehensive documentation.
A critical distinction among these bindings is the division between "Thin" and "Thick" drivers. Thin drivers are designed for streamlined operation; they establish connectivity without the need for Oracle Client libraries, facilitating a lightweight and agile setup. This can be particularly advantageous when looking to minimize the application's footprint or when working within environments where deploying Oracle-specific libraries is impractical.
In contrast, drivers that require Oracle libraries (Thick drivers) tap into a deeper level of Oracle's functionality, leveraging OCI or ODPI-C for their connectivity layers. Each of these layers presents its own complexity and capabilities:
- OCI (Oracle Call Interface): This is a traditional and feature-rich API that allows for a fine-grained control over database interactions. It is often the go-to choice for applications that need to exploit the full spectrum of Oracle's features and where performance is a critical concern.
- ODPI-C (Oracle Database Programming Interface for C): Acting as a high-level abstraction over OCI, ODPI-C offers a simpler, more streamlined API that still provides comprehensive Oracle functionality but with reduced complexity. It is suitable for applications that require a balance between ease of use for the developer and access to advanced database features.
Two Rust bindings: rust-oracle and Sibyl
For Rust developers, rust-oracle and Sibyl are two prominent open source "thick" driver bindings. They both depend on Oracle libraries mentioned above, with rust-oracle being OCI-based and Sibyl utilizing ODPI-C. Both provide robust means to connect to ADB, but they differ mostly in terms of implementation complexity and feature set (for example the Sibyl library includes native support for async calls).
Let's take a look at a basic code snippets to connect and pass a query to fetch the server version in rust-oracle:
// Rust code using rust-oracle to connect to ADB and execute a SQL query
use oracle::{Connection, Error, RowValue};
fn main() -> Result<(), Error> {
// Set the TNS_ADMIN environment variable
env::set_var("TNS_ADMIN", "/path/to/your/wallet_directory");
let conn = Connection::connect("user", "password", "//adb.instance.url")?;
println!("Connected to database successfully.");
let sql = "SELECT * FROM v$version WHERE banner LIKE 'Oracle%'";
let rows = conn.query(sql, &[])?;
for row_result in rows {
let row: RowValue = row_result?;
let version: String = row.get("banner")?;
println!("Oracle DB Version: {}", version);
}
Ok(())
}
And now basically the same code with Sibyl:
// Rust code using Sibyl to connect to ADB and execute a SQL query
use sibyl::{Cursor, Error, Session};
fn main() -> Result<(), Error> {
// Set the TNS_ADMIN environment variable
env::set_var("TNS_ADMIN", "/path/to/your/wallet_directory");
let session = Session::new("user", "password", "//adb.instance.url")?;
println!("Connected to database successfully.");
let sql = "SELECT * FROM v$version WHERE banner LIKE 'Oracle%'";
let mut cursor = session.prepare(sql)?.query(())?;
while let Some(row) = cursor.next_row()? {
let version: String = row.get(0)?;
println!("Oracle DB Version: {}", version);
}
Ok(())
}
As you can see the code is very similar in both libraries and some of the differences are more apparent when you go into more advanced code like DML handling, async support, etc.
For these snippets to work the Oracle libraries will have to be visible from the dynamic library path in your OS. And remember, if you're on Apple silicon you'll need Rosetta 2 as mentioned before and a build that targets Intel:
cargo build --target x86_64-apple-darwin
One Go binding: godror
For Go developers, godror is a comprehensive package built on a multi-level architecture (Go, CGO, ODPI-C (and then OCI)).
Here's a similar code snippet connectiong to ADB and passing a query:
// Go code using godror to connect to ADB and execute a SQL query
package main
import (
"database/sql"
"fmt"
_ "github.com/godror/godror"
)
func main() {
// Set the path to the directory containing your Oracle wallet
os.Setenv("TNS_ADMIN", "/path/to/your/wallet_directory")
db, err := sql.Open("godror", "user/password@adb.instance.url")
if err != nil {
fmt.Println("Error connecting to the database: ", err)
return
}
defer db.Close()
rows, err := db.Query("SELECT * FROM v$version WHERE banner LIKE 'Oracle%'")
if err != nil {
fmt.Println("Error performing query: ", err)
return
}
defer rows.Close()
for rows.Next() {
var version string
if err := rows.Scan(&version); err != nil {
fmt.Println("Error reading rows: ", err)
return
}
fmt.Println("Oracle DB Version: ", version)
}
}
Although the library is powerful, according to some tests, the CGO layer can introduce significant overhead so you might want to carefully test the performance of your ADB enabled godror based application.
One "thin" driver: node-oracledb
The node-oracledb binding is an open-source driver supported by Oracle. It operates in both "thick" and "thin" modes ("thin" by default). The "thin" implementation does not depend on the Oracle libraries mentioned above and is generally easier to set up. Let's take a look at a similar code snippet to connect to ADB and pass a query:
// Node.js code using node-oracledb "thin" driver to connect to ADB and execute a SQL query
const oracledb = require('oracledb');
// Set the path to the directory containing your Oracle wallet
process.env.TNS_ADMIN = '/path/to/your/wallet_directory';
async function run() {
let connection;
try {
connection = await oracledb.getConnection({
user: "user",
password: "password",
connectionString: "adb.instance.url"
});
console.log("Connected to database successfully.");
const result = await connection.execute(
"SELECT * FROM v$version WHERE banner LIKE 'Oracle%'"
);
console.log("Oracle DB Version: ", result.rows);
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();
As you can see above there are no distinctions at the code level to use "thin" mode or "thick" mode. The difference is at the configuration level where you basically have to enable the "thick" mode and provide the Oracle libraries path:
const oracledb = require('oracledb');
let clientOpts = {};
if (process.platform === 'win32') {
// Windows
// If you use backslashes in the libDir string, you will
// need to double them.
clientOpts = { libDir: 'C:\\oracle\\instantclient_19_19' };
} else if (process.platform === 'darwin' && process.arch === 'x64') {
// macOS Intel
clientOpts = { libDir: process.env.HOME + '/Downloads/instantclient_19_8' };
}
// else on other platforms like Linux the system library search path MUST always be
// set before Node.js is started, for example with ldconfig or LD_LIBRARY_PATH.
// enable node-oracledb Thick mode
oracledb.initOracleClient(clientOpts);
Note that by default the library runs in thin mode, so we don't need any local libraries to facilitate the connection (as the connection protocol is implemented in the driver itself).
Another "thin" driver: python-oracledb (formerly cx_Oracle)
The situation is the same for Python and the python-oracledb library: it works in "thin" mode by default and in "thick" mode with proper configuration. Plus the code is extermely simple:
# Python code using python-oracledb "thin" driver to connect to ADB and execute a SQL query
import oracledb
import os
# Assuming you have your Oracle wallet files in the directory specified in TNS_ADMIN environment variable
os.environ["TNS_ADMIN"] = "/path/to/your/wallet_directory"
# Construct the connection string. This typically includes the TNS name, which you get from your tnsnames.ora file.
connection_string = "user/password@adb_tns_name"
try:
# Connect to the database
with oracledb.connect(connection_string) as connection:
# Create a cursor
with connection.cursor() as cursor:
# Execute the SQL query
cursor.execute("SELECT * FROM v$version WHERE banner LIKE 'Oracle%'")
# Fetch and print the results
for row in cursor:
print("Oracle DB Version:", row[0])
except oracledb.Error as e:
print("Oracle-DB error:", e)
Note that you need to install the library first:
pip install oracledb
And to enable "thick" mode, after installing the Oracle Client libraries, you'd tipically set an environment variable like this:
# Set the environment variable to use thick mode
os.environ["ORACLEDB_THICK_MODE"] = "TRUE"
Driverless connections
Alternatively, developers can bypass traditional drivers altogether using Oracle REST Data Services (ORDS) to create RESTful services directly against ADB. This driverless approach simplifies connectivity. Another similar approach is combining REST with standard drivers like JDBC (there are both "thin" and "thick" versions of JDBC). Even if not "driverless" the driver is transparent to the developer as a higher level framework is used for operations on the database. For instance, you could create APIs with Spring Boot that communicate with ADB over REST, making it accessible from any HTTP-capable environment.
Source: oracle.com
Posts
0 comments:
Post a Comment