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:
- Oracle table definition:
CREATE TABLE "FIN"."VENDOREMAIL"
(
"VENDOR" CHAR(9 BYTE) NOT NULL ENABLE,
"E_MAIL_ADDRESS" CHAR(60 BYTE) NOT NULL ENABLE
)
ALTER TABLE "FIN"."VENDOREMAIL" ADD CONSTRAINT "VENDOR_PK" PRIMARY KEY ("VENDOR") - Input Json in update step:
{
"table": "FIN.VENDOREMAIL",
"columns":
{
"e_mail_address": " " ******email is a whitespace (length 1)
},
"where": "vendor = '12345'"
} - Sql Generated in Update step:
UPDATE "FIN"."VENDOREMAIL"
SET "E_MAIL_ADDRESS" = CAST(NULL AS CHAR(60)) ****** casting empty space as null and leading to below error
WHERE vendor = '12345' - OPA error: Error : 1407, Position : 32 Error Msg = ORA-01407: cannot update ("FIN"."VENDOREMAIL"."E_MAIL_ADDRESS") to NULL
#Oracle, #PL/SQL
Respectfully,
-SG
