NCLOB Data Type from SQL/SAP HANA Studio fails in UiPath.Database.Activities

News Knowledge Base
1

Execute Query activity is failing to run the given query and throwing Arithmetic operation resulted in an overflow

Issue Description

Workflow Failure Due to Unsupported Data Type:

When executing a SQL query on a table column with the NLCOB data type, you may encounter the following error in UiPath:

"Arithmetic operation resulted in an overflow"

Root Cause

This error typically occurs because the NLCOB data type (used in databases like SAP HANA or other SQL-based systems) is not natively supported by UiPath's Database activities. UiPath may fail to interpret or process this data type correctly, leading to workflow crashes.

Resolution

To resolve this issue, you need to explicitly convert the NLCOB column to a supported string format, such as NCHAR, within your SQL query. This can be done using the TO_NCHAR() function, which safely transforms the NLCOB data into a readable format for UiPath.

The TO_NCHAR function in SAP HANA SQL is a conversion function used to convert a TEXT expression, date, or number into the NTEXT data type, often in a specified format. This function is primarily utilized for formatting output data.

Syntax:

TO_NCHAR( string-expression [, source-charset-name ] )
Purpose:
  • Converting TEXT expressions:
    It can convert a TEXT string into an NTEXT string, optionally specifying the source character set name if needed for correct interpretation.
  • Converting Dates:
    It can convert DATE values into NTEXT strings, allowing for format specification to control the output representation of the date (e.g., 'YYYY-MM-DD', 'DD/MM/YYYY').
  • Converting Numbers:
    It can convert NUMBER values into NTEXT strings, enabling formatting for numerical output (e.g., adding currency symbols, controlling decimal places).

Example Queries

Example 1

Suppose your table is named CustomerData and the problematic column is CustomerNotes (of type NLCOB). You can modify your query like this:

SELECT TO_NCHAR(CustomerNotes) AS CustomerNotes, CustomerID, CustomerName
FROM CustomerData;

This query converts the CustomerNotes column to NCHAR, allowing UiPath to process it without errors.


Example 2

SELECT TO_NCHAR(CURRENT_DATE, 'YYYY-MM-DD') AS FormattedDate FROM DUMMY;
This example would convert the current date into an NTEXT string formatted as 'YYYY-MM-DD'.
Note: While TO_NCHAR exists for conversion, SAP HANA generally recommends using VARCHAR/NVARCHAR instead of CHAR/NCHAR for column definitions, as the latter are not fully supported and have different blank padding semantics. However, TO_NCHAR remains a valid function for converting data types for output or specific processing needs.