topic Re: Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert in Workato Pros Discussion Board

Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert

sgud — Thu, 04 Jul 2024 05:56:34 GMT

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

Re: Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert

shivakumara — Fri, 05 Jul 2024 10:09:45 GMT

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

Re: Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert

sgud — Fri, 05 Jul 2024 14:10:33 GMT

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.

Re: Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert

shivakumara — Mon, 08 Jul 2024 05:31:28 GMT

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

Re: Oracle SQL Connector is converting whitespace to NULL in Update/Insert/Upsert

sgud — Thu, 25 Jul 2024 19:52:54 GMT

shivakumara,

I am using Oracle connector and upsert action.

Here is the documentation link: https://docs.workato.com/connectors/oracle/upsert.html#upsert-row

oracle upsert