Developing and Deploying Oracle and PHP

Purpose

This tutorial shows you how to use PHP with Oracle Database 11g.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Creating a Standard Connection
 Creating a Persistent Connection
 Creating a Simple Query
 Creating Transactions
 Data Fetching Functions
 Using Bind Variables
 Using Stored Procedures
 Using Collections
 Using LOBs: Uploading and Querying Images
 Using XML
 Error Handling
 Summary
 Appendix: PHP Primer

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

PHP is a popular Web scripting language, and is often used to create database-driven Web sites. If you want to develop your Web application using PHP and an Oracle database, this tutorial helps you get started by giving examples on using PHP against Oracle. If you are new to PHP, review the Appendix: PHP Primer to gain an understanding of the PHP language.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database XE or any other Oracle Database 10g or 11g edition.

2.

Create a user "phpintro" and password (case sensitive) also  of "phpintro" with same sample tables as the default demonstration HR schema.

3.

Install the Apache web server and give UserDir access to $HOME/public_html

4.

Install PHP 5.2 with the OCI8 1.3 extension. In the php.ini file, set "display_errors = On".

5.

Download and unzip the phpintro.zip file into $HOME/public_html.

Back to Topic List

Creating a Standard Connection

To create a connection to Oracle that can be used for the lifetime of the PHP script, perform the following steps.

1.

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("phpintro", "phpintro", "//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 username, the password and the connection string. In this case, Oracle's Easy Connect connection string syntax is used. It consists of the hostname and the DB service name.

The oci_close() function closes the connection. Any standard connections not explicitly closed will be automatically released when the script ends.

 

2.

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

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

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

 

3.

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

select username, to_char(logon_time, 'DD-MON-YY HH:MI:SS') logon_time
from v$session
where username is not null;

exit

This is a SQL script file that you run in SQL*Plus (Oracle's command-line SQL scripting tool). This SQL*Plus script shows the current database sessions, and what time they logged into the database.

 

4.

Open a terminal window and enter the following commands to run the SQL script.

                               
cd $HOME/public_html
sqlplus -l phpintro/phpintro@//localhost/orcl @usersess.sql
                            

The SQL*Plus script lists the current database sessions. The only PHPINTRO session shown is for SQL*Plus. The PHP connections from the oci_connect() function has been closed.

 

Back to Topic List

Creating a Persistent Connection

A persistent connection to Oracle can be reused over multiple scripts. This improves PHP's overall performance because the Oracle connection is already created for subsequent scripts.

To create a persistent connection that can be reused over multiple PHP scripts, perform the following steps:

1.

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("phpintro", "phpintro", "//localhost/orcl");
if (!$conn) {
   $m = oci_error();
   echo $m['message'], "\n";
   exit;
}
else {
   print "Connected to Oracle!";
}

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

This code is almost identical to the Standard Connection example. 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.

Transactions do not span PHP scripts, and uncommitted data will be rolled back at the end of a script.

 

2.

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

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

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

 

3.

Reload the script several times. Different Apache child processes may or may not handle each request.

 

4.

Open a terminal window and enter the following commands to show the database connections are still open. Note that you could also exeucte the script in SQL Developer.

                               
cd $HOME/public_html
sqlplus -l -s phpintro/phpintro@//localhost/orcl @usersess.sql
                            

The SQL*Plus script lists the current database sessions. Depending how many Apache child processes handled the requests, you may see a different number of rows. Even though the oci_close() function was called, this does not close persistent connections, which remain available for other scripts.

PHP can be configured to close idle persistent connections. Oracle Database 11g introduced connection pooling for PHP that significantly reduces the number of connections open at any one time, while still giving the performance benefit of reusing open connections.

 

Back to Topic List

Creating a Simple Query

A common task when developing Web applications is to query a database and display the results in a Web browser. There are a number of functions you can use to query an Oracle database, but the basics of querying are always the same:

1. Parse the statement for execution.
2. Bind data values (optional) .
3. Execute the statement.
4. Fetch the results from the database.

To create a simple query, and display the results in an HTML table, perform the following steps.

1.

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("phpintro", "phpintro", "//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 a row of results of the query as an associative array, and includes nulls.

The htmlentities() function escapes any text resembling HTML tags so it displays correctly in the browser.

 

2.

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

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

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

 

Back to Topic List

Creating Transactions

When you manipulate data in an Oracle database (insert, update, or delete data), the changed or new data is only available within your database session until it is committed to the database. When the changed data is committed to the database, it is then available to other users and sessions. This is a database transaction.

By default, when PHP executes a SQL statement it automatically commits. This can be over-ridden, and the oci_commit() and oci_rollback() functions used to control transactions. At the end of a PHP script, any uncommitted data is rolled back.

Committing each change individually causes extra load on the server. In general you want all or none of your data committed.  Doing your own transaction control has performance and data-integrity benefits.

To learn about transaction management in PHP with an Oracle database, perform the following steps.

1.

Start SQL*Plus and create a new table:

                               
sqlplus phpintro/phpintro@//localhost/orcl
create table mytable (col1 date);
                            

 

2.

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

<?php

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

// PHP function to get a formatted date
$d = date('j:M:y H:i:s');

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

// Use OCI_DEFAULT to insert without committing
$r = oci_execute($s, OCI_DEFAULT);

echo "Data was rolled back\n";

?>

The OCI_DEFAULT parameter overrides the basic behavior of oci_execute()

 

3.

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

                               
http://localhost/~phpintro/trans_rollback.php
                            

This script inserts a row into the table.

 

4.

Because there is no automatic or explicit 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 to_char(col1, 'DD-MON-YY HH:MI:SS') time from mytable;
                            

 

5.

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

<?php

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

// PHP function to get a formatted date
$d = date('j:M:y H:i:s');

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

// Insert & commits
$r = oci_execute($s);

// The rollback does nothing: the data has already been committed
oci_rollback($conn);

echo "Data was committed\n";

?>

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 by the oci_execute() call.

 

6.

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

                               
http://localhost/~phpintro/trans_commit.php
                            

The data is now committed.

 

7.

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

                               
select to_char(col1, 'DD-MON-YY HH:MI:SS') time from mytable;
                            

If you reloaded the PHP script more than once, a row from each execution is inserted.

Remove all data from the table before continuing:

delete from mytable;
commit;

 

8.

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 trans_time_commit.php file in the $HOME/public_html directory.
This commits on each insert.

<?php

function do_insert($conn)
{
  $stmt = "insert into mytable values (to_date('01-JAN-08 10:20:35', 
       'DD:MON:YY HH24:MI:SS'))";
  $s = oci_parse($conn, $stmt);
  $r = oci_execute($s);  // automatically commit
}
function do_row_check($conn)
{
  $stid = oci_parse($conn, "select count(*) c from mytable");
  oci_execute($stid);
  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);
}

// Program starts here
$c = oci_connect("phpintro", "phpintro", "//localhost/orcl");

$starttime = microtime(TRUE);
for ($i = 0; $i < 10000; $i++) {
  do_insert($c);
}
$endtime = microtime(TRUE) - $starttime;
echo "Time was ".round($endtime,3)." seconds<br>";

do_row_check($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.

Make sure SQL*Plus is exited so the table is not locked.

 

9.

Now run the trans_time_explicit.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 after the insertion loop:

...

function do_insert($conn) {
  $stmt = "insert into mytable values (to_date('01-JAN-08 10:20:35', 
      'DD:MON:YY HH24:MI:SS'))";
  $s = oci_parse($conn, $stmt);
  
                              
 $r = oci_execute($s, OCI_DEFAULT);  // Don't commit
}

...

$starttime = microtime(TRUE);
for ($i = 0; $i < 10000; $i++) {
  do_insert($c);
}
                               
oci_commit($c);
$endtime = microtime(TRUE) - $starttime;

...
                            

Rerun the test. The insertion time is less.

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

 

Back to Topic List

Data Fetching Functions

There are a number of ways to fetch data from an Oracle database. You can fetch arrays as associative arrays, numeric arrays, or as both.

To learn how to use the array fetching functions, perform the following steps.

1.

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:

<?php

$conn = oci_connect("phpintro", "phpintro", "//localhost/orcl");
$query = 'select * from employees where employee_id = 101';
$stid = oci_parse($conn, $query);
oci_execute($stid);

echo "<pre>";
while ($row = oci_fetch_array($stid)) {
  var_dump($row); // display PHP's representation of $row
}
echo "</pre>";

oci_close($conn);

?>

The var_dump() function display's PHP internal representation of a variable. it is useful for quick data analysis and debugging.

 

2.

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

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

The output shows that the results contain both associative and numeric indices.

 

3.

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

Using a text editor, change the oci_fetch_array() call to the following:

oci_fetch_array($stid, OCI_ASSOC)

Rerun the following URL:

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

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

 

4.

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/~phpintro/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.

 

5.

Replace the var_dump() function statement to echo and print out only the first and last name columns.

Because you have OCI_NUM already specified for a numeric array, use

echo $row[1], " ", $row[2], "<br>";

Remove the WHERE clause so values from all rows in the table are displayed. The file should look as follows:

Rerun the following URL:

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

Note: If you want to fetch data as an associative array (OCI_ASSOC), the echo statement should be changed to the following:

echo $row['FIRST_NAME'], " ", $row['LAST_NAME'], "<br>";

You can use any HTML tags or CSS styles to make the output pleasing.

 

Back to Topic List

Using Bind Variables

Bind variables enable you to re-execute statements with new values, without the overhead of reparsing the statement. Bind variables improve code reusability, and can reduce the risk of SQL Injection attacks.

To use bind variables in this example, perform the following steps.

1.

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("phpintro", "phpintro", "//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);

?>

 

2.

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

                               
http://localhost/~phpintro/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.

 

Back to Topic List

Using Stored Procedures

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL procedures and functions are stored in the database. Using PL/SQL lets all database application s reuse logic, no matter how the application access es the database. Many data-related operations can be performed in PL/SQL faster than extracting the data into a program (for example, PHP) and then processing it. Oracle also supports Java stored procedures.

In this tutorial, you will create a PL/SQL stored procedure and call it in a PHP script . Perform the following steps:

1.

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

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

 

2.

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

 

3.

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

                               
<?php

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

?>     
                            

 

4.

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

                               
http://localhost/~phpintro/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;
                              
                            

 

5.

Extend proc.php to use a bind variable. Change proc.php to the following:

<?php

$c = oci_connect('phpintro', 'phpintro', '//localhost/orcl');
$s = oci_parse($c, "call myproc('mydata',  
                              
:bv)");
                               
$v = 456;
oci_bind_by_name($s, ":bv", $v);
oci_execute($s);
echo "Completed";

?>
                            

The oci_bind_by_name() function binds the PHP variable $v to ":bv" and experiment changing the value inserted by changing the value in $v.

Rerun the following URL:

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

Query the table again to show the new row:

                               
                                 
select * from ptab;
                              
                            

 

6.

PL/SQL stored functions are also commonly used in Oracle. In SQL*Plus, create a PL/SQL stored function myfunc() to insert a row into the ptab table, and return double the inserted 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;
/

                            

 

7.

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

<?php

$c = oci_connect('phpintro', 'phpintro', '//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

Run the following URL:

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

 

Back to Topic List

Using Collections

A PL/SQL collection is an ordered group of elements of the same type, for example, of the type array.

To work with PL/SQL collections in PHP, perform the following steps:

1.

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.

set echo on

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

exit

From a terminal window, execute the following command:

                               
sqlplus phpintro/phpintro@//localhost/orcl @proc2
                            

 

2.

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_fetch_all($stid, $res);
   var_dump($res);
   echo "</pre>";
}

for ($i = 0; $i < 10; $i++) {
   $a[] = 'value '.$i;
}

$c = oci_connect("phpintro", "phpintro", "//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 a PHP array of strings in $a. The array is then bound to the PL/SQL procedure's parameter.

 

3.

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

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

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

This example show how the number of oci_execute() calls needed to insert multiple values can be reduced to one, reducing overall system load. The bulk FORALL is also an efficient way of doing SQL operations in PL/SQL.

 

Back to Topic List

Using LOBs: Uploading and Querying Images

Oracle Character Large Object (CLOB) and Binary Large Object (BLOB) columns (and PL/SQL variables) can contain very large amounts of data. There are various ways of creating them to optimize Oracle storage. There is also a pre-supplied package DBMS_LOB that makes manipulating them in PL/SQL easy.

To create a small application to load and display images to the database, perform the following steps.

1.

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

                               
sqlplus phpintro/phpintro@//localhost/orcl
                               
create table btab (blobid number, blobdata blob);
                            

 

2.

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

<?php
if (!isset($_FILES['lob_upload'])) {
// If nothing uploaded, display the upload form
?>

<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
} // closing brace from 'if' in earlier PHP code
else {
  // else script was called with data to upload

  $myblobid = 1; // should really be a unique id e.g. a sequence number

  $conn = oci_connect("SUB_HOL_DB_USER", "SUB_HOL_DB_PASSWD", 
       "SUB_HOL_CONNECT_STRING");

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

  // 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);  // Note 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();
}

?>

This shows HTML code embedded in multiple PHP blocks. In particular, a PHP 'if' statement encloses the HTML code.The first time the script is loaded, the HTML upload form is shown. PHP has populated the form action name to call the same script again..

There is a direct relationship between the HTML form name name="lob_upload" and the special PHP variable $_FILES['lob_upload']. When the form is called with data, the script deletes any existing image from the table, and inserts the new picture.

The script shows the use of oci_new_descriptor() which is bound to the empty_blob() location. The LOB->savefile() method inserts the picture to the newly created row. Note the OCI_DEFAULT option to oci_execute() is necessary for the subsequent LOB method to work.

 

3.

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

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

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

 

4.

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

 

5.

Click Upload.

The form action calls the script a second time, but now the special variable $_FILES['lob_upload'] is set and picture is uploaded. The successful echo message is displayed.

The image has been uploaded to the Web server.

 

6.

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

<?php

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

$query = 'SELECT BLOBDATA FROM BTAB WHERE BLOBID = :MYBLOBID';
$stmt = oci_parse ($conn, $query);
$myblobid = 1;
oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
oci_execute($stmt);
$arr = oci_fetch_array($stmt, OCI_ASSOC);
$result = $arr['BLOBDATA']->load();

header("Content-type: image/JPEG");
echo $result;

oci_close($conn);

?>

 

 

7.

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

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

Make sure there is no whitespace before "<?php" and no echo statements in the script, 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.

 

Back to Topic List

Using XML

This tutorial covers the basics of using XML data with Oracle and PHP.

1.

You can fetch relational rows as XML. In this case, you will use the SQL XMLELEMENT function to retrieve the Name and ID of the Employees table where employee_id < 115. Review the code in the xml_string.php file in the $HOME/public_html directory.

<?php

$c = oci_connect('phpintro', 'phpintro', '//localhost/orcl');

$q = 'SELECT XMLElement("tree",
           XMLAttributes(employees.employee_id,
                         employees.last_name,
                         employees.department_id)) AS result
      FROM employees
      WHERE employee_id < 115';

$s = oci_parse($c, $q);
oci_execute($s);

echo "<pre>";
while ($r = oci_fetch_array($s, OCI_ASSOC)) {
  foreach ($r as $item) {
    print htmlentities($item)." ";
  }
  print "\n";
}
print "</pre>";

?>

 

2.

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

                               
http://localhost/~phpintro/xml_string.php
                            

 

3.

An alternative way of creating XML from relational data is to use the PL/SQL package DBMS_XMLGEN(), which returns a CLOB. Review the code in the xml_lob.php file in the $HOME/public_html directory.

<?php

$c = oci_connect('phpintro', 'phpintro', '//localhost/orcl');

$q = "select dbms_xmlgen.getxml(
       'select first_name
        from employees
        where department_id = 30') xml
      from dual";

$s = oci_parse($c, $q);
oci_execute($s);
$r = oci_fetch_array($s, OCI_ASSOC);
$mylob = $r['XML']->load();   // Treat column data as a LOB descriptor

echo "<pre>";
echo htmlentities($mylob);
echo "</pre>";

?>

The code in the file xml_lob.php does the following:

a) Fetches a single LOB locator into $r['XML']. The LOB method load() converts the data to a PHP string which is stored in $mylob

$mylob = $r['XML']->load();

b) dislays the content of $mylob, escaping any HTML entities so they display correctly in a browser

echo htmlentities($mylob);

 

4.

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

                               
http://localhost/~phpintro/xml_lob.php
                            

 

5.

An XML string can be converted to a PHP SimpleXML object for easy access in PHP. Any changes can be uploaded to Oracle.

First, create some XML data. . Review the code as follows that is contained in the xmldata.sql file in the $HOME/public_html directory.

set echo on
                              
create table myxtab (xt_id number, xt_spec xmltype);
insert into myxtab (xt_id, xt_spec) values
(1,
xmltype('<?xml version="1.0"?>
<Xt>
<XtId>1</XtId>
<Size>Big</Size>
<Area>451</Area>
<Fabric>Silk</Fabric>
<Color>Red</Color>
<Strength>20</Strength>
</Xt>'));
commit;
exit

From a terminal window, execute the following command:

                               
sqlplus phpintro/phpintro@//localhost/orcl @xmldata
                            

 

6.

Review the code in the xml_update.php file in the $HOME/public_html directory.

<?php

$c = oci_connect('phpintro', 'phpintro', '//localhost/orcl');

$q = 'select XMLType.getClobVal(xt_spec) xml from myxtab where xt_id = 1';
$s = oci_parse($c, $q);
oci_execute($s);
$r = oci_fetch_array($s, OCI_ASSOC);
$mylob = $r['XML']->load();   // Treat column data as a LOB descriptor

$xml = simplexml_load_string($mylob);

echo "<pre>";
var_dump($xml);
echo "</pre>";

$xml->Color = 'Blue';

// Insert changes using a temporary CLOB
$q = 'update myxtab set xt_spec = XMLType(:clob) where xt_id = 1';
$s = oci_parse($c, $q);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($xml->asXml());
oci_execute($s);
$lob->close();

?>

This code:

a) Converts the fetched XML string $mylob into a PHP SimpleXML object

$xml = simplexml_load_string($mylob);

b) Changes the color of the object

$xml->Color = 'Blue';

c) Uses the SimpleXML method asXml() to convert the XML object to an XML string

$xml->asXml()

d) Uses a temporary LOB to update the table

$lob->writeTemporary($xml->asXml());

 

7.

From your Web browser, enter the following URL to run the script:

                               
http://localhost/~phpintro/xml_update.php
                            

The PHP output displays the data before it is updated. The color is Red.

 

8.

Write a PHP script to query the table to verify the data was updated, or use SQL*Plus:

                               
sqlplus phpintro/phpintro@//localhost/orcl
set long 1000
column xt_spec format a60
select * from myxtab;
                            

The <Color> tag data has changed to Blue.

Error Handling

The PHP function oci_error() is useful when working with Oracle database error handling.

To practice some simple error handling, perform the following steps.

1.

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("phpintro", "phpintro", "//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 a parsing error, change the * to another character, such as '.
// To generate a runtime error, use an invalid table name
$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);

?>


2.

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

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

 

3.

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

                               
$conn = oci_connect("phpintro", "phpintroxx", "//localhost/orcl");
                            

 

4.

Reload the following URL:

                               
http://localhost/~phpintro/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.

In a production environment, PHP would be configured to log errors instead of displaying them.

 

5.

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

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

 

6.

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

                               
$query = "select ' from departments";
                            

 

7.

From your browser reload the following URL:

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

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

 

8.

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

$query = "select * from  
                              
sometable";
                            

 

9.

Rerun the following URL:

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

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

In the error array $e returned by oci_error() the offset key contains the location of the character at which the parsing error beings, and the sqltext parameter contains the script that caused the parsing error.

 

10.

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

 

11.

Rerun the following URL:

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

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

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Create a standard connection
 Create a persistent connection
 Create a simple query
 Use transactions
 Use the data fetch functions
 Use bind variables
 Call PL/SQL
 Use collections
 Use LOBs to Upload and query images
 Use XML
 Implement error handling

Back to Topic List

Appendix: PHP Primer

PHP is a dynamically typed scripting language. It is most often seen in Web applications but can be used to run command-line scripts. Basic PHP syntax is simple to learn. It has familiar loops, tests, and assignment constructs. Lines are terminated with a semi-colon.

Strings can be enclosed in single or double quotes:

'A string constant'
"another constant"

Variable names are prefixed with a dollar sign. Things that look like variables inside a double-quoted string will be expanded:

"A value appears here: $v1"

Strings and variables can also be concatenated using a period.

'Employee ' . $ename . ' is in department ' . $dept

Variables do not need types declared:

$count = 1;
$ename = 'Arnie';

Arrays can have numeric or associative indexes:

$a1[1] = 3.1415;
$a2['PI'] = 3.1415;

Strings and variables can be displayed with an echo or print statement. Formatted output with printf() is also possible.

echo 'Hello, World!';
echo $v, $x;
print 'Hello, World!';
printf("There is %d %s", $v1, $v2);

The var_dump() function is useful for debugging.

var_dump($a2);

Given the value of $a2 assigned above, this would output:

array(1) {
  ["PI"]=>
  float(3.1415)
}

Code flow can be controlled with tests and loops. PHP also has a switch statement. The if/elseif/else statements look like:

if ($sal > 900000) {
  echo 'Salary is way too big';
}
elseif ($sal > 500000) {
  echo 'Salary is huge';
}
  else {
  echo 'Salary might be OK';
}

This also shows how blocks of code are enclosed in braces.

A traditional loop is:

for ($i = 0; $i < 10; $i++) {
  echo $i;
}

This prints the numbers 0 to 9. The value of $i is incremented in each iteration. The loop stops when the test condition evaluates to false. You can also loop with while or do while constructs.

The foreach command is useful to iterate over arrays:

$a3 = array('Aa', 'Bb', 'Cc');
foreach ($a3 as $v) {
  echo $v;
}

This sets $v to each element of the array in turn.

A function may be defined:

function myfunc($p1, $p2) {
  echo $p1, $p2;
  return $p1 + $p2;
}

Functions may have variable numbers of arguments, and may or may not return values. This function could be called using:

$v3 = myfunc(1, 3);

Function calls may appear earlier than the function definition.

Sub-files can be included in PHP scripts with an include() or require() statement.

include("foo.php");
require("bar.php");

A require() will generate a fatal error if the script is not found.

Comments are either single line:

// a short comment

or multi-line:

/*
  A longer comment
*/

PHP scripts are enclosed in <?php and ?> tags.

<?php
  echo 'Hello, World!';
?>

When a Web server is configured to run PHP files through the PHP interpreter, loading the script in a browser will cause the PHP code to be executed and all output to be streamed to the browser.

Blocks of PHP code and HTML code may be interleaved. The PHP code can also explicitly print HTML tags.

<?php
  require('foo.php');
  echo '<h3>';
  echo 'Full Results';
  echo '</h3>';
  $output = bar(123);
?>
<table border="1">
  <tr>
    <td>
     <?php echo $output ?>
    </td>
  </tr>
</table>

Many aspects of PHP are controlled by settings in the php.ini configuration file. The location of the file is system specific. Its
location, the list of extensions loaded, and the value of all the initialization settings can be found using the phpinfo() function:

<?php
  phpinfo();
?>

Values can be changed by editing phpl.ini and restarting the Web server. Some values can also be changed within scripts by using the ini_set() function.

A list of the various oci functions include the following:

oci_bind_array_by_name

Binds PHP array to Oracle PL/SQL array by name

oci_bind_by_name

Binds the PHP variable to the Oracle placeholder

oci_cancel

Cancels reading from cursor

oci_close

Closes Oracle connection

oci_commit

Commits outstanding statements

oci_connect

Establishes a connection to the Oracle server

oci_define_by_name

Uses a PHP variable for the define-step during a SELECT

oci_error

Returns the last error found

oci_execute

Executes a statement

oci_fetch_all

Fetches all rows of result data into an array

oci_fetch_array

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

oci_fetch_assoc

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

oci_fetch_object

Returns the next row from the result data as an object

oci_fetch_row

Returns the next row from the result data as a numeric array

oci_fetch

Fetches the next row into result-buffer

oci_field_is_null

Checks if the field is NULL

oci_field_name

Returns the name of a field from the statement

oci_field_precision

Tell the precision of a field

oci_field_scale

Tell the scale of the field

oci_field_size

Returns the size of the field

oci_field_type_raw

Tell the raw Oracle data type of the field

oci_field_type

Returns data type of the field

oci_free_statement

Frees all resources associated with statement or cursor

oci_internal_debug

Enables or disables internal debug output

oci_new_collection

Allocates new collection object

oci_new_connect

Establishes a new connection to the Oracle server

oci_new_cursor

Allocates and returns a new cursor (statement handle)

oci_new_descriptor

Initializes a new empty LOB or FILE descriptor

oci_num_fields

Returns the number of result columns in a statement

oci_num_rows

Returns number of rows affected during statement execution

oci_parse

Prepares Oracle statement for execution

oci_password_change

Changes password of Oracle's user

oci_pconnect

Connect to an Oracle database using a persistent connection

oci_result

Returns a field's value from a fetched row

oci_rollback

Rolls back outstanding transaction

oci_server_version

Returns server version

oci_set_prefetch

Sets number of rows to be prefetched

oci_statement_type

Returns the type of an OCI statement

Back to Topic List

 Move your mouse over this icon to hide all screenshots.