Hi All,
I have a flow setup where I am fetching some data from an Oracle DB.
Some of the columns of the table are in Oracles Number type, and I need to put them into a Postgres DB as floats.
If I look at the Schema that is being created on read from Oracle it looks like it's the data that's being defined as a type of "Bytes" with a logical type of "decimal" that I am talking about.
Currently I am using the following flow as I need to add some additional update onConflict logic to the Postgres insert to handle updates.
Oracle -> Convert JSON -> Split Record -> Convert JSON to SQL -> Replace Text -> Execute SQL
But at the moment this throws an error when trying to do the insert into Postgres, which seems to be related to the number not being coerced into an actual number and rather been empty in the SQL.
Anyone got any ideas/suggestion on how I can force the type of the numerical types to be a float in the Avro and then a number type once converted to the JSON?
Thanks
Here's an example of the error:
java.sql.SQLDataException: The value of the sql.args.35.value is '', which cannot be converted into the necessary data type
And here's what the data looks like once converted to JSON:
[{
"LASTUPDATED": "18-MAR-20 11:58:11 AM",
"LASTUPDATEDRAW": 1584532691000,
"CONTRACTID": "REDACTED",
"CONTRACTDESCRIPTION": "REDACTED",
"UCCONTRACTDESCRIPTION": "REDACTED",
"DATECREATED": 1581984000000,
"PONUMBER": null,
"STATUS": "OPEN",
"TYPE": "CONTRACT",
"PULLDATE": 1581984000000,
"SHIPDATE": 1581984000000,
"BACKATWHDATE": 1584662400000,
"AVAILTORENTDATE": 1584662400000,
"CHARGESTART": 1582070400000,
"CHARGEEND": 1582675200000,
"PROBABILITY": {
"bytes": "d"
},
"PROJECTMANAGERID": "REDACTED",
"SALESPERSONID": "REDACTED",
"FINANCESIGNOFF": "Good to Ship",
"CARNETREQUIRED": "No",
"OPERATIONSSIGNOFF": "Good to Ship",
"TIGHTTURNAROUNDS": null,
"PROJECTSSIGNOFF": "Good to Ship",
"OUTOFHOURS": null,
"SALESSIGNOFF": "Good to Ship",
"DRIVERDETAILS": null,
"ACCOUNTFORMS": null,
"SHIPWITHIN7DAYS": "NO",
"LEDCALCS": null,
"PREPBAY": null,
"LEDDRAWINGS": null,
"RAMS": null,
"CREW": null,
"SIGNININVITE": null,
"TECHRUNTHROUGH": "NO",
"SUBHIRES": null,
"DEADLINEFLAG": null,
"CUSTOMPACKAGING": null,
"CUSTOMWORK": null,
"CONTACTID": "REDACTED",
"CONTACTNAME": "REDACTED",
"CUSTOMERID": "REDACTED",
"COMPANYNAME": "REDACTED",
"ITEMAMOUNT": {
"bytes": "\u0011ß"
},
"ITEMDISCOUNT": {
"bytes": "\u0000"
},
"TOTALITEMSTAXAMOUNT": {
"bytes": "\u0003\u0093"
},
"LABORAMOUNT": {
"bytes": "\u0001^"
},
"LABORDISCOUNT": {
"bytes": "\u0000"
},
"TOTALLABORTAXAMOUNT": {
"bytes": "F"
},
"PROFITCENTRE": "REDACTED",
"SALESPERSON": "REDACTED",
"PROJECTMANAGER": "REDACTED",
"TAXREGION": "REDACTED",
"ORDERAMOUNT": {
"bytes": "\u0017\u0016"
},
"ORDERDISCOUNT": {
"bytes": "\u0000"
},
"ORDERTAX": {
"bytes": "\u0003Ù"
},
"FREIGHTAMOUNT": {
"bytes": "\u0000"
},
"MISCCHARGESAMOUNT": {
"bytes": "d"
},
"LOSTREASON": null,
"COUNTRY": "United Kingdom",
"VERSION": {
"bytes": "\u0001"
},
"RETURNVIA": "Customer",
"MAINORDERCOMMENTS": null,
"PAYMETHODREFERENCE": null,
"QUOTECONVERTEDDATE": 1581984000000,
"SITECURRENCYID": "GBP",
"BASECURRENCYID": "GBP",
"TRANSACTIONCURRENCYID": "GBP",
"TRANSACTIONAMOUNT": {
"bytes": "\u0017\u0016"
},
"SITEAMOUNT": {
"bytes": "\u0017\u0016"
},
"BASEAMOUNT": {
"bytes": "\u0017\u0016"
},
"PAYMENTTERMSID": "NET30-RETURN"
}]
And this is the Schema being read from the Oracle DB
{
"type": "record",
"name": "REP_ORDERHEADERUPUDFVIEW",
"namespace": "R2",
"fields": [{
"name": "LASTUPDATED",
"type": ["null", "string"]
}, {
"name": "LASTUPDATEDRAW",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "CONTRACTID",
"type": ["null", "string"]
}, {
"name": "CONTRACTDESCRIPTION",
"type": ["null", "string"]
}, {
"name": "UCCONTRACTDESCRIPTION",
"type": ["null", "string"]
}, {
"name": "DATECREATED",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "PONUMBER",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 15,
"scale": 0
}]
}, {
"name": "STATUS",
"type": ["null", "string"]
}, {
"name": "TYPE",
"type": ["null", "string"]
}, {
"name": "PULLDATE",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "SHIPDATE",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "BACKATWHDATE",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "AVAILTORENTDATE",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "CHARGESTART",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "CHARGEEND",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "PROBABILITY",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "PROJECTMANAGERID",
"type": ["null", "string"]
}, {
"name": "SALESPERSONID",
"type": ["null", "string"]
}, {
"name": "FINANCESIGNOFF",
"type": ["null", "string"]
}, {
"name": "CARNETREQUIRED",
"type": ["null", "string"]
}, {
"name": "OPERATIONSSIGNOFF",
"type": ["null", "string"]
}, {
"name": "TIGHTTURNAROUNDS",
"type": ["null", "string"]
}, {
"name": "PROJECTSSIGNOFF",
"type": ["null", "string"]
}, {
"name": "OUTOFHOURS",
"type": ["null", "string"]
}, {
"name": "SALESSIGNOFF",
"type": ["null", "string"]
}, {
"name": "DRIVERDETAILS",
"type": ["null", "string"]
}, {
"name": "ACCOUNTFORMS",
"type": ["null", "string"]
}, {
"name": "SHIPWITHIN7DAYS",
"type": ["null", "string"]
}, {
"name": "LEDCALCS",
"type": ["null", "string"]
}, {
"name": "PREPBAY",
"type": ["null", "string"]
}, {
"name": "LEDDRAWINGS",
"type": ["null", "string"]
}, {
"name": "RAMS",
"type": ["null", "string"]
}, {
"name": "CREW",
"type": ["null", "string"]
}, {
"name": "SIGNININVITE",
"type": ["null", "string"]
}, {
"name": "TECHRUNTHROUGH",
"type": ["null", "string"]
}, {
"name": "SUBHIRES",
"type": ["null", "string"]
}, {
"name": "DEADLINEFLAG",
"type": ["null", "string"]
}, {
"name": "CUSTOMPACKAGING",
"type": ["null", "string"]
}, {
"name": "CUSTOMWORK",
"type": ["null", "string"]
}, {
"name": "CONTACTID",
"type": ["null", "string"]
}, {
"name": "CONTACTNAME",
"type": ["null", "string"]
}, {
"name": "CUSTOMERID",
"type": ["null", "string"]
}, {
"name": "COMPANYNAME",
"type": ["null", "string"]
}, {
"name": "ITEMAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "ITEMDISCOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "TOTALITEMSTAXAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "LABORAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "LABORDISCOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "TOTALLABORTAXAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "PROFITCENTRE",
"type": ["null", "string"]
}, {
"name": "SALESPERSON",
"type": ["null", "string"]
}, {
"name": "PROJECTMANAGER",
"type": ["null", "string"]
}, {
"name": "TAXREGION",
"type": ["null", "string"]
}, {
"name": "ORDERAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "ORDERDISCOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "ORDERTAX",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "FREIGHTAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "MISCCHARGESAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "LOSTREASON",
"type": ["null", "string"]
}, {
"name": "COUNTRY",
"type": ["null", "string"]
}, {
"name": "VERSION",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 3,
"scale": 0
}]
}, {
"name": "RETURNVIA",
"type": ["null", "string"]
}, {
"name": "MAINORDERCOMMENTS",
"type": ["null", "string"]
}, {
"name": "PAYMETHODREFERENCE",
"type": ["null", "string"]
}, {
"name": "QUOTECONVERTEDDATE",
"type": ["null", {
"type": "long",
"logicalType": "timestamp-millis"
}]
}, {
"name": "SITECURRENCYID",
"type": ["null", "string"]
}, {
"name": "BASECURRENCYID",
"type": ["null", "string"]
}, {
"name": "TRANSACTIONCURRENCYID",
"type": ["null", "string"]
}, {
"name": "TRANSACTIONAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "SITEAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "BASEAMOUNT",
"type": ["null", {
"type": "bytes",
"logicalType": "decimal",
"precision": 10,
"scale": 0
}]
}, {
"name": "PAYMENTTERMSID",
"type": ["null", "string"]
}]
}
