Support Questions

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

Oracle CLOB not support by any prosessor in NiFi

avatar
Rising Star

Hi,

I have an Oracle table that has a CLOB. I can't pull them back using any of the processors that I try. Any way around this other than using SQOOP?

NiFI version 1.2.0.3.0.1.1-5

Cheers

3 REPLIES 3

avatar
Master Guru

NiFi processors should be able to handle CLOB, can you please provide an error that you received and which processor it came from?

avatar
Contributor

Hi @David Sheard, @Bryan Bende, @Steven O'Neill,

Myself and a colleague of mine noticed a similar behaviour with BLOB's and a legacy Oracle database we were using (Community link here). To extract the data we built an external Java program (jdbc + ResultSets) to get the BLOB bytes and write files to disk for NiFi ingestion. Alternatively, using NiFi's ExecuteScript processor and Groovy's database helper libraries the entire process can be managed through NiFi (or whatever scripting language you are comfortable with). Whichever of the above techniques you choose, you will still have to maintain your own business logic to manage your delta records. Not the most elegant solution but we just could not get the bundled NiFi processers to play nicely with the Oracle BLOB's.

avatar
Rising Star

Thanks for the feedback.

We used both QueryDatabaseTable and ExecuteSQL. The CLOB is large and our quick and dirty workaround is to create a view but I had to limit the column to 1000 (see below) as the processors still had an error with the max 4000 that Oracle could handle (converting a CLOB).

DBMS_LOB.SUBSTR(TEXT_DOC, 1000, 1)