Hi Folks,
we are trying to save json request/response data into oracle database using SQLExecutor and getting following error
java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
Request that we are trying to save is more than 6000 characters. Appreciate your suggestions ?
Best answer by david_r
Oracle Database column type is CLOB. for Writer i am using insert statement using SQLExecutor. oracle database is 19C.
When using an SQLExecutor, the CLOB is actually transmitted as a string literal, which is why you're running into this Oracle limitation. Remember that the SQLExecutor simply transmits the entire SQL statement as a string to Oracle, there is very little or no parsing being done by FME.
If possible, I would look into using either a FeatureWriter or a DatabaseUpdater to insert the actual CLOB value, those should be much more intelligent in how they handle LOB datatypes.
What is the Oracle datatype of the field you're writing to? How is the writer data type for the same field configured?
Oracle Database column type is CLOB. for Writer i am using insert statement using SQLExecutor. oracle database is 19C.
Oracle Database column type is CLOB. for Writer i am using insert statement using SQLExecutor. oracle database is 19C.
When using an SQLExecutor, the CLOB is actually transmitted as a string literal, which is why you're running into this Oracle limitation. Remember that the SQLExecutor simply transmits the entire SQL statement as a string to Oracle, there is very little or no parsing being done by FME.
If possible, I would look into using either a FeatureWriter or a DatabaseUpdater to insert the actual CLOB value, those should be much more intelligent in how they handle LOB datatypes.
