OCI: Problem with large texts

Archives Bug Discussions
1

Hello, there!

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).

[b]BUT, PDO doesn’t recognizes PDO::PARAM_LOB as a valid type for CLOBs!!! It is a bug apparently without solution!

[/b]

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?

2

Yeah… I know this is an old post… but it is currently my problem. I have a custom DB2 schema in place that works well with the exception of handling the CLOB datatype. Is there anything I can do with this?? Its really got me stumped. :huh:

3

I was getting ORA-01461 when inserting/updating Oracle table with varchar2(4000) columns.

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:

https://bugs.php.net/bug.php?id=63809

4

use LONG VARCHAR instead of VARCHAR. LONG VARCHAR supports a maximum length of 32,700 characters.