So, my new job has me working with Oracle which is in my book, a way different beast compared to mySQL. Everything I know about databases from mySQL basically had to be re-tooled for Oracle.
I spent around 3 hours trying to figure out how to work in a set of prepared statements (one of which returned a value) in PHP PDO, and thought I might share how I did this.
Okay, so I have one stored procedure and one function.
PROCEDURE RETURN_CLOB(:CLOB_ID, :CLOB_OUT IN CLOB) — All this does was search a table with key/ CLOB value pairs. It basically selected a row with the :CLOB_ID, and :CLOB_OUT returned the CLOB value.
FUNCTION CONVERT_CLOB_TO_VARCHAR2(:CLOB_OUT IN CLOB) RETURN VARCHAR2 — this was a function definition that took in the :CLOB_OUT from RETURN_CLOB and spit it out as a VARCHAR2
Note that I did not write these two procedures, it’s something out of a package I used at work, but was having trouble trying to get them to work in PDO.
I’m using PHP Propel 1.3, but you can easily rewrite this in standard PDO calls, since the $con object is actually a combination of the PDO class and PDO Statement class.
Here’s the code:
$con = Propel::getConnection(‘…’); //All this does is just use PDO to create a connection
/*
The call below is the same as PDOStatement::prepare(). I’ve condensed the following into one line:
DECLARE
CLOB_Output CLOB;
BEGIN
PackageName.RETURN_CLOB( :Clob_Id, CLOB_Output );
:Str := PackageName.CONVERT_CLOB_TO_VARCHAR2( CLOB_Output );
END;
//So the declare part is to define an internal variable in Oracle, which I’ve named CLOB_Output. It is of type CLOB. The Begin … End block processes the statements in one container.
1. Return_CLOB returns the CLOB data in CLOB_Output
2. Call CONVERT_BLOB_TO_VARCHAR2 with CLOB_Output
3. The converted CLOB is returned into :Str
*/
$stmt = $con->prepare(“DECLARE CLOB_Output CLOB; BEGIN PackageName.RETURN_CLOB( :Clob_Id, CLOB_Output ); :Str := PackageName.CONVERT_CLOB_TO_VARCHAR2( CLOB_Output ); END;”);
$m = “”;
$stmt->bindValue(‘:Clob_Id’, ‘Clob_Id_goes_here’, PDO::PARAM_INT);
$stmt->bindParam(‘:Str’, $m, PDO::PARAM_STR, 32767); //replace 32767 with the size of the VARCHAR2 that you are returning from CONVERT_BLOB_TO_VARCHAR2
$stmt->execute();
//$m now contains the CLOB as a VARCHAR2!
Phew! Anyways, I’m going to say that Propel 1.3 isn’t ready for Oracle yet, and neither is PHP_PDO for Oracle. There’s too many hassles and tricks I had to use to do the things I needed in Oracle, and I’m going to recommend people continue to use the OCI8 extension.
