Support Questions

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

Inserting CLOB into Datbase throws ORA-01704


I have a flow file which needs to be saved to Database. Size of flow file content can be <= 2 MB. 
I have tried the PutSQL but it throws  ORA-01704 as the insert statement with CLOB in it becomes too large. 
I have tried using PutDatabaseRecord, but it doesn't store complete flowfile in teh clob column, Information that it stores is this "

apRecord[{responseMetadata=MapRecord[{responseType=ACTIVITY, activityDataExist=true, partNo=1, noOfParts=6}], responseData=MapRecord[{responseList=[Ljava.lang.Object;@676af33}]}]"
 @cotopaul @SAMSAL @MattWho @steven-matison @TimothySpann  Can you pls look into this
looks like it is wrapping the Json with MapRecord. 
Can someone pls suggest how to insert a flowfile content into a CLOB column in DB. 
I cannot use ExecuteScript to use PL/SQL ( ExecuteScript is not approved to use by Nifi team in my company).

Super Collaborator


1. If the content of your flow file is too large to be inserted into a single CLOB column, you can split it into smaller chunks and insert each chunk into the database separately.

2. Instead of storing the content in a CLOB column, you can consider storing it in a BLOB (Binary Large Object) column in your database. BLOB columns can store binary data, including large files, without the size limitations of CLOB columns.

3. Store the content of the flow file in an external storage system (e.g., HDFS, Amazon S3) and then insert the reference (e.g., file path or URL) into the database. This approach can be useful if the database has limitations on the size of CLOB or BLOB columns

4. If ExecuteScript is not approved, consider using an external script or application to perform the insertion into the database. You can trigger the script or application from NiFi using ExecuteProcess or InvokeHTTP processors



Chethan YM