Solved

How to insert large payload to Orcale Database table

  • 30 June 2021
  • 5 replies
  • 29 views

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 ?

icon

Best answer by david_r 30 June 2021, 18:42

View original

5 replies

Userlevel 4

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.

Userlevel 4

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.

Thanks David, DatabaseUpdater as able to save the CLOB data. Appreciate your help.

 

I had a similar problem some years ago. Solved using cx_Oracle (Python).

Reply