# Oracle Connection Strings

> How API Maker connects to Oracle - a connect string, a username and a password kept as three keys of the secret, Easy Connect examples, the privilege of an instance, and the common errors.

Source: https://docs.apimaker.dev/v4/docs/Database-connection-string/oracledb-connection-strings.html

An Oracle instance takes three keys of the secret : the connect string, the username and the password. The connect string goes to the client libraries of Oracle as it is, so it can be an Easy Connect string, the name of an entry of `tnsnames.ora`, or a whole connect descriptor.

## The format

**In the default secret**

```typescript
connectionString: {
    oracle: 'host:port/service_name',
    oracle_username: 'app',
    oracle_password: 'password',
},
```

| Part | What it is |
|---|---|
| `host:port` | The server, `1521` when no port is given. |
| `/service_name` | The service of the database : a pluggable database such as `ORCLPDB1`, or `FREEPDB1` of Oracle Free. |
| The username and the password | Two other keys of the secret. Any names work : you pick them on the instance form. |

The **Privilege** of the instance (`SYSDBA`, `SYSOPER`…) is a setting of the instance, not of the secret.

## Examples

```text
# 1. Oracle Free on the same server
127.0.0.1:1521/FREEPDB1

# 2. A pluggable database
10.0.0.5:1521/ORCLPDB1

# 3. A listener on another port
10.0.0.5:1522/ORCLPDB1

# 4. A whole connect descriptor
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.5)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))

# 5. An Oracle RAC through its SCAN address
scan.example.com:1521/sales.example.com
```

## Add it to API Maker

An instance never holds the connection string itself : it names a key of your [default secret](https://docs.apimaker.dev/v4/docs/secrets/secrets.html), where the string stays encrypted, out of Git.

1. **Put the string in the secret** **Security → Secret Management** → `Default`. Add it under `common.connectionString`, with a name of your choice, and save.
2. **Create the instance** **APIs & Logic → Instance API (DB API)** → add an instance : pick the database type, give it a name, and pick the key of the secret under **Connection**.
3. **Test it** **Test Instance** connects with the string. An instance which can not reach its database is not saved, and the form says why.

**Connection String Sandbox** is optional : a second key, for code which runs in a sandbox and reaches the database by another address than API Maker does.

For Oracle, the form asks for three keys of the secret, the connect string, the username and the password, and for the privilege.

## Common errors

| What you see | What to check |
|---|---|
| `ORA-01017: invalid username/password` | The username and the password keys, and the privilege of the instance. |
| `ORA-12514` : the listener does not know the service | The service name : `lsnrctl status` on the server lists them. A pluggable database has its own. |
| `ORA-12541` : no listener, or a timeout | The listener runs, the firewall lets API Maker in, the port is right. |
| `DPI-1047` : the client libraries can not be loaded | The Oracle Instant Client is missing on the server of API Maker. The install script installs it in `/opt/oracle/instantclient`. |

## Related

- [Secret Management](https://docs.apimaker.dev/v4/docs/secrets/secrets.html) · [Execute plain query](https://docs.apimaker.dev/v4/docs/apis-all/system-apis/system-generated-execute-plain-query-api.html) · [Easy Connect in the Oracle manual](https://docs.oracle.com/en/database/oracle/oracle-database/23/netag/configuring-naming-methods.html)