Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Replace Null with white space for not-null column in Oracle table

avatar
New Contributor

Hello, I want to copy record from one table to another table that has the same structure in Oracle 12c+.

One not-null column in source has white space. When trying to insert record to the target table, I got error "cannot insert NULL", so I added UpdateRecord processor to replace Null with white space, but still have the same error.

It works if I replace Null with any other character like "_". Is there any way to keep white space when inserting to the target table?

Below is my Flowfile and the UpdateRecord properties. Thank you

Screenshot 2024-08-28 194121.pngScreenshot 2024-08-28 194135.png

 

1 ACCEPTED SOLUTION

avatar
Super Guru

Hi @xtd ,

I did not try this but I think what is happening is that when you call the PutDatabaseRecord using CSVReader it doesnt differentiate null and empty string value and it will assume the later is also null and that is why you get the error. Have you tried converting csv into json and then do update record to see if that makes any difference? Another option is to use PutSql so that you can write your own sql but this would be cumbersome in case you have many fields.

View solution in original post

2 REPLIES 2

avatar
Super Guru

Hi @xtd ,

I did not try this but I think what is happening is that when you call the PutDatabaseRecord using CSVReader it doesnt differentiate null and empty string value and it will assume the later is also null and that is why you get the error. Have you tried converting csv into json and then do update record to see if that makes any difference? Another option is to use PutSql so that you can write your own sql but this would be cumbersome in case you have many fields.

avatar
New Contributor

Thank you so much, it works perfectly