php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56936 ORA-01405: fetched column value is NULL on LOB fields in 10g
Submitted: 2006-04-04 16:38 UTC Modified: 2007-01-22 07:49 UTC
From: crescentfreshpot at yahoo dot com Assigned:
Status: Closed Package: PDO_OCI (PECL)
PHP Version: 5.1.1 OS: Win 2000, Win XP
Private report: No CVE-ID: None
 [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)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-11-23 09:26 UTC] friedel at gmx dot net
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)
 [2007-01-21 15:50 UTC] afreaks at yahoo dot com
Same here on Debian, php 5.2.0   

Similar bug in oci8 is by the way already fixed by Tony... Do we have any chance to see solution here as well?

Regards
Artur
 [2007-01-22 07:49 UTC] tony2001 at phpclub dot net
This bug has been fixed in CVS.

In case this was a documentation problem, the fix will show up at the
end of next Sunday (CET) on pecl.php.net.

In case this was a pecl.php.net website problem, the change will show
up on the website in short time.
 
Thank you for the report, and for helping us make PECL better.


 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 19:01:29 2024 UTC