Inserting CLOB into Datbase throws ORA-01704

Anderosn — Fri, 13 Oct 2023 15:44:43 GMT

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).

Re: Inserting CLOB into Datbase throws ORA-01704

ChethanYM — Wed, 01 May 2024 12:28:12 GMT

@Anderosn

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

Regards,

Chethan YM

question Re: Inserting CLOB into Datbase throws ORA-01704 in Support Questions

Inserting CLOB into Datbase throws ORA-01704

Re: Inserting CLOB into Datbase throws ORA-01704