1

Currently I am develop web app using Code Igniter and Oracle database. When I try to insert long char for CLOB column I get error

ORA-01704 String literal too long

How to fix this in CI?

Say I have a table in oracle like this

CREATE TABLE test(
   ID NUMBER,
   SUMMARY CLOB
)

When I try to insert a row (with more than 4000 cahr for SUMMARY column) using CI by this command

$this->db->set('ID','123');
$this->db->set('SUMMARY','Very long text is here');
$this->db->insert('test');

I got the string literal is too long

I use Oracle 10g

Lhuqita Fazry
  • 291
  • 4
  • 12
  • show us how & what you're doing – Sathyajith Bhat Feb 01 '12 at 04:44
  • Are you using pdo_oci or oci8 extension? What is the version of Oracle you are connecting to? – Furgas Feb 01 '12 at 08:12
  • Have you tried looking up the error number? There are plenty of articles describing it's occurence and possible fixes, like http://www.dba-oracle.com/t_ora_01704_string_literal_too_long.htm – kb. Feb 02 '12 at 02:31
  • I have already searched in google how to fix this. but I found nothing. Also I find the the url you given me from google, but I cannot access the url. I don't know why – Lhuqita Fazry Feb 02 '12 at 02:50

1 Answers1

3
ORA-01704: string literal too long
Cause: The string literal is longer than 4000 characters.
Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.

When inserting/updating CLOB values longer than 4000 characters it's needed to use binded parameters.

I'm not Code Igniter user but examinig Code Igniter's database abstraction classes shows that it doesn't use prepared statements and real variable binding (BTW: I wouldn't use Code Igniter framework because of this...).

So, the conclusion is you can't do what you want to do using Code Igniter's helpers for database interaction, at least without rewriting some of its code. I believe that in this particular case you must use "pure" PDO interface.

Furgas
  • 2,729
  • 1
  • 18
  • 27
  • yes, you're right. I used the PDO interface, and it's work. Thanks – Lhuqita Fazry Feb 02 '12 at 12:24
  • @Furgas : I am into the similar problem and the link you have provided for pure PDO interface doesn't work. Will you please re post the valid link. Thank you... – hsuk Dec 19 '12 at 04:58
  • abuzuhair, will you please help me with what you have done so far to fix the issue ? – hsuk Dec 19 '12 at 05:09
  • @KusH: I've fixed the link. – Furgas Dec 19 '12 at 09:19
  • @Furgas : I tried the PDO too. Works fine with strings less than 4000 chars. Doesn't work for string that has 4000 chars plus . – hsuk Dec 19 '12 at 10:32
  • @KusH Please create a new question with as much details as you can. – Furgas Dec 20 '12 at 10:25
  • http://stackoverflow.com/questions/13948613/more-than-4000-chars-gives-string-literal-too-long-error-on-oracle – hsuk Dec 20 '12 at 10:39