Oracle CLOB column: Sequel insert fails when string is > 4000 chars. #557

Closed
gherney opened this Issue Sep 27, 2012 · 1 comment

Projects

None yet

2 participants

@gherney

Hi Jeremy - I'm running into an issue using you sequel gem with an Oracle database (Oracle Express 11g, oracle_enhanced adapter). I am trying to insert a string into a CLOB column type. Inserts seem to work correctly as long as the string is less than or equal to 4000 characters. When the string is greater than 4000 characters, I get "Sequel::DatabaseError: OCIError: ORA-01704: string literal too long".

Here is how I produce the error:

Assuming a table 'sequel_test' containing one CLOB column 'clob_column'

sequel = Sequel.oracle(###Connection info for Oracle Express 11g test database, 'oracle_enhanced' adapter###)

sequel[:sequel_test].insert(:clob_column=>('x' * 4000))
======> inserts a record correctly

sequel[:sequel_test].insert(:clob_column=>('x' * 4001))
======> blows up with following error:
Sequel::DatabaseError: OCIError: ORA-01704: string literal too long
from stmt.c:235:in oci8lib_191.so

Should I be doing something different?
Thanks for your help,
Geoff Herney

@jeremyevans
Owner

This appears to be an internal Oracle limitation, not a problem with Sequel. Oracle apparently does not support string literals greater than 4000 characters. You can use a bound variable to work around this restriction:

sequel[:sequel_test].call(:insert, {:c=>('x' * 4001)}, :clob_column=>:$c)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment