Skip to Main Content
  • Questions
  • Passing CLOB as IN parameter into stored procedure from PHP

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Premek.

Asked: March 10, 2017 - 6:21 am UTC

Last updated: March 19, 2017 - 5:10 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Question:
I could not pass CLOB from PHP into stored procedure in Oracle DB and I do not not why.
I think, I follow the examples in PHP and other sites, but I need to use clob as IN parameter
not just select, update or output. I always get the error:
OCI-Lob::write(): OCI_INVALID_HANDLE
What is wrong? Better how can I do that?

Test case:

(1) ORACLE side:
create procedure TMP_TEST(pMyClob in CLOB)
is
begin
  null ; // something, not important ... 
end ;


(2) PHP client side:
<?
$s = 'some input some input ... some input';
$query = "begin TMP_TEST( pMyClob => :abcde ); end; ";
$conn = OCI_Connect($user, $pass, $db, $charset); 
$stmt = OCI_Parse($conn, $query);
$cin  = OCI_New_Descriptor($conn, OCI_DTYPE_LOB) ;
OCI_Bind_By_Name($stmt, ':abcde', $cin, -1, OCI_B_CLOB);
OCI_Execute($stmt);
$cin->write($s);       //  THIS PRODUCE AN ERROR 
@OCI_Free_Statement($stmt);
@OCI_Close($conn);
?>

and Connor said...

I asked Chris Jones about this. He suggested taking a read here:

http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

and said you might be missing a OCI_NO_AUTO_COMMIT flag on your call.

Also a nice article here

http://www.oracle.com/technetwork/articles/fuecks-lobs-095315.html


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Only pinged out

Premek, March 18, 2017 - 7:27 pm UTC

You did not solve the problem, you only throw it away. Have you ever read the question? I do not ask how to insert or update - it is easy. I asked how to bind clob as stored proc parameter. Maybe, oracle does not allow this - that is the question.
Connor McDonald
March 19, 2017 - 5:10 am UTC

A touch of politeness in the review would have gone a long way toward us spending more time on this for you if you did not get a solution from the provided information.

But lack of politeness generally leads to a lack of ongoing interest from us

Just be patient

Alexander, October 19, 2017 - 10:13 am UTC

Just for all great community respect!
Tom answer gave answer as always and answer is

$lob->writeTemporary($myv, OCI_TEMP_BLOB);

Look at excellent book on page 236 in first URL Tom gave.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here