DOWNLOAD Oracle Database XE Zend Core for Oracle

TAGS php, opensource, xe, All

PHPfest Tutorial: Oracle Database 10g Express Edition and Zend Core for Oracle

A step-by-step guide to installing Oracle Database XE and Zend Core for Oracle, as well as using the PHP OCI8 extension

Published March 2006

Installing Oracle Database XE and Zend Core for Oracle

This section covers the installation of:

• Oracle Database XE
• Zend Core for Oracle

• 512MB Memory
• 5GB of hard drive space
• Red Hat Enterprise Linux (RHEL) 3
• RHEL 4
• Novell SUSE Linux Enterprise Server (SLES) 9

Oracle Database XE

• Developers working on PHP, Java, .NET and other applications that require a database
• DBAs who need a free starter database for training and deployment
• Independent Software Vendors (ISVs) who want to embed an Oracle database in their application or product
• Educational institutions and students who need a free starter database

• SQL, PL/SQL
• Java, C, and PHP
• Windows .NET
• Oracle Application Express
• C++, ODBC, OLE DB

• Up to 4GB of user data
• A single database instance
• A single CPU used
• 1GB RAM used

Installing Oracle Database XE

1. Log in or su as root

   su
   Password:
   

2. Copy oracle-xe-10.2.0.1-1.0.i386.rpm to /tmp, then change directory to /tmp.

   cp oracle-xe-10.2.0.1-1.0.i386.rpm /tmp
   cd /tmp
   

3. Install the RPM

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

   Oracle Database XE installs.
   
   
4. Configure the database.

   /etc/init.d/oracle-xe configure
   

5. Accept the default ports of 8080 for Application Express, and 1521 for the Database Listener.
   
   
6. Enter and confirm the password for the default users.
   
   
7. Enter Y or N for whether you want the database to start automatically on reboot.
The database and database listener are configured and started.

Testing the Oracle Database XE Installation

1. Open a web browser and enter:

   http://127.0.0.1:8080/apex
   

2. Log in as user system with the password you entered during the installation.

Zend Core for Oracle

Installing Zend Core for Oracle

1. Log in or su as root if you are not already.

   su
   Password:
   

2. Copy ZendCoreForOracle-v1.3.1-Linux-x86.tar.gz to /tmp, then change directory to /tmp.

   cp ZendCoreForOracle-v1.3.1-Linux-x86.tar.gz /tmp
   cd /tmp
   

3. Extract the contents of the downloaded Zend Core for Oracle software:

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

   Files are extracted to a subdirectory called ZendCoreForOracle-v1.3.1-Linux-x86.
   
   
4. Change directory to ZendCoreForOracle-v1.3.1-Linux-x8 and start the Zend Core for Oracle installation:

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

5. In the initial Zend Core for Oracle Installation page, click OK.
   
6. In the Zend Core for Oracle V.1 page, click Exit.
7. When prompted to accept the terms of the license, click Yes.
8. If you have an existing version of PHP installed, you are prompted to back up and overwrite the existing php.ini file. Click Yes.
9. When prompted to specify the location for installing Zend Core for Oracle, accept the default (or enter your preferred location), and click OK. The installer begins extracting the files required for the installation.
10. When the progress window indicates that all the software has been installed, you are prompted to "Please enter the GUI password." In the Password field, enter the password you want to use when accessing the Zend Core Console, and click OK.
11. When prompted to "Verify the password," enter the same password as specified in the previous step and click OK.
12. In the Zend Core support page, you may optionally enter your Zend network user ID and password to be able to use the Zend Core Console to track when updates to Zend Core and PHP components are available. If you have not registered, or do not want to track updates, click No.
13. The next page prompts you to select the Web server for Zend Core installation. Select the default Apache installed with Linux. Click OK.
14. In the page confirming your Web server selection, at the "Do you wish to proceed?" prompt, click Yes.
15. In the next installation page, you are prompted to "Please select an installation method for Apache 2.0.52." Select Apache module as the method, and click OK.
16. In the next installation page, when you are prompted to "Please select a virtual server for the Zend Core GUI," select Main Server, and click OK.
17. In the next installation page, at the "Would you like to restart the Web Server" prompt, click Yes.
18. In the next installation page, a notice is displayed stating that the apachectl script has been updated. Click OK.
19. The next installation page (containing "Thank you for installing Zend Core for Oracle") lists useful configuration commands and a Web page for the administration of the Zend Core engine. Take note of the information and click Exit.
20. A final confirmation page is displayed. Click OK to finish the installation.

Configuring Zend Core for Oracle

1. Enter the following URL in a Web browser to access the Zend Core Administration page: http://127.0.0.1/ZendCore
2. Enter the GUI password that you provided during Zend Core for Oracle installation. Click the login >>> icon.
3. Click the Configuration tab to display the configuration options.
4. Click the + icon to expand the Error Handling and Logging configuration entry.
5. Set the display_errors directive to On to enable the display of errors in the HTML script output during development.
6. Because there are unsaved changes, the "Unsaved configuration" message appears under the page header. Click Save Settings to save the configuration change.
7. Because you have made configuration changes, you must restart the Apache Web server. Under the page header notice the "Please Restart Apache" message reminding you to do so. Click Restart Server to restart the Apache server.
8. Click Logout to exit the Zend Core for Oracle Administration page.

Testing the Zend Core for Oracle Installation

1. Create a public virtual directory as public_html. Edit APACHE_HOME/conf/httpd.conf and remove the "#" from the following line:

   #UserDir public_html
   

2. As your normal user (not root), create a directory called public_html in your home directory, and change directory to the newly created directory, enter the following commands in a command window:

   cd $HOME
   mkdir public_html
   cd public_html
   

3. Create a file called hello.php that contains the following PHP code:

   <?php
   echo "Hello world!";
   ?>
   

4. Open a Web browser and enter the following URL in your browser:

   http://127.0.0.1/~<username>/hello.php
   

   The line "Hello world!" appears in the browser.

The PHP OCI8 Extension

<?php
  $conn = oci_connect('hr', 'hrpw', '//localhost/XE');

  $stid = oci_parse($conn, 'select city from locations');
  oci_execute($stid);

  print '<table border="1">';
  while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {
    print '<tr>';
    foreach ($row as $item) {
      print '<td>'.($item?htmlentities($item):' ').'</td>';
    }
    print '</tr>';
  }
  print '</table>';
?> 

Connecting to Oracle

$c = oci_connect($username, $password, $dbname)

$c = oci_new_connect($username, $password, $dbname)

$c = oci_pconnect($username, $password, $dbname)

• oci8.max_persistent: this limits the number of persistent connections cached. When the limit is reached, all oci_pconnect() calls are treated like oci_connect() calls. Setting it to -1 (the default) means there is no limit.
• oci8.persistent_timeout: the length in seconds an Apache process maintains an idle persistent connection. The expiry check happens whenever a PHP script finishes regardless of whether the script calls oci8 functions. Setting it to -1 (the default) means there is no timeout. If a connection has been expired, oci_pconnect() will create a new connection.
• oci8.ping_interval: the number of seconds that pass before oci8 does a ping during oci_pconnect(). When set to 0, PHP pings the database each time oci_pconnect() is called. To disable pinging, set the value to -1. The default value is 60 seconds. If the ping determines the connection is no longer usable, a new connection is created.

Connection Strings

1. Easy Connect string
2. Full connection string
3. tnsnames.ora alias

//hostname:port/service_name

$c = oci_connect('hr', 'hrpw', '//localhost/XE');

$db = MYDB2 = (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)
                  (HOST = mymachine.mydomain)(PORT = 1521))
                  (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = MYDB.AU.ORACLE.COM)))';

$c = oci_connect($un, $pw, $db);

# tnsnames.ora
    MYDB2 = (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)
                (HOST = mymachine.mydomain)(PORT = 1521))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = MYDB.AU.ORACLE.COM)))

$c = oci_connect($un, $pw, 'MYDB2');

$ORACLE_HOME/network/admin/tnsnames.ora

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/tnsnames.ora

Oracle Environment Variables

#!/bin/sh

TNS_ADMIN=/usr/local/apache/conf
export TNS_ADMIN
echo Starting Apache
#export > /tmp/envvars
/usr/local/apache/bin/apachectl start

Closing a Connection

oci_close($c);

$c1 = oci_connect('hr', 'hrpw', '//localhost/XE');
$c2 = oci_connect('hr', 'hrpw', '//localhost/XE');

do_query($c1, 'select user from dual');
oci_close($c1);
do_query($c1, 'select user from dual');
do_query($c2, 'select user from dual');
oci_close($c2);

Executing Statements

1. Parse - Prepare a statement for execution
2. Bind - Optionally lets you bind data values e.g. in the WHERE clause, for better performance and security
3. Define - Optional step allowing you to specify which PHP variables will hold the results. (Uncommon?)
4. Execute - The database processes the command and buffers the results.
5. Fetch - Get query results back from the database. Oci8 has a bunch of fetch statements to choose from.

Queries

$stid = oci_parse($c, 'select * from locations');
oci_execute($stid, OCI_DEFAULT);
oci_fetch_all($stid, $res);

$stid = oci_parse($c, "select * from locations where city = 'Sydney'");

• oci_fetch_all(): get all results at once
• oci_fetch_array(): get next row as an array of your choice
• oci_fetch_assoc(): get next row as an associative array
• oci_fetch_object(): get new row as an object
• oci_fetch_row(): get next row as an integer indexed array
• oci_fetch(): used with oci_result(), which returns the result of a given field

$stid = oci_parse($c, "select city from locations");
oci_execute($stid, OCI_DEFAULT);
while ($res = oci_fetch_row($stid)) {
   echo $res[0] . "<br>\n";
}

• oci8.default_prefetch: the number of records to be returned by Oracle when each database fetch occurs. The default value is 10. Tuning this setting can significantly improve performance of queries that return a large number of rows. It minimizes database server "round-trips" by returning as much data as possible each time. Oracle will cache the data in its client buffers and give PHP only the rows PHP itself requests.
• oci8.statement_cache_size: enables OCI client statement caching. The default is 20 statements. Caching can be disabled by setting the value to 0. The client-side statement cache means even the text of the statement does not need to be transmitted to the database, further reducing network traffic and database server load. The cache is per-Oracle session so this feature is most likely to be useful when persistent connections are used.

Insert/Update/Delete/Create/Drop

$s = oci_parse($c1, "create table i1test (col1 number)");
$r = oci_execute($s, OCI_DEFAULT);

$s = oci_parse($c1, "insert into i1test values (1)");
$r = oci_execute($s, OCI_DEFAULT);

Transactions

$r = oci_execute($s, OCI_DEFAULT);

$s = oci_parse($c, "insert into i2test values ('row 1')");
$r = oci_execute($s);

$s = oci_parse($c, "insert into i2test values ('row 2')");
$r = oci_execute($s, OCI_DEFAULT);

Error Handling

$c = oci_connect("hr", "hr", "//localhost/XE");
if (!$c) {
  $e = oci_error();  // no resource passed
  var_dump($e);
}

$stid = oci_parse($c, "select city from locations");
if (!$stid) {
  $e = oci_error($c);   // connection resource passed
  var_dump($e);
}

$rc = oci_execute($stid, OCI_DEFAULT);
if (!$rc) {
  $e = oci_error($stid);  // statement resource passed
  var_dump($e);
}

$rc = oci_fetch_all($stid, $results);
if (!$rc) {
  $e = oci_error($stid);  // statement resource passed
  var_dump($e);
}

Bind Variables

$stid = oci_parse($c,
             "select last_name from employees where employee_id = :eidbv");
$myeid = 101;
oci_bind_by_name($stid, ":EIDBV", $myeid);
oci_execute($stid, OCI_DEFAULT);
oci_fetch_all($stid, $res);
echo "Last name is: ". $res['LAST_NAME'][0] ."\n";

// No need to re-parse
$myeid = 102;
oci_execute($stid, OCI_DEFAULT);
oci_fetch_all($stid, $res);
echo "Last name is: ". $res['LAST_NAME'][0] ."\n";

There is one case where you might decide not to use bind variables. When statements contain bind variable the optimizer does not have any information about the value you may eventually use. If your data is highly skewed you might want to hard code values. But if the data is derived from user input be sure to sanitize it.

PL/SQL Stored Procedures

$stid = oci_parse($c, "begin myproc('mydata', 123); end;");
oci_execute($stid, OCI_DEFAULT);

PL/SQL REF CURSORS

CREATE OR REPLACE PACKAGE cv_types AS
  TYPE EmpInfoTyp is REF CURSOR;
  PROCEDURE EmpInfoRpt (jid IN VARCHAR, emp_cv IN OUT EmpInfoTyp);
END cv_types;
/

CREATE OR REPLACE PACKAGE BODY cv_types AS
PROCEDURE EmpInfoRpt (jid IN VARCHAR, emp_cv IN OUT EmpInfoTyp) AS
  BEGIN
    OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
                    FROM EMP_DETAILS_VIEW WHERE JOB_ID=jid;
   END;
END;
/

$job_id = 'SA_MAN';
$stmt = "BEGIN cv_types.EmpInfoRpt(:jid, :rc); END;";

$stid = oci_parse($c, $stmt);
oci_bind_by_name($stid, ':jid', $job_id);

$refcur = oci_new_cursor($c); // pass the connection
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);

// Execute the call to EmpInfoRpt()
oci_execute($stid);

// Execute and fetch from the cursor
oci_execute($refcur);
echo '<table border="1">';
while($row = oci_fetch_assoc($refcur)) {
  echo '<tr>';
  foreach ($row as $c) {
    echo "<td>$c</td>";
  }
  echo '</tr>';
}
echo '</table>';

Large Objects (LOBS)

$myv = 'a very large amount of binary data';

$lob = oci_new_descriptor($c, OCI_D_LOB);
$stid = oci_parse($c,
         'INSERT INTO mybtab (blobid, blobdata) '
       . 'VALUES(123, EMPTY_BLOB()) RETURNING blobdata INTO :blobdata');
oci_bind_by_name($stid, ':BLOBDATA', $lob, -1, OCI_B_BLOB);
oci_execute($stid, OCI_DEFAULT);

$lob->save($myv);

$query = 'SELECT blobdata FROM mybtab WHERE blobid = 123';

$stmt = oci_parse ($conn, $query);
oci_execute($stmt, OCI_DEFAULT);
$arr = oci_fetch_assoc($stmt);
$result = $arr['BLOBDATA']->load();

$arr['BLOBDATA']->seek(10, OCI_SEEK_SET);
$result = $arr['BLOBDATA']->read(50);

Oracle Collections

SQL> drop table emails;

SQL> create table emails (
       user_id       varchar2(10),
       friend_name   varchar2(20),
       email_address varchar2(20));

SQL> create or replace type email_array as
       varray(100) of varchar2(20);
     /

SQL> create or replace type friend_array as
       varray(100) of varchar2(20);
     /

SQL> create or replace procedure update_address_book(
       p_user_id         in varchar2,
       p_friend_name        friend_array,
       p_email_addresses    email_array)
     is
     begin
       delete from emails where user_id = p_user_id;
       for i in 1 .. p_email_addresses.count loop
         insert into emails (user_id, friend_name, email_address)
                     values (p_user_id, p_friend_name(i),
                             p_email_addresses(i));
       end loop;
     end update_address_book;
     /

$user_name      = 'cjones';
$friends_names  = array('alison', 'aslam');
$friends_emails = array('alison@example.com', 'aslam@example.com');

$friend_coll = oci_new_collection($c, 'FRIEND_ARRAY');
$email_coll  = oci_new_collection($c, 'EMAIL_ARRAY');

for ($i=0; $i < count($friends_names); $i++) {
  $friend_coll->append($friends_names[$i]);
  $email_coll->append($friends_emails[$i]);
}

$stid = oci_parse($c,
      "begin update_address_book(:name, :friends, :emails); end;");

oci_bind_by_name($stid, ':name',    $user_name);
oci_bind_by_name($stid, ':friends', $friend_coll, -1, OCI_B_NTY);
oci_bind_by_name($stid, ':emails',  $email_coll,  -1, OCI_B_NTY);

oci_execute($stid);

SQL> drop table mytab;
SQL> create table mytab(name varchar2(20));

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

SQL> create or replace package body mypkg as
       cursor cur is select name from mytab;
       procedure myproc(p1 in out arrtype) is
         begin
         for i in 1 .. p1.count loop
           insert into mytab values (p1(i));
         end loop;
       end myproc;
     end mypkg;
     /

$s = "BEGIN mypkg.myproc(:c1); END;";
$stid = oci_parse($c, $s);

$array = array("abc", "def", "ghi", "jkl", "mno");
oci_bind_array_by_name($stid, ":c1", $array, 5, -1, SQLT_CHR);
oci_execute($stid, OCI_DEFAULT);
oci_commit();

SQL> select * from mytab;

NAME
--------------------
abc
def
ghi
jkl
mno

Globalization

<language>_<territory>.<character set>

GERMAN_GERMANY.AL32UTF8

$c = oci_connect("hr", "hr", "//localhost/XE", 'ja16euc');

Using XML

$query =
  'SELECT XMLELEMENT("Employees",
     XMLELEMENT("Name", employees.last_name), XMLELEMENT("Id", employees.employee_id)) as result
    FROM employees
    WHERE employee_id > 200';

$stid = oci_parse($c, $query);
oci_execute($stid, OCI_DEFAULT);
while ($row = oci_fetch_row($stid))
  foreach ($row as $item)
    echo htmlentities($item)." ";

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

$stid = oci_parse($c, $query);
oci_execute($stid, OCI_DEFAULT);
$res = oci_fetch_row($stid);
$mylob = $res[0]->load();       // treat result as a LOB descriptor

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <FIRST_NAME>Den</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Alexander</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Shelli</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Sigal</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Guy</FIRST_NAME>
 </ROW>
 <ROW>
  <FIRST_NAME>Karen</FIRST_NAME>
 </ROW>
</ROWSET>

Onward and Upward