Fix Oracle 'ORA-01861' Error on Tableau Server for Linux

On Tableau Server running on Linux, an Oracle data source that works in Tableau Desktop may fail only on the server with the following error.

ORA-01861: literal does not match format string

Root Cause

This error occurs when a query converts a string to a date without a format mask (implicit conversion). Oracle uses the session's NLS_DATE_FORMAT for this conversion, and the value depends on the connecting client's locale. A Korean Windows environment applies RR/MM/DD, while Tableau Server on Linux with an English locale applies DD-MON-RR, so the same query fails only on the server. Implicit conversions inside a view definition also run under the calling session, so the error occurs even when Tableau reads the column as a string.

Tableau Server on Linux records the system locale at installation time in the file below and uses it when starting its services. Changing the OS locale after installation does not affect the services. The Oracle JDBC driver also ignores the NLS_LANG environment variable.

~tableau/.config/systemd/tableau_server.conf.d/10-lang.conf

To check the current session values, publish a sheet built on the following custom SQL to the server.

SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_DATE_FORMAT')

Solution

There are two ways to resolve this issue.

Option 1. Change the Tableau Server service locale
This option suits environments with many queries or views to fix. Change the locale in 10-lang.conf to ko_KR.UTF-8 and restart the services. The procedure follows the forward proxy configuration steps in the official Tableau documentation.

tsm stop
sudo su -l tableau
sed -i 's/^LANG=.*/LANG=ko_KR.UTF-8/' ~/.config/systemd/tableau_server.conf.d/10-lang.conf
exit
sudo /opt/tableau/tableau_server/packages/scripts.<version>/stop-administrative-services
sudo /opt/tableau/tableau_server/packages/scripts.<version>/start-administrative-services
tsm restart

After applying, the check sheet should return KOREAN, KOREA, and RR/MM/DD.

Option 2. Use explicit conversion in SQL
Specifying the format in views or custom SQL makes the query behave the same regardless of the server environment.

WHERE date_col >= TO_DATE('2020-01-01', 'YYYY-MM-DD')

When using a string column as a date in Tableau, use DATEPARSE instead of changing the data type directly. It is sent to Oracle as TO_DATE with an explicit format.

DATEPARSE("yyyy-MM-dd", [CLOSE_DATE])