โ07-03-2024 10:39 PM - edited โ07-03-2024 10:56 PM
Hi memrbers,
The Workato connector for Oracle SQL is converting whitespace to NULL values when inserting, updating, or upserting data into a table.
Specifically, this column (e_mail_address, char (60) ) is defined as NOT NULL in the table schema, and it is part of a legacy system that cannot be modified easily.
To work around this limitation, we are using whitespace (" ") with length 1 as a placeholder.
Workato currently converts both NULL and empty strings (e.g., CAST(NULL AS CHAR(60))) to NULL values, which aligns with Oracleโs handling of empty or NULL values.
But whitespace, it should cast it as : CAST(' ' AS CHAR(60)) to preserve the input value. But right now it is casting as NULL which is failing the (NOT NULL) constraint on the table.
Is it a bug in the Oracle SQL Connector? Is there a workaround to prevent this?
Additional details:
โ07-05-2024 03:09 AM
Hi @sgud ,
Since you have created table with "NOT NULL" constraint, hence email address always expects some values
The Oracle database does not support empty string values in text columns.
Either you can decide what should be the default value to be passed instead of Empty values.
Thanks and Regards,
Shivakumara Avadhani
โ07-05-2024 07:08 AM - edited โ07-05-2024 07:10 AM
Thanks Shivakumara , for responding.
I am not passing an empty string ("") but a single whitespace character (" " with a length of 1). This approach works fine in SQL Script/Developer. However, the Workato Oracle connector does not respect the whitespace (default value) and passes null.
โ07-07-2024 10:31 PM
Hi @sgud ,
I m just curious on which connector do you use for this task? I tried with below inbuilt connector of Workato i.e
which will help in doing it from my end.
Thanks and Regards,
Shivakumara Avadhani
โ07-25-2024 12:46 PM - edited โ07-25-2024 12:52 PM
shivakumara,
I am using Oracle connector and upsert action.
Here is the documentation link: https://docs.workato.com/connectors/oracle/upsert.html#upsert-row