PHP and Oracle CLOB woes

Been working with Oracle lately, trying to make CLOBs work. For those of you who dont know, CLOBs are Character Large OBjects, and BLOBs are Binary Large OBjects. (edited) Character and Binary LOcator Objects, like pointers. CLOBs and BLOBs are ways that databases can store arbitrarily large amounts of text or binary data, and overcomes the size limitation of a VARCHAR field type (4000bytes). If using an 8-bit ASCII charset, that means 4000 characters, but with the onset of UTF 8 and other multibyte charsets, Unicode and so on, that could be as few as 1000 characters per column/field.

We're still in the middle of resolving some issues, becase the Oracle databse handles a CLOB as an IN OUT parameter, and rather than passing the data into a stored procedure, you awkwardly have to set the value of vCLOBfield = empty_clob()


CREATE OR REPLACE PACKAGE BODY PKG_CLOB AS
/***************************************

PROCEDURE UpdateClob(vCLOBfield IN OUT CLOB) IS -- cursor must be in out
BEGIN
UPDATE table_or_view_with_clob
SET clob_value = empty_clob() -- returns a reference to the LOB (think 'null pointer')
WHERE clob_id = 1 --use your own where clause here
RETURNING clob_value INTO vCLOBfield; -- returns the actual LOB reference (think 'address pointer')
END;
END PKG_CLOB;
/


However using "instanceof" OCI-Lob on the returned object will fail, as the php developers wisely named the object using invalid characters, specifically '-'. Since "is_a" is deprecated, the only way to discover if the oject returned from Oracle is indeed an instance of an OCI-Lob, is perhaps something like


/*
db connection,
sql statement,
@OCIParse statements here
*/

$mylob = OCINewDescriptor($this->connection, OCI_D_LOB);
if ( get_class($mylob) == 'OCI-Lob' )
{
echo "is a LOB";
@OCIBindByName($stmt, ':inputclob', &$mylob, -1, OCI_B_CLOB);

} else {
echo "not a LOB";
}

/*
@OCIExecute,
@OCIError handling,
@OCIFreeCursor statements
*/


Now this is still very much in development here, so its by no means a complete solution to using CLOBs, but as things develop I may well come back and address this in future when I get a complete solution - so leave a comment if you are interested and want me to increase my efforts!!

TTFN.