Install Oracle Database 11g or Oracle Database XE.

Install PHP 5.2.4.

Configure the Linux Apache Server.

Download and unzip the php.zip files into the directory where the Apache Server finds the files (i.e. $HOME/public_html).

Review the code as follows that is contained in the connect.php file in the $HOME/public_html directory.

<?php
// Create connection to Oracle
$conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
if (!$conn) {
   $m = oci_error();
   echo $m['message'], "\n";
   exit;
}
else {
   print "Connected to Oracle!";
}
// Close the Oracle connection
oci_close($conn);  
?>

The oci_connect() function contains the connection information. In this case, an abbreviated connection string is used.

The oci_close() function is not required as the connection is automatically closed when the script ends.

Open a Web browser and enter the following URL to display the output:

http://localhost/~phplab/connect.php

"Connected to Oracle!" is displayed if the connection succeeds.

The error "Error connecting to Oracle" is displayed if there are problems creating the database connection.

Review the code as follows that is contained in the query.php file in the $HOME/public_html directory.

<?php
// Create connection to Oracle
$conn = oci_connect("hr", "hrpwd", "//localhost/orcl");

$query = 'select * from departments';
$stid = oci_parse($conn, $query);
$r = oci_execute($stid);

// Fetch the results in an associative array 
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC)) {
   print '<tr>';
   foreach ($row as $item) {
      print '<td>'.($item?htmlentities($item):' ').'</td>';
   }
   print '</tr>';
}
print '</table>';

// Close the Oracle connection
oci_close($conn);  

?>

The oci_parse() function parses the statement.

The oci_execute() function executes the parsed statement.

The oci_fetch_array() function retrieves the results of the query as an associative array, and includes nulls.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/query.php

The results of the query are displayed in a Web browser.

Review the code as follows that is contained in the pconnect.php file in the $HOME/public_html directory.

<?php

// Create a persistent connection to Oracle
// Connection will be reused over multiple scripts
$conn = oci_pconnect("hr", "hrpwd", "//localhost/orcl");
if (!$conn) {
   $m = oci_error();
   echo $m['message'], "\n";
   exit;
}
else {
   print "Connected to Oracle!";
}

// Close the Oracle connection
oci_close($conn);  

?>

The oci_pconnect() function creates a persistent connection to Oracle.

Using the oci_close() function does not close persistent connections and is redundant in this script.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/pconnect.php       

A persistent connection has now been created. This connection is still available to scripts that use the same login credentials and that are served by the same http process.

Review the code as follows that is contained in the usersess.sql file in the $HOME/public_html directory.

column username format a30
column logon_time format a18
set pagesize 1000 feedback off echo on
alter session set nls_date_format = 'DD-MON-YY HH:MI:SS';
select username, logon_time from v$session where username is not null;

You have now created a SQL*Plus (Oracle's command-line SQL scripting tool) script file that you run in SQL*Plus. This SQL*Plus script changes the National Language Character date format of the database, and shows the current database sessions. The date format change only relates to the SQL*Plus session, and is used to format the output of the logon times.

Open a terminal window and enter the following commands. Note that you could also exeucte the script in SQL Developer.

cd $HOME/public_html
sqlplus system/oracle@//localhost/orcl
@usersess.sql 

The SQL*Plus script lists the current database sessions. The session created by the PHP script is still active and shown in the first line of the results as the username HR. Even though the oci_close() function was called, this does not close persistent connections, and the connection is available for other scripts.

To show that the persistent connection is being reused by other PHP scripts, and that the session settings are the same, review the code as follows that is contained in the pconnect2.php file in the $HOME/public_html directory.

<?php

// Function to execute a query
function do_query($conn, $query) 
{
  $stid = oci_parse($conn, $query);
  oci_execute($stid);
  oci_fetch_all($stid, $res);
  echo "<pre>";
  var_dump($res);
  echo "</pre>";
}

// Create a persistent connection to Oracle
$c = oci_pconnect("hr", "hrpwd", "//localhost/orcl");

// Query the database system date
do_query($c, "select sysdate from dual");

// Change the NLS Territory
$s = oci_parse($c, "alter session set nls_territory=germany");
$r = oci_execute($s);

// Query the database system date again
do_query($c, "select sysdate from dual");

?>

This script creates a new persistent connection, or reuses an existing one with the same login credentials.

The script then uses the do_query() function to query and fetch the database system date. It uses the var_dump debugging function to print the value and structure of the PHP variable containing the date query result.

The script then changes the National Language Territory setting to display the output in the format for Germany, and calls the do_query() function again to display the database system date a second time.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/pconnect2.php 

Note that the date format of the two queries differs as the ALTER SESSION command changed to another locale in between queries.

To see the effect of using a persistent connection reload the script.  You may need to do this a few times until the original PHP session (Apache process) is reused.  The date format for both queries is now using the same, new format.  This shows that the connection has been reused and the date format set in the inital script is still set when the later script runs.  The connection has remained alive (is persistent) for reuse by other PHP scripts that use the same login credentials.  If the script was changed to use a standard connection, it would always print two different time formats.

You should be aware of environment changes you make during a persistent session as they may also affect other scripts. But transactions do not span PHP scripts, and uncommitted data will be rolled back at the end of a script.

Run the SQL*Plus script usersess.sql again to see which connections are open.

There are now a number of database sessions open created by the HR user. This shows the persistent sessions that are currently available. On Linux, Apache runs as multiple independent processes.  PHP does not share any information, including connections, between processes. Because each time you run a script it might be executed by a different httpd process, when you use oci_pconnect() you can end up with multiple database connections open.

In your SQL*Plus session, enter the following commands to log in to the database as the user HR and create a new table:

connect hr/hrpwd@//localhost/orcl
create table mytable (col1 date);

Review the code as follows that is contained in the trans1.php file in the $HOME/public_html directory.

<?php
    echo "<pre>";

    // Execute a query
    function do_query($conn)
    {
      $stid = oci_parse($conn,
          "select to_char(col1, 'DD-MON-YY HH:MI:SS') from mytable");
      oci_execute($stid, OCI_DEFAULT);
      oci_fetch_all($stid, $res);
      foreach ($res as $v) {
        var_dump($v);
      }
    }

    // Create a database connection
    function do_connect()
    {
      $conn = oci_new_connect("hr", "hrpwd", "//localhost/orcl");
      return($conn);
    }
    $d = date('j:M:y H:i:s');

    // Create a connection
    $c1 = do_connect();

    // Insert the date into mytable
    $s = oci_parse($c1,
        "insert into mytable values (to_date('"
        . $d . "', 'DD:MON:YY HH24:MI:SS'))");

    // Use OCI_DEFAULT to execute the statement without committing
    $r = oci_execute($s, OCI_DEFAULT);

    // Query the current session/connection
    echo "Query using connection 1<br>\n";
    do_query($c1);

    // Create a new connection and query the table contents
    $c2 = do_connect();
    echo "<br>Query using connection 2<br>\n";
    do_query($c2);
    echo "</pre>";
    ?>

There are two connections used in this script.

This script uses oci_new_connect() to create a unique, non-persistent database connection, then inserts the date into the mytable table and queries it back.

The script then creates a second unique database connection, and queries the table again to show the contents visible to the second connection.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/trans1.php

This script inserts a row into the table using connection $c1.

The data is not been committed to the database because each oci_execute() call uses OCI_DEFAULT and no oci_commit() was called.  No other database user can yet see this row. The query using the second connection $c2 returns an empty array.

Because there is no commit, the data is rolled back by PHP when the script finishes. To see that the data has not been committed, query the table to see if there are any inserted rows. From your SQL*Plus session, enter the following commands to select any rows from the mytable table:

select * from mytable;

Review the code as follows that is contained in the trans2.php file in the $HOME/public_html directory.

<?php
    echo "<pre>";

    // Execute a query
    function do_query($conn)
    {
      $stid = oci_parse($conn,
          "select to_char(col1, 'DD-MON-YY HH:MI:SS') from mytable");
      oci_execute($stid, OCI_DEFAULT);
      oci_fetch_all($stid, $res);
      foreach ($res as $v) {
        var_dump($v);
      }
    }

    // Create a database connection
    function do_connect()
    {
      $conn = oci_new_connect("hr", "hrpwd", "//localhost/orcl");
      return($conn);
    }
    $d = date('j:M:y H:i:s');

    // Create a connection
    $c1 = do_connect();

    // Insert the date into mytable
    $s = oci_parse($c1,
        "insert into mytable values (to_date('"
        . $d . "', 'DD:MON:YY HH24:MI:SS'))");

    $r = oci_execute($s);  // no OCI_DEFAULT means automatically commit

    // Query the current session/connection
    echo "Query using connection 1<br>\n";
    do_query($c1);

    // Create a new connection and query the table contents
    $c2 = do_connect();
    echo "<br>Query using connection 2<br>\n";
    do_query($c2);
    echo "</pre>";
    ?>

This script differs from trans1.php in that there is no OCI_DEFAULT when the data is inserted.  This means the new data is commited.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/trans2.php

The data is now committed, so both queries return the new row in the table.

Reload the page. Each time you reload you will see more rows added to the table.

From your SQL*Plus session, enter the following commands to delete any rows from the mytable table:

delete from mytable;
commit;

You can compare the performance difference between committing each row individually versus at the end of the transaction.

To test the difference, review the code as follows that is contained in the trans3.php file in the $HOME/public_html directory.

    <?php
    function currTime()
    {
      $time = microtime();
      $time = explode(' ', $time);
      $time = $time[1] + $time[0];
      return $time;
    }
    function elapsedTime($start)
    {
      return (currTime() - $start);
    }
    function do_query($conn)
    {
      $stid = oci_parse($conn,
          "select count(*) c from mytable");
      oci_execute($stid, OCI_DEFAULT);
      oci_fetch_all($stid, $res);
      echo "Number of rows: ", $res['C'][0], "<br>";
    }
    function do_delete($conn)
    {
      $stmt = "delete from mytable";
      $s = oci_parse($conn, $stmt);
      $r = oci_execute($s);
    }
    function do_insert($conn)
    {
      $d = date('j:M:y H:i:s');
      $stmt = "insert into mytable values (to_date('"
              . $d . "', 'DD:MON:YY HH24:MI:SS'))";
      $s = oci_parse($conn, $stmt);
      $r = oci_execute($s);
    }
    $c = oci_connect("hr", "hrpwd", "//localhost/orcl");
    $start = currTime();
    for ($i = 0; $i < 10000; $i++) {
      do_insert($c);
    }
    $et = elapsedTime($start);
    echo "Time was ".round($et,3)." seconds<br>";
    do_query($c);  // Check insert done
    do_delete($c); // cleanup committed rows
    ?>

Run this several times and see how long it takes to insert the 10,000 rows.

Now run the trans4.php script. The only difference in this script is that in the do_insert() function OCI_DEFAULT has been added so it doesn't automatically commit, and an explicit commit has been added at the end of the insertion loop:

...

function do_insert($conn) {
  $d = date('j:M:y H:i:s');
  $stmt = "insert into mytable values 
    (to_date('" . $d . "', 'DD:MON:YY HH24:MI:SS'))";
  $s = oci_parse($conn, $stmt);
  $r = oci_execute($s, OCI_DEFAULT);
}

$c = oci_connect("hr", "hrpwd", "//localhost/orcl");
$start = currTime();
for ($i = 0; $i < 10000; $i++) {
  do_insert($c);
}
oci_commit($c);
$et = elapsedTime($start);

...

Rerun the test. The insertion time decreases.

In general you want all or none of your data committed. Doing your own transaction control has performance and data-integrity benefits.

The first part shows fetching arrays using the default output of oci_fetch_array(), which is to fetch the array with both associative and numeric indices.

Review the code as follows that is contained in the fetch.php file in the $HOME/public_html directory. Review the code as follows:

<?php
echo "<pre>";
$conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
$query = 'select * from employees where employee_id = 101';
$stid = oci_parse($conn, $query);
oci_execute($stid);
while ($row = oci_fetch_array($stid)) {
  var_dump($row); // display PHP's representation of $row
}
oci_close($conn);
echo "</pre>";
?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/fetch.php

The output shows that the results contain both associative and numeric indices. While this may provide more flexibility with how you want to handle the results, it is a bigger network and memory overhead.

You may want, instead, to just fetch an array as an associative array. This part shows how you fetch only an associative array.

Change the oci_fetch_array() call to the following:

oci_fetch_array($stid, OCI_ASSOC)

Rerun the following URL:

http://localhost/~phplab/fetch.php

As shown in the output, the OCI_ASSOC parameter fetches the array as an associative array.

The final option is to fetch an array as a numeric array. This part shows how you fetch only a numeric array.

Change the oci_fetch_array() call once again to the following:

oci_fetch_array($stid, OCI_NUM)

Rerun the following URL:

http://localhost/~phplab/fetch.php

The output shows the OCI_NUM parameter fetches the array as a numeric array.

There are other oci_fetch_array() parameters and combinations you can use, such as:

• oci_fetch_array($stid, OCI_BOTH), which returns both associative and numeric indices
• oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS), which returns an associative index, and includes NULLs.

The PHP documentation contains the full list of the fetching options.

Review the code as follows that is contained in the bind.php file in the $HOME//public_html directory.

<?php

   function do_fetch($myeid, $s)
   {
     // Fetch the results in an associative array
     print '<p>$myeid is ' . $myeid . '</p>';
     print '<table border="1">';
     while ($row = oci_fetch_array($s, OCI_RETURN_NULLS+OCI_ASSOC)) {
         print '<tr>';
         foreach ($row as $item) {
             print '<td>'.($item?htmlentities($item):'&nbsp;').'</td>';
         }
         print '</tr>';
     }
     print '</table>';
   }

   // Create connection to Oracle
   $c = oci_connect("hr", "hrpwd", "//localhost/orcl");

   // Use bind variable to improve resuability, and to
   // remove SQL Injection attacks.
   $query = 'select * from employees where employee_id = :eidbv';
   $s = oci_parse($c, $query);

   $myeid = 101;
   oci_bind_by_name($s, ":EIDBV", $myeid);
   oci_execute($s);
   do_fetch($myeid, $s);

   // Redo query without reparsing SQL statement
   $myeid = 104;
   oci_execute($s);
   do_fetch($myeid, $s);

   // Close the Oracle connection
   oci_close($c);
   ?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/bind.php

The $myeid variable is bound to the :eidbv bind variable, so when the query is re-executed the new value of $myeid is passed to the query. This allows you to execute the statement again, without reparsing it with the new value, and can improve performance of your code.

Start SQL*Plus and create a new table, ptab with the following command:

sqlplus hr/hrpwd@//localhost/orcl
create table ptab (mydata varchar(20), myid number);

In SQL*Plus, create a stored procedure, myproc, to insert data into the ptab table, with the following commands:

create or replace procedure
myproc(d_p in varchar2, i_p in number) as
begin
  insert into ptab (mydata, myid) values (d_p, i_p);
end;
/

Review the code as follows that is contained in the proc.php file in the $HOME/public_html directory. Review the code as follows:

<?php

$c = oci_connect('hr', 'hrpwd', '//localhost/orcl');
$s = oci_parse($c, "call myproc('mydata', 123)");
oci_execute($s);
echo "Completed";

?>      

From a Web browser, enter the following URL to display the output:

http://localhost/~phplab/proc.php

The PHP script has created a new row in the ptab table by calling the stored procedure myproc. The table ptab has a new row with the values "mydata" and 123.

Switch to your SQL*Plus session and query the table to show the new row:
           
select * from ptab; 

Apart from stored procedures, PL/SQL stored functions are also commonly used. In SQL*Plus, create a PL/SQL stored function myfunc() to insert a row into the ptab table, and and returns the inserted double the myid value:

create or replace function
   myfunc(d_p in varchar2, i_p in number) return number as
   begin
     insert into ptab (mydata, myid) values (d_p, i_p);
     return (i_p * 2);
   end;
   /

Review the code as follows that is contained in the func.php file in the $HOME/public_html directory. Review the code as follows:

<?php

   $c = oci_connect('hr', 'hrpwd', '//localhost/orcl');
   $s = oci_parse($c, "begin :bv := myfunc('mydata', 123); end;");
   oci_bind_by_name($s, ":bv", $v, 10);
   oci_execute($s);
   echo $v, "<br>\n";
   echo "Completed";

   ?>

Because a value is being returned, the optional length parameter to oci_bind_by_name() is set to 10 so PHP can allocate the correct amount of memory to hold up to 10 digits

Rerun the following URL:

http://localhost/~phplab/func.php

You first will create a simple table and new procedure myproc(). The procedure accepts an array and uses Oracle's fast bulk insert "FORALL" statement to insert all the elements of the array. Review the code in the proc2.sql file in the $HOME/public_html directory.

drop table ptab;

create table ptab(name varchar2(20));

create or replace package mypkg as
  type arrtype is table of varchar2(20) index by pls_integer;
  procedure myproc(p1 in out arrtype);
end mypkg;
/

create or replace package body mypkg as
  procedure myproc(p1 in out arrtype) is
  begin
    forall i in indices of p1
      insert into ptab values (p1(i));
  end myproc;
end mypkg;
/

From a terminal window, execute the following commands:

sqlplus hr/hrpwd@//localhost/orcl
@proc2

Review the code as follows contained in the coll.php file in the $HOME/public_html directory.

<?php
 function do_query($conn)
 {
    echo "<pre>";
    $stid = oci_parse($conn, "select * from ptab");
    oci_execute($stid, OCI_DEFAULT);
    oci_fetch_all($stid, $res);
    var_dump($res);
    echo "</pre>";
 }
 for ($i = 0; $i < 10; $i++) {
    $a[] = 'value '.$i;
 }
 $c = oci_connect("hr", "hrpwd", "//localhost/orcl");
 $s = oci_parse($c, "BEGIN mypkg.myproc(:c1); END;");
 oci_bind_array_by_name($s, ":c1", $a, count($a), -1, SQLT_CHR);
 oci_execute($s);
 do_query($c)
 ?>

This creates an array of strings in $a. The array is then bound to the PL/SQL procedure's parameter.

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/coll.php

The values are queried back from the PTAB table to verify that they have been inserted.

Review the code as follows contained in the errors.php file in the $HOME/public_html directory.

<?php
//Create connection to Oracle
$conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
if (!$conn) {
   // No argument needed for connection errors.
   // To generate an error here, change the connection parameters to be invalid.
   $e = oci_error();
   print "There was a database connection error: " . htmlentities($e['message']);
   exit;
}
// To generate an error here, change the * to an another character, such as %.
$query = "select * from departments";
$stid = oci_parse($conn, $query);
if (!$stid) {
   // For parsing errors, pass the connection resource
   $e = oci_error($conn);
   print "There was a statement parsing error: " . htmlentities($e['message']);
   exit;
}
$r = oci_execute($stid);
if (!$r) {
   // For execution and fetching errors, pass the statement resource
   // To generate an error here, change $query to be an invalid query.
   $e = oci_error($stid);
   echo "<p>";
   print "There was a statement execution error: <strong>" . htmlentities($e['message']).
     "</strong><br>";
   print "The error is located at character " . htmlentities($e['offset']+1) ." 
     of the query: 
     <strong>". htmlentities($e['sqltext']). "</strong><br>";
   echo "</p>";
exit;
}
// Fetch the results in an associative array 
print '<table border="1">';
while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC)) {
  print '<tr>';
  foreach ($row as $item) {
  print '<td>'.($item?htmlentities($item):'&nbsp;').'</td>';
  }
  print '</tr>';
}
print '</table>';
// Close the Oracle connection
oci_close($conn); 
?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/errors.php

To generate a connection error, edit errors.php to change the login information to a connection string that will fail, for example an invalid password for the HR user.

$conn = oci_connect("hr", "hrxx", "//localhost/orcl");

Reload the following URL:

http://localhost/~phplab/errors.php

The connection error handling code catches the connection error and displays the error in the output.

Note: The first error is an error generated by PHP, and can be suppressed by turning off error reporting in the php.ini configuration file.

Edit errors.php to change the login information to the original login so the login and connection will succeed.

To generate a parsing error, edit the $query variable to an invalid query structure, for example:

$query = "select ' from departments";

From your browser reload the following URL:

http://localhost/~phplab/errors.php

The parsing error handling code catches the parsing error and displays the error in the output.

To generate a fetching error, edit the $query variable to an invalid query, for example:

$query = "select * from sometable";

Rerun the following URL:

http://localhost/~phplab/errors.php

The fetching error handing code catches the fetching error and displays the error in the output.

The offset parameter contains the location of the character at which the parsing error beings, and the sqltext parameter contains the SQL statement that caused the parsing error.

The @ function prefix suppresses all PHP errors. This is the same as setting the php.ini file to not display errors, but it is only relevant to the function on which you've used it. Using the @ prefix removes the PHP errors that have been displayed in the previous error-handling examples. To demonstrate this, change oci_execute() to:

$r=@oci_execute($stid);

Rerun the following URL:

http://localhost/~phplab/errors.php

The PHP errors have been suppressed, but the Oracle errors are still displayed by the error handling code in the script.

Before doing this section create a table to store a BLOB. In SQL*Plus logged in as HR, execute the following commands:

create table btab (blobid number, blobdata blob);

Review the code as follows contained in the blobins.php file in the $HOME/public_html directory.

<?php
$myblobid = 1; // should really be a unique id e.g. a sequence number
if (!isset($_FILES['lob_upload'])) {
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" 
      method="POST" enctype="multipart/form-data">
Image filename: <input type="file" name="lob_upload">
<input type="submit" value="Upload">
</form>
<?php
}
else {
  $conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
  // Delete any existing BLOB
  $query = 'DELETE FROM BTAB WHERE BLOBID = :MYBLOBID';
  $stmt = oci_parse ($conn, $query);
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  $e = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
  if (!$e) {
    die;
  } 
else {
  $conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
   // Delete any existing BLOB
  $query = 'DELETE FROM BTAB WHERE BLOBID = :MYBLOBID';
  $stmt = oci_parse ($conn, $query);
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  $e = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
  if (!$e) {
    die;
  }
  oci_free_statement($stmt);
  // Insert the BLOB from PHP's temporary upload area
  $lob = oci_new_descriptor($conn, OCI_D_LOB);
  $stmt = oci_parse($conn, 'INSERT INTO BTAB (BLOBID, BLOBDATA) '
  .'VALUES(:MYBLOBID, EMPTY_BLOB()) RETURNING BLOBDATA INTO :BLOBDATA');
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  oci_bind_by_name($stmt, ':BLOBDATA', $lob, -1, OCI_B_BLOB);
  oci_execute($stmt, OCI_DEFAULT);
  if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
    oci_commit($conn);
    echo "BLOB uploaded";
  }
  else {
    echo "Couldn't upload BLOB\n";
  }
  $lob->free();
  oci_free_statement($stmt);
}
}
?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/blobins.php

It shows a Web form with Browse and Upload buttons. Click Browse.

Select the oracle.jpg from the /home/oracle/public_html directory and click Open.

Click Upload.

The form action calls the script a second time, but now the special variable $_FILES['lob_upload'] is set and the echo statement is executed.

The image has been uploaded to the Web server.

To show the image, review the code as follows contained in the blobview.php file in the $HOME/public_html directory.

<?php
$myblobid = 1;
$conn = oci_connect("hr", "hrpwd", "//localhost/orcl");
// Now query the uploaded BLOB and display it
$query = 'SELECT BLOBDATA FROM BTAB WHERE BLOBID = :MYBLOBID';
$stmt = oci_parse ($conn, $query);
oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
oci_execute($stmt);
$arr = oci_fetch_assoc($stmt);
$result = $arr['BLOBDATA']->load();
header("Content-type: image/JPEG");
echo $result;
oci_free_statement($stmt);
oci_close($conn);
?>

From your Web browser, enter the following URL to display the output:

http://localhost/~phplab/blobview.php

Make sure there are no echo statements in the script or any whitespace before "<?php", because, otherwise the wrong HTTP header will be sent and the browser won't display the image properly. If you have problems, comment out the header() function call and see what is displayed.

Fetches all rows of result data into an array

Returns the next row from the result data as an associative or numeric array, or both

Returns the next row from the result data as an associative array

Returns the next row from the result data as an object