Example #1 Basic query
<?php
$conn = oci_connect('hr', 'hr', 'orcl');
if (!$conn) {
$e = oci_error();
print htmlentities($e['message']);
exit;
}
$query = 'SELECT * FROM DEPARTMENTS';
$stid = oci_parse($conn, $query);
if (!$stid) {
$e = oci_error($conn);
print htmlentities($e['message']);
exit;
}
$r = oci_execute($stid, OCI_DEFAULT);
if (!$r) {
$e = oci_error($stid);
echo htmlentities($e['message']);
exit;
}
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {
print '<tr>';
foreach ($row as $item) {
print '<td>'.($item?htmlentities($item):' ').'</td>';
}
print '</tr>';
}
print '</table>';
oci_close($conn);
?>
Example #2 Insert with bind variables
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mid NUMBER, myd VARCHAR2(20));
$conn = oci_connect('scott', 'tiger', 'orcl');
$query = 'INSERT INTO MYTABLE VALUES(:myid, :mydata)';
$stid = oci_parse($conn, $query);
$id = 60;
$data = 'Some data';
oci_bind_by_name($stid, ':myid', $id);
oci_bind_by_name($stid, ':mydata', $data);
$r = oci_execute($stid);
if ($r)
print "One row inserted";
oci_close($conn);
?>
Example #3 Inserting data into a CLOB column
<?php
// Before running, create the table:
// CREATE TABLE MYTABLE (mykey NUMBER, myclob CLOB);
$conn = oci_connect('scott', 'tiger', 'orcl');
$mykey = 12343; // arbitrary key for this example;
$sql = "INSERT INTO mytable (mykey, myclob)
VALUES (:mykey, EMPTY_CLOB())
RETURNING myclob INTO :myclob";
$stid = oci_parse($conn, $sql);
$clob = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_bind_by_name($stid, ":myclob", $clob, -1, OCI_B_CLOB);
oci_execute($stid, OCI_DEFAULT);
$clob->save("A very long string");
oci_commit($conn);
// Fetching CLOB data
$query = 'SELECT myclob FROM mytable WHERE mykey = :mykey';
$stid = oci_parse ($conn, $query);
oci_bind_by_name($stid, ":mykey", $mykey, 5);
oci_execute($stid, OCI_DEFAULT);
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
$result = $row['MYCLOB']->load();
print '<tr><td>'.$result.'</td></tr>';
}
print '</table>';
?>
You can easily access stored procedures in the same way as you would from the command line.
Example #4 Using Stored Procedures
<?php
// by webmaster at remoterealty dot com
$sth = oci_parse($dbh, "begin sp_newaddress( :address_id, '$firstname',
'$lastname', '$company', '$address1', '$address2', '$city', '$state',
'$postalcode', '$country', :error_code );end;");
// This calls stored procedure sp_newaddress, with :address_id being an
// in/out variable and :error_code being an out variable.
// Then you do the binding:
oci_bind_by_name($sth, ":address_id", $addr_id, 10);
oci_bind_by_name($sth, ":error_code", $errorcode, 10);
oci_execute($sth);
?>