Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
PHP and OCI-Lob::read from oracle clob

I'm having a weird problem with PHP and the OCI-Lob::read method.
We have a page that connects to the database and pulls all the other fields out with no problems. However, it when it comes to the clobs, only a small portion of the actual data is retrieved.
The code uses:
<span class="pln" style="background: transparent;">$content</span><span class="pun" style="background: transparent;">=</span><span class="pln" style="background: transparent;"> $features</span><span class="pun" style="background: transparent;">[</span><span class="lit" style="color: #800000; background: transparent;">14</span><span class="pun" style="background: transparent;">]-></span><span class="pln" style="background: transparent;">read</span><span class="pun" style="background: transparent;">(</span><span class="lit" style="color: #800000; background: transparent;">2000</span><span class="pun" style="background: transparent;">);</span><span class="pln" style="background: transparent;"> echo $content</span><span class="pun" style="background: transparent;">;</span>
And we're getting a tiny section of the field returned (only about 185 out of a total 553 chars). The really strange thing is that no matter what the length specified is - it still only returns a small section - and the cut off seems to actually be 549 bytes/chars - anything below the read(length) specified as 549 shortens the returned string (even though what is returned is far less the 549 chars) but anything over 549 makes no difference.
Additionally, we've used the OCI-Lob::load method with exactly the same results where only a portion of the actual contents of the clob field is returned.
Any ideas what could be causing this?
PHP version is 5.1.6
RDBMS is 11.2.0.4.2
Following shows OCI config via phpinfo()
oci8
OCI8 Support | enabled |
Revision | $Revision: 1.269.2.18 $ |
Active Persistent Connections | 1 |
Active Connections | 1 |
Oracle Instant Client Version |
|
Temporary Lob support | enabled |
Collections support | enabled |
Directive | Local Value | Master Value |
oci8.default_prefetch | 10 | 10 |
oci8.max_persistent | -1 | -1 |
oci8.old_oci_close_semantics | 0 | 0 |
oci8.persistent_timeout | 60 | 60 |
oci8.ping_interval | 60 | 60 |
oci8.privileged_connect | Off | Off |
oci8.statement_cache_size | 20 | 20 |
Best Answer
-
It turns out that there was some issue with the tns alias. To test, the connect string was hard-coded and the clob data was then returned successfully without truncation. Using the tns alias in the connect descriptor seems to have been the root of the issue so there must have been some small syntax error in tnsnames.ora although I'll be damned if I can figure out why it was partially retrieving the clob!
Answers
-
Your version of OCI8 corresponds to PECL OCI8 1.2.1, which is very old. You can see the subsequent fixes in the changelog PECL :: oci8 Changelog In particular there have been a number of LOB changes.
I would start by upgrading. Ideally you would use PHP 5.5 with PECL OCI8 2.0.8 and Instant Client 11.2.0.4. Otherwise just upgrade to PECL OCI8 1.4.10 and Instant Client to 11.2.0.4. The following instructions should help: Installing PHP and the Oracle 11g Instant Client for Linux and Windows
I am suggesting using PECL OCI8 because it is more recent than the OCI8 in the PHP 5.1.x - 5.5.x bundles. PHP 5.6 natively has OCI8 2.0 but you may not want to upgrade an existing site to PHP 5.6 until this branch of PHP has matured.
-
It turns out that there was some issue with the tns alias. To test, the connect string was hard-coded and the clob data was then returned successfully without truncation. Using the tns alias in the connect descriptor seems to have been the root of the issue so there must have been some small syntax error in tnsnames.ora although I'll be damned if I can figure out why it was partially retrieving the clob!
-
I appreciate hearing the resolution. I'm glad you got it worked out.
(I'd still recommend upgrading!)