Using Oracle db26ai from Rust with the oracle 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 oracle crate, which is based on ODPI-C. That means that the ODPI-C dynamic libraries must be installed and available in 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

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 instead of 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!

The Connection instance provides simple methods for executing queries directly. The simplest one is query(), which takes a string containing the SQL select statement and and a reference to an array with the arguments of the query, if any. Let's go step by step.

  const MAX_ROWS: usize = 20;

  let sql_customer_list = "SELECT * FROM co.customers";
  println!("Getting {MAX_ROWS} customers:");
  for row in connection
      .query(sql_customer_list, &[])?
      .take(MAX_ROWS)
  {
      let row = row?;
      let id: u32 = row.get(0)?;
      let email: String = row.get(1)?;
      let fullname: String = row.get(2)?;

      println!("{id}: {fullname} - {email}");
  }

  let sql_product_with_id = "SELECT product_id, product_name, unit_price \
                             FROM products \
                             WHERE product_id = :1";
  let row = connection.query_row(sql_product_with_id, &[&15])?;
  let (id, name, price) = row.get_as::<(i32, String, Option<f32>)>()?;
  println!("\nProduct 15:");
  println!("{id}: {name} - {}", price.unwrap_or(0.0));

It is quite common in an application to repeatedly perform a set of operations. This can be achieved by either running direct queries or by preparing statements and reusing them to save time and effort.

  {
      println!("\nAGAIN Getting {MAX_ROWS} customers:");
      let mut stmt = connection
          .statement(sql_customer_list)
          .tag("customer list")
          .build()?;
      for row in stmt.query_as::<(u32, String, String)>(&[])?.take(MAX_ROWS) {
          if let Ok((id, email, fullname)) = row {
              println!("{id}: {fullname} - {email}");
          }
      }
      println!("\nAND AGAIN Getting {MAX_ROWS} customers:");
      for row in stmt.query(&[])?.take(MAX_ROWS) {
          let row = row?;
          let (id, email, fullname) = row.get_as::<(u32, String, String)>()?;
          println!("{id}: {fullname} - {email}");
      }
  }

  println!("\n...AND AGAIN Getting {MAX_ROWS} customers:");
  let mut stmt = connection.statement("").tag("customer list").build()?;
  query_mr(&mut stmt, MAX_ROWS)?;

  fn query_mr(stmt: &mut Statement, n_rows: usize) -> Result<(), oracle::Error> {
    for row in stmt.query(&[])?.take(n_rows) {
        let row = row?;
        let id: u32 = row.get(0)?;
        let email: String = row.get(1)?;
        let fullname: String = row.get(2)?;

        println!("{id}: {fullname} - {email}");
    }

    Ok(())
  }

  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 mut stmt = connection
      .statement(sql_refunded_store_customer)
      .tag("refunds")
      .build()?;
  for row in stmt.query_named(&[("customer", &99), ("store", &1)])? {
      let row = row?;
      let (order_id, timestamp) = row.get_as::<(u32, Timestamp)>()?;
      println!("{order_id} - {timestamp}");
  }