Using Oracle db26ai from Rust with the sibyl crate

The whole purpose of this article is to interact with an Oracle database, to query, create, update, and modify it, so we will need to have access to an instance. I recommend using a container because we will add a schema and some data. If you spin up an instance of the Oracle AI Database 26ai Free Container, it will start in a clean state, and you can discard the data after using all the code in this article. I have already explained how to run Oracle AI db26ai in a container in this other article, in case you haven't done so yet.

In this article, I will use the sibyl crate, which is based on Oracle Call Interface (OCI). That means that the OCI dynamic libraries must be installed and available on your system. In case you don't have them yet, they are available in a package that can be downloaded in two versions: basic and basic light. They both contain all the drivers, but the latter only includes the English error messages and support for some character sets. I'll use the former, but feel free to do otherwise if you don't need the extra data.

Double-click on the dmg file that you have downloaded from the link above, or use the following command with the right version identifier.

  hdiutil mount instantclient-basic-macos.arm64-<version id>.dmg

Then, install its contents from the command line.

  cd /Volumes/instantclient-basic-macos.arm64-<version id>/
  sh ./install_ic.sh

However, the script will fail in Tahoe (macOS 26) and show a pop-up because macOS cannot assess the safety of this code. Open the System settings, and select Privacy & Security from the left pane. Scroll down and allow this script to run. Execute the script again, and it will create the directory ~/Downloads/instantclient_23_3 and copy its contents there. I moved its contents to ~/bin/oracle because I don't want the libraries I use to be in my downloads directory, and I added that directory to the dynamic linker path so they can be used by the crate.

  export DYLD_LIBRARY_PATH=~/bin/oracle

You also need to export that path to the Rust compiler so it can find the OCI libraries.

  export RUSTFLAGS="-L/Users/<your_user>/bin/oracle"

It is also convenient to have a tool to talk to our Oracle DB instance. SQL*Plus has been the traditional go-to Oracle client for a very long time, and you can download it from the same place and install it using the same process that I used for the dynamic libraries. But I'd recommend you use SQLcl instead, which is newer, easier to use, and offers additional features, like code completion, which may be useful here. I will install it using Homebrew (brew install sqlcl) to simplify the process, but you can refer to the official documentation and to Jeff's video for an intro to the tool.

Once I have connected to the database with the correct password, I can use the opportunity to alter the CDB's vector pool size, as I will need it for the second part of this article.

  ALTER SYSTEM SET vector_memory_size=512M scope=spfile;

This change has been introduced to the server parameters file and requires restarting the database so it gets read and applied. In this case, I had called my container db-free, and I restart it from the command line.

  docker container restart db-free

In order to run the script that creates the database, I will use SQLcl from the command line, connecting again to the database as SYS, but this time targeting the pluggable database rather than the container one.

  sql sys@localhost:1521/FREEPDB1 as sysdba @co_install.sql

When prompted, I will use "co" as the password for the "CO" user. Obviously, this is NOT a secure choice, but this is just a test in a container that will be discarded afterwards. The tablespace for CO will be USERS (default value), and I have safely assumed that the schema can be overwritten because this is a new container.

The script will create the tables, add the data, and exit from SQLcl. I want to connect again, this time with the CO user.

  sql co/co@localhost:1521/FREEPDB1

And make a query to verify that everything went well.

  SELECT product_id, product_name, unit_price FROM products WHERE product_name LIKE 'Girl''s Coat%';

Which should reply with a single product.

   PRODUCT_ID PRODUCT_NAME             UNIT_PRICE
_____________ _____________________ _____________
           15 Girl's Coat (Blue)            13.09

Feel free to familiarize yourself with the schema by using desc <table_name> or info <table_name> before moving on to writing some Rust code.

I will finish the database setup by configuring the user CO to work with vector search, because I will be using these changes in the second part of this article. I need to make these changes as SYSDBA, so we can start a new session as the SYS user and with the SYSDBA privilege.

  sql sys@localhost:1521/FREEPDB1 as sysdba

This is a good opportunity to verify that the change I made before has been applied.

  SHOW PARAMETER vector_memory_size;

Then, I issue the following commands that add some capabilities to the CO user.

  GRANT "DB_DEVELOPER_ROLE" TO "CO";
  ALTER USER "CO" DEFAULT ROLE ALL;
  ALTER USER "CO" QUOTA UNLIMITED ON USERS;
  EXIT;

That's it! Let's start coding!

Before any real interaction with the Oracle database instance, I need to connect to it. Sessions are created using the instance of Environment that is produced by the top level function env() that is meant to parse the Oracle Cloud Infrastructure environments.

  #[tokio::main]
  async fn main() -> Result<(), anyhow::Error> {
      println!("sibyl-test");

      let sibyl_env = sibyl::env()?;

      let session = sibyl_env
          .connect("localhost:1521/FREEPDB1", "co", "co")
          .await?;

      Ok(())
  }

WARNING:

The connection data is hard-coded in this example. DO NOT do this in real software! Instead, get those values from the environment.

And I would like to send the simplest command before closing the session: a ping. It shouldn't fail, because the connection has just been established.

  session.ping().await?;
  println!("Connected to the database.");

If we run this version with cargo run, we should get the "Connected to database" message in the output. But if you get any "Library not loaded" error mentioning libclntsh.dylib double-check your DYLD_LIBRARY_PATH before you move on. If the problem is caused by rustc and the linker ("ld: library 'clntsh' not found"), verify RUSTFLAGS.

Although the goal is to use statements to interact with the database, we can use a statement that uses the DUAL table to return a result without interacting with any actual table.

  let statement = session.prepare("SELECT 'Hello Oracle!' FROM dual").await?;
  if let Some(row) = statement.query_single(()).await? {
      let greeting: String = row.get(0)?;
      println!("{greeting}");
  } else {
      eprintln!("Greeting query failed.");
  }

In contrast with the oracle crate, sibyl requires that all SQL must be prepared first and then executed, i.e., there is no way to execute SQL directly using Session methods. However, I don't think that is a limitation in the context of an application, and I'd consider it a small burden when writing a proof of concept.

  const MAX_ROWS: usize = 20;

  let sql_customer_list = "SELECT * FROM co.customers";
  println!("Getting {MAX_ROWS} customers:");
  let stmt = session.prepare(sql_customer_list).await?;
  let rows = stmt.query(()).await?;
  let mut i: usize = 0;
  while i < MAX_ROWS
      && let Some(row) = rows.next().await?
  {
      let id: u32 = row.get(0)?;
      let email: String = row.get(1)?;
      let fullname: String = row.get(2)?;

      println!("{id}: {fullname} - {email}");
      i += 1;
  }

  let sql_product_with_id = "SELECT product_id, product_name, unit_price \
                             FROM products \
                             WHERE product_id = :1";
  let stmt = session.prepare(sql_product_with_id).await?;
  if let Some(row) = stmt.query_single(15).await? {
      let id: i32 = row.get(0)?;
      let name: String = row.get(1)?;
      let price: Option<f32> = row.get(2)?;
      println!("\nProduct 15:");
      println!("{id}: {name} - {}", price.unwrap_or(0.0));
  } else {
      eprintln!("Product query failed.");
  }

In an application, database interactions are used repeatedly. All the SQL used by sibyl is prepared in advance, and the produced Statement, which is immutable, can be reused as needed. This makes the code more consistent and saves some time and effort.

  async fn query_mr(stmt: &Statement<'_>, n_rows: usize) -> Result<(), sibyl::Error> {
      let rows = stmt.query(()).await?;
      let mut i: usize = 0;
      while i < n_rows
          && let Some(row) = rows.next().await?
      {
          let id: u32 = row.get(0)?;
          let email: String = row.get(1)?;
          let fullname: String = row.get(2)?;

          println!("{id}: {fullname} - {email}");
          i += 1;
      }

      Ok(())
  }

  println!("\nAGAIN Getting {MAX_ROWS} customers:");
  let stmt_mr = session.prepare(sql_customer_list).await?;
  let rows = stmt_mr.query(()).await?;
  let mut i: usize = 0;
  while i < MAX_ROWS
      && let Some(row) = rows.next().await?
  {
      let id: u32 = row.get(0)?;
      let email: String = row.get(1)?;
      let fullname: String = row.get(2)?;

      println!("{id}: {fullname} - {email}");
      i += 1;
  }
  println!("\nAND AGAIN Getting {MAX_ROWS} customers:");
  query_mr(&stmt_mr, MAX_ROWS).await?;
  println!("\n...AND AGAIN Getting {MAX_ROWS} customers:");
  query_mr(&stmt_mr, MAX_ROWS).await?;

  let sql_refunded_store_customer = "SELECT order_id, order_tms \
                                     FROM co.orders \
                                     WHERE order_status = 'REFUNDED' AND store_id = :store AND customer_id = :customer";
  println!("\nCustomer orders refunded in store 1 for customer 99");
  let stmt = session.prepare(sql_refunded_store_customer).await?;
  let rows = stmt.query((("customer", 99), ("store", &1))).await?;
  let mut i: usize = 0;
  while i < MAX_ROWS
      && let Some(row) = rows.next().await?
  {
      let order_id: u32 = row.get(0)?;
      let timestamp: Timestamp = row.get(1)?;

      println!("{order_id} - {timestamp:?}");
      i += 1;
  }