OCI: Problem with large texts Sharing this issue with the community
Posted 24 September 2009 - 07:06 PM
I found this bug (working with ORACLE) and found a solution, but I'm not sure if mine is the best way, so I want to see your opinions
These days I had to develop a form with an text information around 4000 characters. I used first a VARCHAR(4000), but got an ORA-01461 error:
<h3 class="r">can bind a LONG value only for insert into a LONG column</h3> After extensive search, I realized the following:
VARCHAR only supports 4000characters long. It's a good number of chars, but in multibytescollations (i.e., UTF8) , this limit decreases to 1333 chars (it uses 3 bytes tohandle it properly).
Above this limit, PDO treats the column as LONG, and requires thePDO::PARAM_LOB as param type (Instead of, the ORA-01461 error message israised)
Above this limit we can use the CLOB type (Character Large OBject).
BUT, PDO doesn't recognizes PDO::PARAM_LOB as a valid type for CLOBs!!! It is a bug apparently without solution!
Also, CLOBs are charged by Yii as resource streams, so it requires some special treatment.
Oracle and forum developers, do you have some opinion about how can we handle with this problem?
Posted 25 October 2011 - 09:30 AM
Posted 19 December 2012 - 02:52 PM
I found line 300 in PHP PDO source php-5.4.9/ext/pdo_oci/oci_statement.c:
value_sz = 1332; /* maximum size before value is interpreted as a LONG value */
Not sure why. Looked back thru Github source, and found someone changed value from 4000 to 1332 in Jan, 2007.
I changed it to "value_sz = 4000;", recompiled PHP, and the inserts/updates work fine. I don't know how this affects CLOBs or LONGs, but I'm using neither.
Reported as PHP bug: