Why doesn't OCI-Lob->close work for IN OUT parameters?

Question

From the documentation I've read regarding LOBs and the OCI8 PHP extension, it seems like I should call $lob->close() in the code below, since I have used $lob->writeTemporary(). $lob->close() works fine when I am passing a LOB to a stored procedure that accepts an IN parameter, but does not work if I am passing a LOB to a stored procedure that accepts an IN OUT parameter.

Obviously I can just leave out the call to $lob->close() for IN OUT parameters, but I am curious to know why I need to. Could someone please explain what is happening in the code below that causes it to produce the following error? Any insight is much appreciated.

OCI-Lob::close() [oci-lob.close]: ORA-22289: cannot perform %s operation on an unopened file or LOB

$my_clob = 'Lorem ipsum dolor sit amet...';

$connection = oci_connect('user', 'pass', 'connection string');
$statement  = oci_parse($connection, 'begin p_clob_in_out(:p_my_clob); end;');
$lob        = oci_new_descriptor($connection, OCI_D_LOB);

$lob->writeTemporary($my_clob, OCI_TEMP_CLOB);

oci_bind_by_name($statement, ':p_my_clob', $lob, -1, OCI_B_CLOB);

oci_execute($statement, OCI_DEFAULT);

if (is_object($lob))
{
  $data = $lob->load();

  $lob->close();
  $lob->free();
}

echo $data;

The p_clob_in_out procedure looks like this:

procedure p_clob_in_out(
    p_my_clob in out clob
)
is
begin
    p_my_clob := 'ABC123... ' || p_my_clob;
end p_clob_in_out;

---

Upon further reading thanks to Vincent Malgrat's answer, I think this is what is happening... In my PHP code, the $lob variable is a temporary LOB that is passed in. That temporary LOB is modified by the procedure, which creates a copy of it. The copy is then passed out and replaces the $lob variable. The writeTemporary method was never called on the copy of the LOB, so when I call $lob->close() it fails. The original LOB that was initially created (that I would be able to call $lob->close() on) is no longer accessible by the PHP script.

I think the NOCOPY hint may not apply here because on this page under "Restrictions on NOCOPY" it states that NOCOPY will be ignored if "the subprogram is called through a database link or as an external procedure". According to this page, it sounds like the anonymous block in my PHP script that is calling the stored procedure would be considered an external procedure.

Answer 1

I run into a similar puzzling problem with temporary LOBs (pure Pl/SQL so may be similar in PHP). Some code that was working fine with persistent LOBS didn't work with temporary LOB. After some searching I found this note in the documentation:

A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made.

I would be curious to see if you run into the same problem if you specify NOCOPY in your procedure : procedure p_clob_in_out(p_my_clob in out NOCOPY clob). Also can you check that your lob contains 'ABC123... ' after the procedure call?

My reasoning is the following: IN parameters are passed as reference, so the LOB is modified anyway when you pass it as an IN parameter. IN OUT parameters are passed by value so in effect you apply your procedure to a copy of the temporary LOB (persistent LOBs would not be deep-copied).