ORA01858 - a non-numeric character was found where a numeric was expected

News Knowledge Base
,
1

ORA01858 - a non-numeric character was found where a numeric was expected when executing a select statement using the execute query activity of UiPath.database.activities

Issue Description

The Execute Query activity in Studio fails with:

"ORA-01858: a non-numeric character was found where a numeric was expected"

The same query works in other tools like PL/SQL Developer or SQL Studio.

Root Cause

A mismatch between the date formats or language settings used by Studio and the working application.


Resolution

  1. Check current NLS settings in both Studio and the working application: 
    select parameter, value 
from nls_session_parameters
where parameter in ('NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE');
    1. Insert an Alter Session command in a new Execute Query activity before the main query to match the working format: 
      alter session set nls_date_format = '<working_format>';

    or 

    alter session set nls_date_language = '<working_format>';