# Using the sql() driver with an Oracle database

The Oracle sql destination has some special aspects that are important to note.

  * The hostname of the database server is set in the `tnsnames.ora` file, not in the `host` parameter of the `sql()` destination.

If the `tnsnames.ora` file is not located in the /etc directory (or in the /var/opt/oracle directory on Solaris), set the following Oracle-related environment variables, so AxoSyslog will find the file: `ORACLE_BASE`, `ORACLE_HOME`, and `ORACLE_SID`. For details, see the documentation of the Oracle Instant Client.

  * You cannot use the same `database()` settings in more than one destination, because the `database()` option of the SQL driver is just a reference to the connection string of the `tnsnames.ora` file. To overcome this problem, you can duplicate the connections in the `tnsnames.ora` file under a different name, and use a different table in each Oracle destination in AxoSyslog.

  * As certain database versions limit the maximum length of table names, macros in the table names should be used with care.

  * In the current version of AxoSyslog, the types of database columns must be explicitly set for the Oracle destination. The column used to store the text part of the syslog messages should be able to store messages as long as the longest message permitted by `syslog-ng`, therefore it is usually recommended to use the `varchar2` or `clob` column type. (The maximum length of the messages can be set using the `log-msg-size()` option.) For details, see the following example.

  * The Oracle Instant Client used by AxoSyslog supports only the following character sets:

    * Single-byte character sets: `US7ASCII, WE8DEC, WE8MSWIN1252, and WE8ISO8859P1`

    * Unicode character sets: `UTF8, AL16UTF16, and AL32UTF8`




## Example: Using the sql() driver with an Oracle database

The following example sends the log messages into an Oracle database running on the `logserver` host, which must be set in the `/etc/tnsnames.ora` file. The messages are inserted into the `LOGS` database, the name of the table includes the exact date when the messages were sent. The AxoSyslog application automatically creates the required tables and columns, if the user account used to connect to the database has the required privileges.
```
 
       destination d_sql {
          sql(type(oracle)
          username("syslog-ng") password("password")
          database("LOGS")
          table("msgs_${R_YEAR}${R_MONTH}${R_DAY}")
          columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar2")
          values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
          indexes("datetime", "host", "program", "pid", "message"));
        };
    
```

The Oracle Instant Client retrieves the address of the database server from the `/etc/tnsnames.ora` file. Edit or create this file as needed for your configuration. A sample is provided below.
```
 
       LOGS =
        (DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)
        (HOST = logserver)
        (PORT = 1521))
        )
        (CONNECT_DATA =
        (SERVICE_NAME = EXAMPLE.SERVICE)
        )
        )
    
```

Last modified July 2, 2023: [Change highlight mode of code examples (2f8a9593)](<https://github.com/axoflow/axosyslog-core-docs/commit/2f8a95937c6498193e7168ce8b0dc831e9f0f8ad>)