1

I'd like to add a large string (above 76k characters) to CLOB column in Oracle database. I need to run a script from liquibase framework. How can achive this?

Simple insert INSERT INTO table_clob (clob_column) VALUES (to_Clob('string above 72000 chars...')); with and without to_clob() method is returning exception like:

ORA-01704: string literal too long

Cannot load data from file as described here with procedure: https://oracle-base.com/articles/8i/import-clob as I don't have priviliges to any directory

Searched google but didn't enounter any solution for my requirement. Any advice?

UPDATE:

After hours of searching finally found a workaround here: https://stackoverflow.com/a/49817056/1622703

It is not sufficient as I need to cut the text for 3 chunks manually (with around 30k chars), but it works. Now just need to figure our how to do it dynamically in case that the string will have vary lenghts of chars (above 10k chars for example).

1 Answer 1

4

A hard-coded string enclosed in single quotes is known as a string literal. An example is 'Hello world'. Another example is the very long string you are trying to insert in the table. By contrast, 'abc' || 'def' is a string expression but it is not a string literal. Similarly, to_char(sysdate, 'yyyy-mm-dd') is a string expression, but not a literal. "Literal" means constant, hard-coded text.

The issue you are facing has nothing to do with insert, or to_clob(), or the data type of columns in your table, etc. It only has to do with the string literal itself.

In Oracle, a string literal can be at most 4000 bytes long (or 32767 bytes if the database is set up with extended MAX_STRING_SIZE). PERIOD! There is no way around it.

So, the question is, how can you ever get a string as long as the one you have into a table with a CLOB column. The answer depends on how you are receiving the string in the first place. The best option would be if it came in chunked already - as a collection of strings, with a tag (an id) to keep track of which fragment belongs to which CLOB and an ordinal number (to show if it's the first chunk, the second, etc.) Then you could re-assemble them using TO_CLOB() on the first chunk, plus the concatenation operator.

If your process is to type 72000 characters at the keyboard, you will have to type 4000 of them at a time, enclose in single quotes, and use the concatenation operator (essentially doing by hand what I described above). You would also have to use TO_CLOB() on the first fragment (otherwise the concatenation will fail).

Another option is for the string to come as a value, from some application that supports long strings (something compatible with Oracle's CLOB) and that can hand over such values to the Oracle database without the need to write out the hard-coded string in full.

So, the ball is in your court. The first question is, Where is the long string coming from in the first place?

Sign up to request clarification or add additional context in comments.

2 Comments

Thanks mathduy for your input. The string will be provided manually by business partner and the length of it can be vary.
@studentttt We had the same problem in our project, ended up setting up an interceptor service, which takes the third-party input and breaks it down to chunks, then passes it to our main service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.