cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

sgud
Deputy Chef II
Deputy Chef II

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:

  1. 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")
  2. Input Json in update step:
    {
      "table": "FIN.VENDOREMAIL",
      "columns":
         {
            "e_mail_address": " " ******email is a whitespace (length 1)
          },
      "where": "vendor = '12345'"
    }
  3. 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'
  4. OPA error: Error : 1407, Position : 32 Error Msg = ORA-01407: cannot update ("FIN"."VENDOREMAIL"."E_MAIL_ADDRESS") to NULL

    #Oracle, #PL/SQL

    Respectfully,
    -SG
4 REPLIES 4

shivakumara
Executive Chef I
Executive Chef I

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

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.

shivakumara
Executive Chef I
Executive Chef I

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

 

SQL.png

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 upsertoracle upsert