orax

# Orax

[![CI](https://github.com/Girg-Z/orax/actions/workflows/elixir.yml/badge.svg)](https://github.com/Girg-Z/orax/actions/workflows/elixir.yml)
[![Hex.pm](https://img.shields.io/hexpm/v/orax.svg)](https://hex.pm/packages/orax)
[![Docs](https://img.shields.io/badge/hex-docs-blue.svg)](https://hexdocs.pm/orax)
[![License](https://img.shields.io/hexpm/l/orax.svg)](https://github.com/Girg-Z/orax/blob/main/LICENSE)

Native Erlang driver and Ecto adapter for Oracle Database.

Orax speaks Oracle's TNS protocol directly from the BEAM: no Oracle Instant
Client, no OCI, no ODBC, no NIFs. Add the dependency and connect.

  * **Ecto 3 adapter**: schemas, queries, migrations, `Repo.transaction/2`,
    `Ecto.Adapters.SQL.Sandbox`, identity primary keys and `RETURNING`.
  * **Plain Erlang driver** for applications that do not use Ecto.
  * Prepared statements, positional and named binds, PL/SQL calls with `OUT`
    parameters, `SYS_REFCURSOR`, batched inserts and row prefetching.
  * TLS connections, proxy authentication and Oracle character sets.
  * `JSON` columns and `json_extract_path` translated to `JSON_VALUE`.

## Requirements

| Component      | Supported versions                     |
| -------------- | -------------------------------------- |
| Oracle Database | 19c, 26ai (23ai)                      |
| Elixir         | 1.18 or later                          |
| Erlang/OTP     | 27 or later                            |
| ecto_sql       | 3.14 or later                          |

Releases older than 19c are not supported.

## Installation

Add `orax` to your dependencies in `mix.exs`:

```elixir
def deps do
  [
    {:orax, "~> 0.1.0"}
  ]
end
```

## Quick start

Define a repository that uses the adapter:

```elixir
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Orax
end
```

and configure it:

```elixir
config :my_app, MyApp.Repo,
  hostname: "localhost",
  port: 1521,
  database: "FREEPDB1",
  username: "scott",
  password: "tiger",
  pool_size: 10
```

Then use it as any other Ecto repository:

```elixir
MyApp.Repo.all(from u in User, where: u.active, select: u.name)

MyApp.Repo.query!("SELECT sysdate FROM dual")
#=> %{num_rows: 1, rows: [[~N[2026-11-15 10:00:00]]], ...}
```

The implicit `:bigserial` primary key added by Ecto migrations is created as a
`GENERATED BY DEFAULT AS IDENTITY` column, so `mix ecto.migrate` works as
usual.

## Configuration

### Connection options

| Option            | Description |
| ----------------- | ----------- |
| `:hostname`       | Name or IP address of the database server. |
| `:port`           | Port the listener accepts connections on. |
| `:database`       | Service name, or SID prefixed by a colon (`":FREE"`). |
| `:username`       | Name of the connecting user. |
| `:password`       | Password of the connecting user. |
| `:parameters`     | Keyword list of connection parameters, see below. |
| `:socket_options` | Options given to the underlying socket. |
| `:timeout`        | Timeout of each call in milliseconds, defaults to `15000`. |

When `:timeout` expires the call is broken off and an error is returned. The
connection stays usable if the server answers the break within another
timeout. Without out-of-band breaks the server only sees the break between
operations, so a statement still running after twice the timeout closes the
connection.

### Pool options

| Option           | Description |
| ---------------- | ----------- |
| `:pool`          | Connection pool module, defaults to `DBConnection.ConnectionPool`. |
| `:pool_size`     | Number of connections, defaults to `1`. |
| `:idle_interval` | Ping interval in milliseconds for idle connections, defaults to `5000`. |

### Connection parameters

Given as a keyword list under `:parameters`:

| Parameter          | Description |
| ------------------ | ----------- |
| `:autocommit`      | Commit each statement run outside a transaction, defaults to `1`. |
| `:fetch`           | Number of rows fetched per round trip. |
| `:sdu`             | Session data unit size. |
| `:end_of_response` | Use the end of response marker when the server offers it (26ai), defaults to `true`. |
| `:charset`         | Client character set, defaults to UTF-8. |
| `:proxy_user`      | User name for proxy authentication. |
| `:description`     | Full connect descriptor, instead of host, port and database. |
| `:newpassword`     | Change the password of the connecting user. |
| `:role`            | Mode used for an internal logon. |
| `:prelim`          | Mode permitted while the database is down. |
| `:ssl`             | TLS options, see below. |

Inside `Repo.transaction/2` autocommit is always off until COMMIT or ROLLBACK.
Oracle implicitly commits DDL statements, also inside a transaction.

### TLS

Start the `:ssl` application:

```elixir
def application do
  [extra_applications: [:logger, :ssl]]
end
```

and pass the TLS options under `:ssl`:

```elixir
config :my_app, MyApp.Repo,
  # ...
  parameters: [
    ssl: [
      verify: :verify_peer,
      server_name_indication: ~c"adb.example.oraclecloud.com",
      cacerts: :public_key.cacerts_get()
    ]
  ]
```

### Quoted identifiers

By default identifiers generated by the adapter are not quoted, so Oracle
folds them to upper case, as existing Oracle schemas expect. To quote every
table, prefix, column, index and constraint name:

```elixir
config :orax, quote_identifiers: true
```

Quoted identifiers keep their case, so `"users"` and `"USERS"` are different
objects. Use the same setting for migrations and application queries.

With the option off, single identifiers can still be quoted explicitly:

```elixir
schema "\"USERS\"" do
  field :name, :string, source: :"\"NAME\""
end
```

### JSON library

`:map` fields and `JSON` columns are encoded with Elixir's built-in `JSON`.
Any module with `encode!/1` and `decode!/1` can replace it:

```elixir
config :orax, :json_library, Jason
```

## Types

| Ecto type         | Oracle type                          |
| ----------------- | ------------------------------------ |
| `:id`, `:integer` | `NUMBER(*,0)`, `INTEGER`             |
| `:float`          | `NUMBER`, `FLOAT`, `BINARY_FLOAT`    |
| `:decimal`        | `NUMBER`, `FLOAT`, `BINARY_FLOAT`    |
| `:string`         | `CHAR`, `VARCHAR2`, `CLOB`, `NCHAR`, `NVARCHAR2`, `NCLOB` |
| `:binary`         | `RAW`, `BLOB`                        |
| `:binary_id`      | `RAW`                                |
| `:boolean`        | `CHAR`, `VARCHAR2`, `NUMBER`         |
| `:map`            | `JSON`, `CLOB`, `NCLOB`              |
| `:date`           | `DATE`                               |
| `:naive_datetime` | `DATE`, `TIMESTAMP`                  |
| `:utc_datetime`   | `TIMESTAMP WITH TIME ZONE`           |

In migrations, `add :name, :string, national: true` creates an `NVARCHAR2`
column, and `comment:` on tables and columns is supported.

## Raw SQL and PL/SQL

`Repo.query/4` takes positional (`:1`, `:2`) binds. Binds whose type cannot be
inferred from the value are given with the `:in` option. `OUT` parameters are
given in place as `{:out, type}`, or with the `:out` option, which appends
them after the other binds:

```elixir
# Binary and UUID binds
Repo.query("INSERT INTO docs (id, body) VALUES (:1, :2)",
  [Ecto.UUID.bingenerate(), <<0xE7, 0x99, 0xBE>>], in: [:binary_id, :binary])

# Stored function with an OUT parameter
Repo.query("BEGIN :1 := price_of(:2); END;", [{:out, :number}, "SKU-1"])
#=> {:ok, %{rows: [[50]], ...}}

# RETURNING ... INTO
Repo.query("INSERT INTO events (at) VALUES (:1) RETURNING id INTO :2",
  [Date.utc_today()], out: [:integer])
#=> {:ok, %{rows: [[1]], ...}}

# Batched insert
Repo.query("INSERT INTO points VALUES (:1, :2)", [[1, 2], [3, 4]], batch: true)
```

For schema operations the `OUT` types of `RETURNING ... INTO` are inferred from
the schema, including identity primary keys and the IDs of nested association
inserts.

| `OUT` / `:in` type | Oracle type |
| ------------------ | ----------- |
| `:number`, `:integer`, `:float`, `:decimal` | `NUMBER`, `FLOAT`, `BINARY_FLOAT` |
| `:varchar`, `:char`, `:clob`, `:string` | `CHAR`, `VARCHAR2`, `CLOB` |
| `:nvarchar`, `:nchar`, `:nclob` | `NCHAR`, `NVARCHAR2`, `NCLOB` |
| `:raw`, `:blob`, `:binary`, `:hexstring` | `RAW`, `BLOB` |
| `:date` | `DATE` |
| `:timestamp` | `TIMESTAMP` |
| `:timestamptz` | `TIMESTAMP WITH TIME ZONE` |
| `:cursor` | `SYS_REFCURSOR` |

## Using the Erlang driver

Without Ecto, the driver is a process per connection:

```erlang
{ok, Conn} = orax:start_link([
    {host, "localhost"}, {port, 1521},
    {user, "scott"}, {password, "tiger"},
    {service_name, "FREEPDB1"}
]),

{ok, [{result_set, Columns, _, Rows}]} =
    orax:sql_query(Conn, {"SELECT id, name FROM users WHERE id > :1", [10]}),

%% Named binds
orax:sql_query(Conn, {"INSERT INTO t VALUES (:id, :dat)", [#{id => 1, dat => {2026, 1, 1}}]}),

%% PL/SQL with OUT parameters and cursors
orax:sql_query(Conn, {"BEGIN :1 := func(:2); END;", [{out, varchar}, "one hundred"]}),
orax:sql_query(Conn, {"BEGIN OPEN :1 FOR SELECT * FROM t WHERE dat > :2; END;",
                      [cursor, {2026, 1, 1}]}),

%% Batching and prefetching
orax:sql_query(Conn, {batch, "INSERT INTO t VALUES (:1, :2)", [[1, 2], [3, 4]]}),
orax:sql_query(Conn, {fetch, "SELECT * FROM t WHERE id > :1", [1]}).
```

### Character sets

In Erlang, strings are charlists and binaries are `RAW` values. Text is UTF-8
by default; the `:charset` parameter also accepts:

`:us7ascii`, `:we8iso8859p1`, `:ee8iso8859p2`, `:nee8iso8859p4`,
`:cl8iso8859p5`, `:ar8iso8859p6`, `:el8iso8859p7`, `:iw8iso8859p8`,
`:we8iso8859p9`, `:ne8iso8859p10`, `:th8tisascii`, `:vn8mswin1258`,
`:we8iso8859p15`, `:blt8iso8859p13`, `:ee8mswin1250`, `:cl8mswin1251`,
`:el8mswin1253`, `:iw8mswin1255`, `:tr8mswin1254`, `:we8mswin1252`,
`:blt8mswin1257`, `:ar8mswin1256`, `:ja16euc`, `:ja16sjis`, `:ja16euctilde`,
`:ja16sjistilde`, `:ko16mswin949`, `:zhs16gbk`, `:zht32euc`, `:zht16big5`,
`:zht16mswin950`, `:zht16hkscs`.

## Migrating from jamdb_oracle

  * Replace the `:jamdb_oracle` dependency with `:orax`.
  * Replace `Ecto.Adapters.Jamdb.Oracle` with `Ecto.Adapters.Orax`,
    `Jamdb.Oracle` with `Orax` and the Erlang modules `jamdb_oracle_*` with
    `orax_*`.
  * Move `config :jamdb_oracle, ...` to `config :orax, ...`.
  * The JSON library defaults to Elixir's `JSON`; set
    `config :orax, :json_library, Jason` to keep Jason.
  * Remove `:read_timeout` from `:parameters`.

See the [changelog](CHANGELOG.md) for the other behaviour changes.

## Development

Unit tests need no database:

```sh
mix test
```

Integration tests are tagged `:oracle` and run against an Oracle Database Free
container, as CI does:

```sh
docker run -d --name orax-test-db -p 1521:1521 -e ORACLE_PWD=OraxTest1 \
  container-registry.oracle.com/database/free:latest-lite
docker cp test/support/create_test_user.sql orax-test-db:/tmp/
docker exec orax-test-db sqlplus -s system/OraxTest1@//localhost:1521/FREEPDB1 \
  @/tmp/create_test_user.sql
mix test --include oracle
```

The connection is read from `ORAX_TEST_HOST`, `ORAX_TEST_PORT`,
`ORAX_TEST_USER`, `ORAX_TEST_PASSWORD` and `ORAX_TEST_DATABASE`.

## Credits

Orax is a fork of [jamdb_oracle](https://github.com/erlangbureau/jamdb_oracle)
by Mykhailo Vstavskyi and Sergiy Kostyushkin, who wrote the driver this project
is built on. Many thanks to them.

## License

MIT, see [LICENSE](https://github.com/Girg-Z/orax/blob/main/LICENSE).