php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39147 Problems with OCIExecute and CLOB fields (maybe OCIBindByName)
Submitted: 2006-10-13 13:44 UTC Modified: 2006-10-13 17:07 UTC
From: pucheta at yahoo dot com Assigned:
Status: Not a bug Package: OCI8 related
PHP Version: 4.4.4 OS: Windows Server 2003 Apache/1.3.3
Private report: No CVE-ID: None
 [2006-10-13 13:44 UTC] pucheta at yahoo dot com
Description:
------------
The problem is to insert or update CLOB fields in Oracle. When i use Bind variables with OCI library, i connect, parse and bind by name ok, but OCIExecute says that the LOB Locator is invalid. I search internet for several days and i've not find any solution. The code is equal to the php examles of the manual. I prove to pass the OCI-Lob object returned by OCINewDescriptor, to the OCIBindByName by the deprecated reference format and without reference, but the problem is the same. I suspect OCIBindByName modify the OCI-Lob object.

-------
Modules:
oci8 and several commons like mysql, gd, pgsql, sessions, etc.

------
php.ini

error_reporting = 2047

to reporting all errors



Reproduce code:
---------------
<?php
if ( !$conn = OCILogon("user", "pass", "base") )
	die "Conection failed.";

$SQL = "UPDATE table SET field = :var WHERE ID = 1";

if ( !($stat = OCIParse ( $conn, $SQL )) )
	echo "OciParse failed.<br />";

$lob = OCINewDescriptor($conn, OCI_D_LOB);

OCIBindByName($stat, ':PEPE', &$lob, -1, OCI_B_CLOB);

OCIExecute($stat);

$large_text = "realy large text...";

if( $lob->save($large_text) ){
	OCICommit($conn);
	echo "Blob successfully uploaded\n";
} else {
	echo "Couldn't upload Blob\n";
}

OCIFreeStatement($stat);
OCILogoff($conn);
?>

Expected result:
----------------
I espect to record the content of the variable $large_text (containing a large text) in the field "field" in the table "table".

Actual result:
--------------
Two warnings:

1 - In OCIExecute line php warning:

"Warning: ociexecute(): OCIStmtExecute: ORA-22275: invalid LOB locator specified in "this file" on line "OCIExecute line"


2 - In the last "if" line, when execute the save method, warning:

"Warning: save(): OCILobWrite: OCI_INVALID_HANDLE in "this file" on line "that line"

And no any changes in database. I can't record anything  :(



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-10-13 13:54 UTC] tony2001@php.net
This code cannot work, since the descriptor IS invalid and it has to be initialized by Oracle itself. 
So the easiest way to perform your task is to do the following:
INSERT INTO mytable (mykey, myclob)
       VALUES (:mykey, EMPTY_CLOB())
       RETURNING myclob INTO :myclob
I.e. insert into the column an EMPTY_CLOB(), return it to your descriptor and then write the data into it and commit the transaction. See example #3 here: http://php.net/oci8

Or you can INSERT EMPTY_CLOB() first, then SELECT it and write that data, which is effectively the same, but in two steps.
No bug here, this is how the things work.
 [2006-10-13 17:07 UTC] pucheta at yahoo dot com
Very Thanks. Your suggestions works fine. This in not a php or oracle bug. The bug is me  :)

Thanks!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Apr 28 16:01:31 2024 UTC