Examples

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);



?>