> For the complete documentation index, see [llms.txt](https://docs.appstrategy.com/apprules-r-documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/oracle.md).

# Oracle

Connects to Oracle Database using the Oracle managed driver (ODP.NET). No Oracle client installation is required. SQL is sent to the database as written, so the full Oracle SQL dialect is available.

#### Connection Properties

**Connection**

| Property          | Default | Notes                                                                                     |
| ----------------- | ------- | ----------------------------------------------------------------------------------------- |
| Server            | —       | Host name or IP address of the Oracle server. Required unless TNS Name is set             |
| Port              | `1521`  | Listener port                                                                             |
| Service Name      | —       | Oracle service name, e.g. `ORCLPDB1`. Recommended                                         |
| SID               | —       | Legacy system identifier. Used only when Service Name is empty                            |
| TNS Name          | —       | Alias defined in `tnsnames.ora`. When set, Server, Port, Service Name and SID are ignored |
| Timeout           | `30`    | Connection timeout, seconds                                                               |
| Query Passthrough | `true`  | `true`: SQL is sent to Oracle unchanged. `false`: see **Supported SQL** below             |

One of **TNS Name**, **Service Name** or **SID** is required. They are used in that order of priority.

When using **TNS Name**, the `tnsnames.ora` file must be reachable by the driver (for example through the `TNS_ADMIN` environment variable).

**Connection Pooling**

| Property      | Default | Notes                                       |
| ------------- | ------- | ------------------------------------------- |
| Pooling       | `true`  | Reuse database connections between requests |
| Min Pool Size | `1`     | Connections kept open in the pool           |
| Max Pool Size | `100`   | Maximum connections in the pool             |

**Authentication**

**Auth Scheme** selects the mechanism: `Password` (default), `OracleWallet`, `OS`, `Kerberos` or `LDAP`.

| Property        | Used by                  | Notes                                                                                          |
| --------------- | ------------------------ | ---------------------------------------------------------------------------------------------- |
| User            | Password, LDAP, Kerberos | Oracle user name. Required for Password and LDAP                                               |
| Password        | Password, LDAP           | Required for Password and LDAP                                                                 |
| Wallet Location | OracleWallet, Use SSL    | Folder containing the Oracle wallet (`cwallet.sso` / `ewallet.p12`). Required for OracleWallet |
| Use SSL         | Any                      | Use the wallet in Wallet Location for TLS connections                                          |

* `Password` — user name and password checked by the database.
* `OracleWallet` — credentials are taken from the wallet; no password is entered.
* `OS` — connects as the operating system account running appRules (externally identified user).
* `Kerberos` — uses the Kerberos ticket of the account running appRules.
* `LDAP` — user name and password of a directory-managed user.

**Schema**

| Property          | Default | Notes                                                                              |
| ----------------- | ------- | ---------------------------------------------------------------------------------- |
| Browsable Schemas | —       | Comma-separated list of schemas (owners) to expose. Empty = all non-system schemas |
| Tables            | —       | Comma-separated list of tables to expose. Empty = all                              |
| Views             | —       | Comma-separated list of views to expose. Empty = all                               |

**Logging**

| Property  | Default                                                                     | Notes                         |
| --------- | --------------------------------------------------------------------------- | ----------------------------- |
| Log Level | `0`                                                                         | `0` disabled, `1` log to file |
| Log File  | `%APPDATA%\appStrategy\PortalData\Default\Logs\Connectors\Oracle_Debug.log` |                               |

#### Supported SQL

**Tables and columns**

* Tables and views are addressed as `TABLE` or `SCHEMA.TABLE`.
* Oracle stores unquoted names in upper case: `customers` and `CUSTOMERS` are the same table. Use double quotes only for names created in mixed case: `"MyTable"`.
* Primary keys, foreign keys and indexes are read from the database.

**SELECT**

Queries are executed by Oracle as written. Everything the database supports is available: joins, sub-queries, `GROUP BY`, aggregate and analytic functions, `UNION`, hints, `FETCH FIRST n ROWS ONLY`, and so on.

```sql
SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = :dept ORDER BY LAST_NAME
SELECT d.DEPARTMENT_NAME, COUNT(*) AS HEADCOUNT
FROM HR.EMPLOYEES e JOIN HR.DEPARTMENTS d ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME
SELECT * FROM ORDERS ORDER BY ORDER_DATE DESC FETCH FIRST 50 ROWS ONLY
```

**INSERT, UPDATE, DELETE**

```sql
INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME, COUNTRY) VALUES (:id, :name, :country)
UPDATE CUSTOMERS SET COUNTRY = 'IE' WHERE CUSTOMER_ID = :id
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = :id
```

* With **Query Passthrough** `true` (default), statements are sent unchanged. DDL (`CREATE`, `ALTER`, `DROP`, `TRUNCATE`), `MERGE` and PL/SQL blocks (`BEGIN … END;`) can also be executed.
* With **Query Passthrough** `false`, INSERT, UPDATE and DELETE are parsed and rebuilt with bind variables and upper-case quoted names before being sent. SELECT and all other statements are still sent unchanged.
* Saving records through a workflow target requires the table to have a primary key (used to locate the row to update or delete).

**Parameters**

Oracle bind variables use a colon: `:name`. The `@name` form is also accepted and converted to `:name`. Parameters are bound by name, so their order does not matter.

```sql
SELECT * FROM ORDERS WHERE CUSTOMER_ID = :customer AND ORDER_DATE >= :from
```

**Transactions**

Transactions are supported and are handled by the database.

#### Data Types

| Oracle type                                               | Read as                    |
| --------------------------------------------------------- | -------------------------- |
| `CHAR`, `NCHAR`, `VARCHAR2`, `NVARCHAR2`, `LONG`, `ROWID` | Text                       |
| `CLOB`, `NCLOB`, `XMLTYPE`, `JSON`                        | Text                       |
| `NUMBER`, `FLOAT`, `BINARY_FLOAT`, `BINARY_DOUBLE`        | Decimal                    |
| `DATE`, `TIMESTAMP`, `TIMESTAMP WITH (LOCAL) TIME ZONE`   | Date/time                  |
| `INTERVAL YEAR TO MONTH`, `INTERVAL DAY TO SECOND`        | Time span                  |
| `BLOB`, `RAW`, `LONG RAW`, `BFILE`                        | Binary                     |
| `VECTOR` (Oracle 23ai)                                    | Text, e.g. `[0.1,0.2,0.3]` |

`JSON` and `VECTOR` values are written as text. Vectors are converted with `TO_VECTOR()` when the record is saved.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.appstrategy.com/apprules-r-documentation/platform/platform-features/system-settings/data-sources/connection-settings/databases/oracle.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.