| 
        php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login | 
  [2006-04-04 16:38 UTC] crescentfreshpot at yahoo dot com
 Description:
------------
pdo oci does not convert oracle nulls to php nulls when fetching from lob fields. Appears in 5.0.5 to 5.1.2 versions of php/pdo/pdo_oci. Oracle version is 10g. Non-lob fields appear to convert just fine.
I'm aware that this behaviour is 'by design' for oracle but was led to believe by the docs that pdo handled nulls for me so I don't have to resort to using NVL(...) in my queries.
Setting the PDO::ATTR_ORACLE_NULLS driver attribute to PDO::NULL_TO_STRING and/or PDO::NULL_EMPTY_STRING has no effect.
Reproduce code:
---------------
<?php
error_reporting(E_ALL);
$dbh = new PDO('oci:', 'scott', 'tiger');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
var_dump($dbh->query("SELECT * FROM SCOTT.EMP")->fetch(PDO::FETCH_ASSOC));
if($dbh->exec("ALTER TABLE SCOTT.EMP ADD (PIC BLOB)") === false) {
  die("ALTER TABLE failed.");
}
var_dump($dbh->query("SELECT * FROM SCOTT.EMP")->fetch(PDO::FETCH_ASSOC));
$dbh->exec("ALTER TABLE SCOTT.EMP DROP (PIC)");
?>
Expected result:
----------------
array(8) {
  ["EMPNO"]=>
  string(4) "7369"
  ["ENAME"]=>
  string(5) "SMITH"
  ["JOB"]=>
  string(5) "CLERK"
  ["MGR"]=>
  string(4) "7902"
  ["HIREDATE"]=>
  string(9) "17-DEC-80"
  ["SAL"]=>
  string(3) "800"
  ["COMM"]=>
  NULL
  ["DEPTNO"]=>
  string(2) "20"
}
array(8) {
  ["EMPNO"]=>
  string(4) "7369"
  ["ENAME"]=>
  string(5) "SMITH"
  ["JOB"]=>
  string(5) "CLERK"
  ["MGR"]=>
  string(4) "7902"
  ["HIREDATE"]=>
  string(9) "17-DEC-80"
  ["SAL"]=>
  string(3) "800"
  ["COMM"]=>
  NULL
  ["DEPTNO"]=>
  string(2) "20"
  ["PIC"]=>
  NULL
}
Actual result:
--------------
array(8) {
  ["EMPNO"]=>
  string(4) "7369"
  ["ENAME"]=>
  string(5) "SMITH"
  ["JOB"]=>
  string(5) "CLERK"
  ["MGR"]=>
  string(4) "7902"
  ["HIREDATE"]=>
  string(9) "17-DEC-80"
  ["SAL"]=>
  string(3) "800"
  ["COMM"]=>
  NULL
  ["DEPTNO"]=>
  string(2) "20"
}
<br />
<b>Warning</b>:  PDOStatement::fetch() [<a href='function.fetch'>function.fetch</a>]: SQLSTATE[HY000]: General error: 1405 OCIStmtFetch: ORA-01405: fetched column value is NULL
 (..\pecl_5_0\pdo_oci\oci_statement.c:446) in <b>C:\dev\tests\db.php</b> on line <b>13</b><br />
bool(false)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits             
             | 
    |||||||||||||||||||||||||||
            
                 
                Copyright © 2001-2025 The PHP GroupAll rights reserved.  | 
        Last updated: Tue Nov 04 10:00:02 2025 UTC | 
same here with PHP 5.2.0, Linux: $dbh = new PDO( 'oci:dbname=localhost/XE;charset=UTF-8', 'user', 'pass' ); $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $dbh->exec('CREATE TABLE test ( test_clob CLOB )'); $dbh->exec("INSERT INTO test ( test_clob ) VALUES ( '' )"); $dbh->query( "SELECT test_clob FROM test" )->fetchAll(); result: PDOException: SQLSTATE[HY000]: General error: 1405 OCIStmtFetch: ORA-01405: fetched column value is NULL (/usr/local/src/php5.2.x/ext/pdo_oci/oci_statement.c:446)