php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #36974 ORA-01405: fetched column value is NULL on LOB fields in 10g
Submitted: 2006-04-04 20:45 UTC Modified: 2016-03-08 01:39 UTC
Votes:4
Avg. Score:4.0 ± 0.7
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:1 (33.3%)
From: crescentfreshpot at yahoo dot com Assigned: wez (profile)
Status: Closed Package: PDO OCI
PHP Version: 5.1.2 OS: Win 2000, Win XP
Private report: No CVE-ID: None
 [2006-04-04 20:45 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.

Setting the PDO::ATTR_ORACLE_NULLS driver attribute to PDO::NULL_TO_STRING and/or PDO::NULL_EMPTY_STRING has no effect.

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.

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)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-05 13:48 UTC] crescentfreshpot at yahoo dot com
Happens on Oracle 9i as well (win xp, php 5.1.2).
 [2006-06-20 15:19 UTC] tony2001@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.2-win32-latest.zip


 [2006-06-28 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2006-07-31 18:20 UTC] crescentfreshpot at yahoo dot com
Same warning with PHP 5.2.0RC2-dev (cli) (built: Jul 31 2006 16:21:10)
 [2007-02-09 14:48 UTC] crescentfreshpot at yahoo dot com
Fixed in PHP 5.2.1 (cli) (built: Feb  7 2007 23:11:26) for win32.

This was for a long time my only reason for choosing oci8 over pdo_oci. Thank you for finally fixing this!
 [2016-03-08 01:39 UTC] sixd@php.net
-Package: PDO related +Package: PDO OCI
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Apr 26 15:01:56 2024 UTC