Oracle Develop 2007

Developing and Deploying Oracle and PHP

Purpose

This hands-on-lab covers installation of PHP with Oracle and shows how to develop maintainable, scalable PHP applications. You'll walk away with all the knowledge needed to create highly functional PHP appplications for your enterprise.

Time to Complete

Approximately 2 hours.

Topics

This tutorial covers the following topics:

	1. Overview
	2. Prerequisites
	3. Installing Oracle Database XE
	4. Installing Zend Core for Oracle
	5. Editing and Running PHP Scripts
	6. PHP Primer
	7. Creating a Connection
	8. Creating a Simple Query
	9. Creating a Persistent Connection
	10. Creating Transactions
	11. More on Transactions
	12. Data Fetching Functions
	13. Tuning Data Prefetching
	14. Using Bind Variables
	15. Using PL/SQL
	16. Using Collection
	17. Using XML
	18. Error Handling
	19. Using LOBs
	20. Address Book Application
	21. Summary
	22. Related information

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.

1. Overview

In this tutorial you learn how to:

	Install Oracle Database XE on Linux
	Install Zend Core for Oracle on Linux
	Review the basics of the PHP language
	Use PHP to create a connection to an Oracle database
	Create a simple query, retrieve and format the data
	Create a persistent connection for reuse across multiple PHP scripts
	Create and manage transactions
	Fetch and retrieve data in different formats.
	Tune the OCI8 prefetching parameters
	Use bind variables and PL/SQL to improve the performance and the security of your PHP applications
	Use Collections
	Use XML
	Handle errors with database connections and queries
	Use LOBs: upload images into the database, then query and display them
	Putting most things you've learned together to build a PHP appliation

This tutorial is to be performed on a Linux operating system.

Back to Topic List

2. Prerequisites

Before starting this tutorial, you should:

In real lfe, you also need to check that libaio is installed using
rpm -qa | grep libaio

and if its version is lesser than libaio-0.3.99-2.i386 then install using
rpm -ivh libaio-0.3.99-2.i386.rpm

Back to Topic List

3. Installing Oracle Database XE

To install Oracle Database XE and unlock the default HR user, perform these steps.

cd /home/hol

rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm

/etc/init.d/oracle-xe configure

Back to Topic List

4. Installing Zend Core for Oracle

An alternative to manually installing Apache and PHP, is to install the preconfigured Zend Core for Oracle. The rest of this tutorial uses this installation of PHP.

Zend Core for Oracle installs PHP, the Oracle Instant Client, and configures Apache. To install Zend Core for Oracle, perform these steps.

su
Password: oracle

cd ~hol

tar -zxf ZendCoreForOracle-v1.5.0-Linux-x86.tar.gz

cd ZendCoreForOracle-v1.5.0-Linux-x86
./install

/usr/sbin/apachectl stop



/usr/sbin/apachectl start

Back to Topic List

5. Editing and Running PHP Scripts (Skip this section and go to Section #7)

Several text editors such as Gedit, Emacs, and VI are available on your machines. For this lab, we suggest to use Gedit. .Save your PHP scripts in the following directory:

$HOME/hol/public_html

Load your PHP scripts using the following URL:

http://localhost/~hol/<script_name>

Back to Topic List

6. PHP Primer

This part of the tutorial gives you an overview of the PHP language. If you are familiar with PHP, skip this section and move onto the next.

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 true. 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 or using the Zend Core for Oracle console, and restarting the web server. Some values can also be changed within scripts by using the ini_set() function.

Back to Topic List

7. Creating a Connection

1.	Create a new file connect.php in the ~hol/public_html directory.
2.	Copy the following code into the new file. <?php // Create connection to Oracle $conn = oci_connect("hr", "hr", "//localhost/XE"); if (!$conn) { print "Error connecting to Oracle"; exit; } else { print "Connected to Oracle!"; } // Close the Oracle connection oci_close($conn); ?>
3.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/connect.php You should see the following message if the connection is successful, or an error if it was not. Connected to Oracle!

Back to Topic List

8. Creating a Simple Query

1.	Create a new file query.php in the ~hol/public_html directory.
2.	Copy the following code into the new file. <?php // Create connection to Oracle $conn = oci_connect("hr", "hr", "//localhost/XE"); $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); ?>
3.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/query.php A table similar to the following table is displayed. 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury   1700 130 Corporate Tax   1700 140 Control And Credit   1700 150 Shareholder Services   1700 160 Benefits   1700 170 Manufacturing   1700 180 Construction   1700 190 Contracting   1700 200 Operations   1700 210 IT Support   1700 220 NOC   1700 230 IT Helpdesk   1700 240 Government Sales   1700 250 Retail Sales   1700 260 Recruiting   1700 270 Payroll   1700

Back to Topic List

9. Creating a Persistent Connection

A persistent connection to Oracle can be reused over multiple scripts. Changes made to the Oracle environment are reflected in all scripts that access the connection. This lesson demonstrates this by creating a persistent connection, then changing the Oracle environment with another script.

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

1.	Create a new file pconnect.php in the ~hol/public_html directory.
2.	Copy the following code into the new file. <?php // Create a persistent connection to Oracle // Connection will be reused over multiple scripts $conn = oci_pconnect("hr", "hr", "//localhost/XE"); if (!$conn) { print "Error connecting to Oracle"; exit; } else { print "Connected to Oracle!"; } // Close the Oracle connection oci_close($conn); ?>
3.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/pconnect.php You should see the following message if the connection is successful, or an error if it was not. Connected to Oracle!
4.	Create a new text file using your text editor, and save it in your ~hol directory as usersess.sql. Enter the following commands into the script file. 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.
5.	Start SQL*Plus (Oracle's command line SQL scripting tool) by selecting Applications > Oracle Database 10g Express Edition > Run SQL Command Line. Enter the following command to log into the database as the user SYSTEM: connect system/oracle@//localhost/XE All the SQL commands used in this tutorial can also be executed using Oracle Application Express' SQL Worksheet. It is your choice which tool you prefer.
6.	Run the script by entering the following command: @/home/hol/usersess.sql
7.	Even after the PHP script has finished the database connection is still open. USERNAME LOGON_TIME ------------------------------ ------------------ SYSTEM 19-OCT-06 12:05:27 HR 19-OCT-06 12:14:03
8.	To show that the persistent connection is being reused by other PHP scripts, and that the session settings are the same, create a new PHP script that reuses the existing connection. In your text editor, create a new file pconnect2.php and save it in the ~hol/public_html directory. <?php 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>"; } $c = oci_pconnect("hr", "hr", "//localhost/XE"); do_query($c, "select sysdate from dual"); $s = oci_parse($c, "alter session set nls_territory=germany"); $r = oci_execute($s); do_query($c, "select sysdate from dual"); ?>
9.	Load http://localhost/~hol/pconnect2.php into your browser. Note that the date format of the two queries differs. Reload the PHP script. To see effect you may need to do this a few times until the orignal Apache process is reused. The date format for both queries is the new format. The connection has been reused.
10.	Experiment with changing the oci_pconnect() to a normal oci_connect(). Run the SQL*Plus script usersess.sql again to see which connections are open. You may want to stop and restart Apache to close all currently opened database connections.

Back to Topic List

10. Creating Transactions

1.	Start SQL*Plus and enter the following commands to log into the database as the user HR and create a new table: connect hr/hr@//localhost/XE create table mytable (col1 date); exit
2.	Create a new file trans1.php in the ~hol/public_html directory.
3.	Copy the following code into the new file. <?php echo "<pre>"; 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); } } function do_connect() { $conn = oci_connect("hr", "hr", "//localhost/XE"); return($conn); } $d = date('j:M:y H:i:s'); $c1 = do_connect(); $s = oci_parse($c1, "insert into mytable values (to_date('" . $d . "', 'DD:MON:YY HH24:MI:SS'))"); $r = oci_execute($s, OCI_DEFAULT); do_query($c1); // query connection #1 $c2 = do_connect(); do_query($c2); // query connection #2 echo "</pre>"; ?>
4.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/trans1.php This script inserts a row into the table using connection $c1. In Oracle, new data is only visible in the original database session until it is committed. The use of OCI_DEFAULT in PHP means that data is not automatically committed. Although there is no commit, the query using $c2 is able to see the new value. This is because each time oci_connect() is called in a script, PHP actually returns the same database connection handle.
5.	Change the oci_connect() call to oci_pconnect() and then to oci_new_connect(). What is the difference?

Back to Topic List

11. More on Transactions

1.	Start SQL*Plus and enter the following commands to log into the database as the user HR and delete any rows from the table mytable: connect hr/hr@//localhost/XE delete from mytable; commit; exit
2.	Create a new file trans2.php in the ~hol/public_html directory.
3.	Copy the following code into the new file. <?php echo "<pre>"; function do_query($conn) { echo "Start Query\n"; $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); } echo "End Query\n"; } function do_insert($conn) { echo "Start Insert\n"; $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); echo "End Insert\n"; } $c = oci_connect("hr", "hr", "//localhost/XE"); do_insert($c); do_query($c); ?>
4.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/trans2.php Reload the page. You should see the same number of rows each time.
5.	Now edit the do_insert() function. Remove the OCI_DEFAULT parameter in the oci_execute() call changing the line to: $r = oci_execute($s); Re-load the script several times. The data is now automatically committed.
6.	Committing each row individually causes extra load on the server. You can compare the performance difference between committing each row individually versus at the end of the transaction. To test the difference, create a new file trans3.php in the ~hol/public_html directory. Copy the following code into the file. <?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", "hr", "//localhost/XE"); $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.
7.	Change the do_insert() function and add OCI_DEFAULT so it doesn't automatically commit, and add an explicit commit 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", "hr", "//localhost/XE"); $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.

Back to Topic List

12. Data Fetching Functions

There are a number of ways to fetch data from an Oracle database. To learn how to use the fetching parameters, perform the following steps.

1.	Create a file fetch.php and save it in the ~hol/public_html directory. <?php echo "<pre>"; $conn = oci_connect("hr", "hr", "//localhost/XE"); $query = 'select * from employees where employee_id = 101'; $stid = oci_parse($conn, $query); oci_execute($stid); while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) { var_dump($row); // display PHP's representation of $row } oci_close($conn); echo "</pre>"; ?>
2.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/fetch.php Output similar to the following is displayed. array(11) { ["EMPLOYEE_ID"]=> string(3) "101" ["FIRST_NAME"]=> string(5) "Neena" ["LAST_NAME"]=> string(7) "Kochhar" ["EMAIL"]=> string(8) "NKOCHHAR" ["PHONE_NUMBER"]=> string(12) "515.123.4568" ["HIRE_DATE"]=> string(9) "21/SEP/89" ["JOB_ID"]=> string(5) "AD_VP" ["SALARY"]=> string(5) "17000" ["COMMISSION_PCT"]=> NULL ["MANAGER_ID"]=> string(3) "100" ["DEPARTMENT_ID"]=> string(2) "90" }
3.	Try changing the oci_fetch_array() call to the following parameters. Note the output variations. oci_fetch_array($stid, OCI_ASSOC) oci_fetch_array($stid, OCI_NUM) oci_fetch_array($stid, OCI_BOTH) oci_fetch_array($stid, OCI_RETURN_NULLS) oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)

Back to Topic List

13. Tuning Data Prefetching

Pre-fetching is similar to the traditional Oracle array fetch. It maximizes data transfer from the database to PHP. The default number of rows to prefetch is set in php.ini. The number of rows to pre-fetch can be also be set per statement with the oci_set_prefetch() function.

Raise te default PHP memory allocation using the Zend Core GUI

Under Configuration

Resource Limits

To benchmark the fetch times for a query, perform the following steps.

1.	In Zend Core for Oracle's console, check the value of the oci8.default_prefetch parameter. Log in to the Zend Core for Oracle's console, http://localhost/ZendCore, using the password oracle: Select Configuration. Select Extensions. Expand the oci8 tree. Alternatively, load the phpinfo.php script in your public_html directory. Note down the value of the oci8.default_prefetch parameter.
2.	Create a new file called prefetch.php and save it in the ~hol/public_html directory.
3.	Copy the following code into the new file: <?php $prefetch = 1; $iterations = 1; function currTime() { $time = microtime(); $time = explode(' ', $time); $time = $time[1] + $time[0]; return $time; } function elapsedTime($start) { return (currTime() - $start); } $c = oci_connect("system", "oracle", "//localhost/XE"); $stid = oci_parse($c, "select * from all_objects"); for ($i = 0, $totalFetch = 0; $i < $iterations; $i++) { oci_execute($stid); oci_set_prefetch($stid, $prefetch); $start = currTime(); oci_fetch_all($stid, $res); // Check the query returns the expected results. // var_dump($res); $totalFetch += elapsedTime($start); } print "Prefetch is $prefetch :"; print " Average fetch time is: " . round($totalFetch/$iterations, 3); print " seconds ($iterations iterations)\n<br>"; ?>
4.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/prefetch.php The query needs to return large output to get reliable figures. Very large output may cause PHP to reach its pre-set memory limit (the memory_limit parameter) and no results will be shown. If you have problems, experiment with changing the query to: select object_name, object_id from all_objects
5.	Try changing the prefetch value to 100 to see the reduced fetch time: oci_set_prefetch($stid, 100); Experiment with values 1, 10, 100, and 1000. On a piece of paper, plot a rough graph of the performance change for the various prefetch values.

Back to Topic List

14. Using Bind Variables

Bind variables improve code reusability, and remove the risk of SQL Injection attacks.

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

1.	Create a new file bind.php in the ~hol/public_html directory.
2.	Copy the following code into the new file. <?php // Create connection to Oracle $conn = oci_connect("hr", "hr", "//localhost/XE"); // Use bind variable to improve resuability, and to // remove SQL Injection attacks. $query = 'select * from employees where employee_id = :eidbv'; $stid = oci_parse($conn, $query); $myeid = 101; oci_bind_by_name($stid, ":EIDBV", $myeid); $r = oci_execute($stid); // Fetch the results in an associative array print '<p>$myeid is ' . $myeid . '</p>'; 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):' ').'</>'; } print '</tr>'; } print '</table>'; // Redo query without reparsing SQL statement $myeid = 102; $r = oci_execute($stid); // Fetch the results in an associative array print '<p>$myeid is ' . $myeid . '</p>'; print '<table border=">'; 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); ?>
3.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/bind.php A table similar to the following table is displayed. $myeid is 101 101 Neena Kochhar NKOCHHAR 515.123.4568 21/SEP/89 AD_VP 17000   100 90 $myeid is 102 102 Lex De Haan LDEHAAN 515.123.4569 13/JAN/93 AD_VP 17000   100 90

Back to Topic List

15. Using PL/SQL

PL/SQL is Oracle's procedural language extension to SQL. Using PL/SQL stored procedures lets all database application reuse logic no matter how the application access 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.

To create a PL/SQL 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: connect hr/hr@//localhost/XE create table ptab (mydata varchar(20), myid number); Now create a stored procedure myproc in SQL*Plus 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; /
2.	Create a new file proc.php in the ~hol/public_html directory. Copy the following code into the file <?php $c = oci_connect('hr', 'hr', '//localhost/XE'); $s = oci_parse($c, "call myproc('mydata', 123)"); oci_execute($s); echo "Completed"; ?>
3.	Extend proc.php to query from the table to check the data has been inserted. Change proc.php to $s = oci_parse($c, "call myproc('mydata', :bv)"); Use oci_bind_by_name() to bind a PHP variable $v to ":bv" and experiment changing the value inserted by changing the value in $v. $v = 123; oci_bind_by_name($s, ":bv", $v);
4.	As well as stored procedures, PL/SQL stored functions are commonly used. In SQL*Plus, create a PL/SQL stored function myfunc(): 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); end; /
5.	Change your PHP code to call the function. Use an anonymous block like: $s = oci_parse($c, "begin :bv := myfunc('mydata', 123); end;"); The bind variable will need to be bound. This time, print the value of the bind variable $v after the oci_execute() has completed.

Back to Topic List

16. Using Collections

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

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

1.	Start SQL*Plus and execute the following commands: 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; / This creates 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.
2.	Create a new file coll.php in the ~hol/public_html directory. Copy the following code into the file: <?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", "hr", "//localhost/XE"); $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.
3.	Load http://localhost/~hol/coll.php in your browser. The values are queried back from the PTAB table to verify they have been inserted.
4.	Write a PHP function to loop over the array and do a SQL INSERT statement for each row. Add the currTime() and elapsedTime() functions from the earlier Tuning Data Prefetching section and benchmark the difference in performance between the two methods of insertion.

Back to Topic List

17. Using XML

All editions of Oracle database contain "XML DB". PHP5 has excellent XML capabilities. his lab covers the basics of returning XML data from Oracle to PHP.

1.

Fetching Relational Rows as XML Using the SQL XMLELEMENT function, we'll retrieve the Name and ID of the Employees table where employee_id < 115..<?php // xml1.php - XML fragment $c = oci_connect('hr', 'hr', '//localhost/XE'); $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); $res = oci_fetch_row($s); echo "<pre>"; while ($r = oci_fetch_assoc($s)) { foreach ($r as $item) { print htmlentities($item)." "; } print "\n"; } print "</pre>"; ?>

2.

Using PL/SQL Package DBMS_XMLGEN An alternative way of creating XML form relational data is to use the PL/SQL paclage DBMS_XMLGEN(), whicb returns a CLOB. The following code fragment: (xml2.php) a) retrieves the first name of employees in department 30 and stores the XML marked-up output in $mylob $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); $res = oci_fetch_row($s); $mylob = $res[0]->load(); // treat as LOB descriptor b) dislays the content of $mylob echo htmlentities($mylob); c) turns the CLOB into an XML Array using PHP's SmpleXML function. $xml = (array) simplexml_load_string($mylob);

Back to Topic List

18. Error Handling

To add simple error handling to this example, perform the following steps.

<?php

//Create connection to Oracle
$conn = oci_connect("hr", "hr", "//localhost/XE");
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 an error connecting to Oracle: " . 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 an error parsing your query: " . 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);
   print "There was an error executing your query: " . htmlentities($e['message']);
   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):' ').'</>';
   }
   print '</tr>';
}
print '</table>';

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

?>

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

$query = "select ' from departments";

$query = "select * from sometable";

$r=@oci_execute($stid);

Back to Topic List

19. Using LOB: 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 run: drop table btab; create table btab (blobid number, blobdata blob);
2.	Create a new file blobins.php in the ~hol/public_html directory.
3.	Copy the following code into the new file. <?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 { echo "Insert an image"; } ?>
4.	Open a web browser and enter the following URL to display the output: http://localhost/~hol/blobins.php It shows a web form with Browse and Upload buttons. Click Browse and select ~hol/lab/oracle.jpg on your disk. 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.
5.	Now, write code to insert the image into the database. First, replace the echo statement with code to clean out the BTAB table. This enables the script to be run multiple times. ... else { $conn = oci_connect("hr", "hr", "//localhost/XE"); // 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); }
6.	Extend this code to do an insert of the uploaded image. Immediately after the deletion code, add: else { $conn = oci_connect("hr", "hr", "//localhost/XE"); // 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); } This creates a PHP descriptor $lob. The descriptor is bound to the SQL statement by explicitly specifying the bind variable type OCI_B_BLOB: oci_bind_by_name($stmt, ':BLOBDATA', $lob, -1, OCI_B_BLOB); The -1 is a placeholder in this statement. The variable $_FILES['lob_upload']['tmp_name'] is the name of the file uploaded to the web server. The function $lob->savefile(...) reads from the uploaded file and inserts the data into the table.
7.	Load the script in a browser and upload ~hol/lab/oracle.jpg. The message "BLOB uploaded" is displayed.
8.	To show the image create a new file blobview.php in the ~hol/public_html directory. <?php $myblobid = 1; $conn = oci_connect("hr", "hr", "//localhost/XE"); // 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); ?>
9.	Load http://localhost/~hol/blobview.php in a browser. Make sure there are no echo statements in the script or any whitespace before "<?php", else 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

20. Create an Address Book

Create an application to practice the skills you have learned in this tutorial.

The application should let the user enter names and phone numbers. It should display all entries sorted by name.

Decide on the table definition. Do you want first and last names? Will you allow more than one number per person? Will you accept international standard phone number format with a "+" country code prefix? Will you allow punctuation in the number or will you accept only digits?

1.	Use SQL*Plus to create your table and any indexes.
2.	Create an HTML form (in PHP or mixed HTML/PHP script file) that prompts the user for a name and phone number and inserts the value into your table.
3.	For the form, you can use PHP code similar to the previous BLOB upload code in the Uploading and Querying Images section. The submit action can recursively call the same PHP script (as the BLOB example does) or call a second script.
4.	Extend the code to display all phone numbers in the address book.
5.	Change the input form to allow the user to enter more than one name/number at a time.
6.	Create an "installation" administration PHP script that drops the table if it exists (prompting the user beforehand if any records exist) and creates the table.
7.	Add PHP code to automatically reformat numbers to your chosen display scheme. Review the PHP manual section on regular expressions.
8.	Add HTML (or use CSS) markup to the application to allow the address book output to change the style.
9.	Add basic error handling (and test it).
10.	Extend the error handling. Review the PHP manual section on output control functions. Use these output buffering functions to build up output pages. If an error occurs, flush the output buffer and instead send a customized error page to the browser.
11.	Write some test cases for the application.
12.	Write some documention for the code.
13.	Rewrite your application using PHP objects.

Back to Topic List

21. Summary

In this tutorial, you have learned how to:

	Install Oracle Database XE on Linux
	Install Zend Core for Oracle on Linux
	Review the basics of the PHP language
	Use PHP to create a connection to an Oracle database
	Create a simple query, retrieve and format the data
	Create a persistent connection for reuse across multiple PHP scripts
	Create and manage transactions
	Fetch and retrieve data in different formats.
	Tune the OCI8 prefetching parameters
	Use bind variables and PL/SQL to improve the performance and the security of your PHP applications
	Use Collections
	Use XML
	Handle errors with database connections and queries
	Use LOBs: upload images into the database, then query and display them
	Putting things you've learned together to build an appliation

Back to Topic List

22. Related Information

	PHP Developer Centre on OTN
	PHP and Oracle Underground Manual
	Oracle Database XE and PHP Developer Guide
	Zend Core for Oracle
	PHP Discussion Forum on OTN
	Oracle Database Express Edition on OTN

Back to Topic List