Oracle 10g XE and PHP

In case you missed it, yesterday Oracle announced a free (as in beer) version of their database – Oracle 10g Express Edition (XE) – basically a “lite” version – some industry analysis here. Significance of this move aside, more interesting is having a play. Managed to get the equivalent of a “Hello World” from PHP to Oracle up in under 1.5 hours today (ran into a specific glitch that required a re-install otherwise would have been less time). Here’s how…

Download is available at here. Be warned you’ve got a number of links to follow, an online license form to agree (check all the boxes) and finally a registration to fill in, before you get to the actual download. The download itself is about 150Mb and your system will need at least 250Mb RAM available to actual run the DB.

In my case installed it under Windows XP. There’s also a Linux version which I’ll be trying under Ubuntu later this week (had some hard disk issues recently which meant reinstall everything). Nothing available for OSX yet (the standard Oracle DB does support OSX though, so expect it’s a matter of time).

Once you’ve downloaded it, run the installer. It will ask you for a password for the “systen” user (think “root” user) which you should make sure you remember. The rest of the installation is just a matter of clicking “Next”. When finished it fires up a browser so you can login to your local instance of HTMLDb – something roughly equivalent to phpMyAdmin. In general, if you’ve ever tried installing the full Oracle database, this installation is breeze by comparison (not even a Java based installer – InstallShield I believe but could be wrong).

To login, you need the username “system” and the password you entered in the install process. You then need to following the instructions provided in the “Getting Started” guide (the installer adds a link to the guide on your start menu), in particular how to enable the “HR” user.

With that done, the next step is PHP. First things first you need to create a couple of environment variables (Control Panel > System > Advanced > Environment Variables );

  • ORACLE_SID = XE (…think of this as the name of your database)
  • ORACLE_HOME = C:oraclexeapporacleproduct10.2.0server (…assuming a default installation path)

If you’re running Apache as a Windows service, the easiest thing to do now is reboot Windows.

Now edit your php.ini and uncomment extension=php_oci8.dll (I assume here you have extension_dir pointing at the right place) – note I’m running PHP 5.0.5 and the code below reflects that. Restart Apache and check out your phpinfo(). If you don’t get error messages about being unable to find php_oci8.dll, all is probably good. If you do get that error message, either the dll really can’t be found (check it exists in your extension_dir) or the above two environment variables are incorrectly set (or Apache is running as a service and you haven’t yet rebooted Windows).

Assuming all went well and you can see the OCI8 extension listed by phpinfo(), here’s something like a “Hello World”…

<?php
$conn = oci_connect('hr','hr'); // This assumes you followed the Getting Start guide...

$sql = "SELECT * FROM employees";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt);

echo '<pre>';

while ( $row = oci_fetch_assoc($stmt) ) {
  print_r($row);
}

echo '</pre>';
?>

Oracle has a bunch more info for PHP developers here.

From reading around and the little playing I’ve done, apart from the resource restrictions (one db per system, 1GB or mem usage max, 4GB database size max), it seems to have all the functionality as a normal Oracle DB (would be interested to hear more info on that if anyone has some).

Note that glitch I encounted, which may help non-US users, was I first attempted installing with Windows set to Swiss German as preferred language, which led to an ORA-27101 when I tried to login with SQL*Plus (and HTMLDb wouldn’t start). Switching to US English (Control Panel > Regional Settings > make everything “US English” on all tabs) solved this after a re-install.

Anyway – overall a pleasant installation experience. Oracle have definately “worked it out” in making getting started pain-free. Whether this will result in a big uptake amongst those that might otherwise choose an Open Source DB is hard to say. With the resource restrictions placed on the use of Oracle XE I doubt it’s really an option for shared hosts but it could be useful for software vendors who need to distribute a DB with their products. For developers it’s certainly a good place to start getting to know Oracle.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • http://www.designity.nl peach

    Oracle 10g XE and PHP
    Harry Fuecks in PHP
    -1 years, 12 months ago

    wow according to the frontpage this blog post is 1 year.. and twelve months old.
    lol

  • Ivo Jansch

    Note that it should not be necessary to set the ORACLE_SID environment variable. You can pass the SID as third parameter to oci_connect. This makes it easier to connect to different databases.

  • http://www.phpsimplicity.com NeverMind

    altho it might sound exciting but who would download a 150+ MB software with restrection applied and most importantly not really free!

    BETA TRIAL LICENSE: Oracle Corporation (“Oracle”) grants to you a no-charge trial license to use the pre-production beta version of the Oracle Database Express Edition software, documentation and product training (the “Software”) provided to you by Oracle solely for evaluation purposes until January 31, 2006. Either party may terminate the license for the Software at any time. Upon termination, you shall cease using the Software. You will ensure that all your personnel who may access and/or use the Software during the trial period specified above will comply with the terms of this agreement.

    this is quoted from The OTN License Agreement terms.

    presonally, I’d rather use MySQL or PostgreSQL over Oracle XE.

  • ikeo

    I’m running 10g on my laptop right now and its killing me!
    I only have 512MB of RAM and if the Oracle database starts up automatically it takes up all the available ram (I have 64MB free ram), so I set the service to start manually.
    Even then it eats up about 197MB of RAM when I start the service.

    Does Oracle XE use less Memory?

    I only need it for development and testing so if it does, I will uninstall 10g and put in 10xe.

  • http://www.phppatterns.com HarryF

    presonally, I’d rather use MySQL or PostgreSQL over Oracle XE.

    That’s fair enough. I’m thinking more in terms of this being a good starting point to learn about Oracle, given significant demand for expertise.

    It might also be useful for Open Source PHP projects that want their code to run against multiple databases – where I work MySQL is vaguely acknowledged as a “toy database” while PostgreSQL isn’t even on the map. If I want to install something like a PHP-based bug tracker, it’s taken a lot more seriously if it supports Oracle.

    I only have 512MB of RAM

    In my case running it on a laptop with 1GB – not noticing the impact. That said it’s probably worth thinking in terms of running it on a seperate box, if possible. In such cases it may be worth looking at the Instant Client as a quick way to hook up to your server. There’s a tutorial about how to set it up with PHP here.

    Does Oracle XE use less Memory?

    As far as I know, no – the 256Mb minimum requirement is the same as that for the normal 10g database. It really seems like XE is the full database but with resource limitations applied.

  • Henri

    Glad to read you again on this blog Harry!

  • scorps

    Today when MySql 5.0 is out, you can’t say anymore that its a toy database.

  • http://www.dotcomwebdev.com chris ward

    Im keen, and Harry’s right about it being in demand…
    so if hands-on experience is being offered on a PHP plate, you just can’t pass it up!

    So what does oracle have that MS-SQL/mySQL5 doesn’t???

    Will I end up preferring it to MS-SQL/mySQL ?

    I heard it’s the database of choice from db gurus, if that’s the truth or not… i’d like to know

  • Wombert

    No need to restart Windows. Running “net stop apache” followed by “net start apache” from the Windows+R box is enough.

  • http://www.phppatterns.com HarryF

    Running “net stop apache” followed by “net start apache” from the Windows+R box is enough.

    Are you sure that works? My understanding is Windows loads environment variables for services only at boot time. That means Apache as a service would only see the new variables on the next boot (stopping and starting the service is not enough). I (think – not 100% here) I’ve got round this before by uninstalling and reinstalling the Apache service.

  • Dr Livingston

    Thanks :)

  • http://timvw.madoka.be timvw

    I’m starting to like Oracle more and more ;)

    Their JSF components, i believe ADF, are really impressing…
    And the JDeveloper IDE is also very nice…

    Playing with those made me wonder where the PHP equivalents are..

  • Wombert

    Are you sure that works? My understanding is Windows loads environment variables for services only at boot time. That means Apache as a service would only see the new variables on the next boot (stopping and starting the service is not enough). I (think—not 100% here) I’ve got round this before by uninstalling and reinstalling the Apache service.

    Pretty sure. Environment variables are available as soon as you enter them in the System Preferences settings dialog. I just used this the other day to install F# for Visual Studio which needs certain env vars to be present, otherwise it bails out with an error message.

  • qtstorm

    This here is a bit confusing…

    apart from the resource restrictions (one db per system, 1GB or mem usage max, 4GB database size max).

    If I’m reading the Oracle website correctly, it should read 1 Instance - which hopefully allows more than 1 database

    * is limited to a single instance per system;

    because I gotta tell ya, it would be kinda hard to justify a 150mb download if all I can run is one database.

  • Giacomo

    The “embedding or redistribution” part is funny: who would add a 150Mb installer of a crippled database to their software? I understand shipping BDB, SQLite, even Mysql, Postgre o some java db… but a 150Mb closed-source installer for a closed-source database with huge hardware requirements and performance limits? I don’t see the point.

    I suppose they only hope to get kids hooked, but they tried that already and it didn’t really work…

  • http://www.phppatterns.com HarryF

    Their JSF components, i believe ADF, are really impressing…
    And the JDeveloper IDE is also very nice…

    Playing with those made me wonder where the PHP equivalents are..

    May be this will appeal (or one of many others) – many of these have some kind of UI “components”, even if they’re just PHP functions you call from a template. Or wait for this to be released

    If I’m reading the Oracle website correctly, it should read 1 Instance – which hopefully allows more than 1 database

    Yep – my fault – was moving too fast.

  • Anonymous

    Apples and Pears….

    Oracle have never said it was a open source database their statement is as below.

    Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level, small-footprint (150MB) database based on the Oracle Database 10g Release 2 code base that’s free to develop, deploy, and distribute; fast to download; and simple to administer. Oracle Database XE is a great starter database for:

    Developers working on PHP, Java, .NET, and Open Source applications
    DBAs who need a free, starter database for training and deployment
    Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
    Educational institutions and students who need a free database for their curriculum .

    This gives people the chance to investigate, play and find out what Oracle has to offer and if they want to buld an application using an Oracle db well hey, here it is. But before anyone bites of their noses I would say have look at it investigate, then put information on the blogg. At least then when someone comes onto the blog they will find it a little more infromative.

    All the best
    Whisp

  • akie

    Nice to see you here again, Harry!
    Your new posts are very good news for me :-)

  • Anonymous

    Does it come with Oracle HTTP Server and mod_plsql to develop PSP/Web Toolkit applications?

  • redbone

    Does it come with Oracle HTTP Server and mod_plsql to develop PSP/Web Toolkit applications?

    Oracle HTTP Server is just Apache. I don’t know if it comes with mod_plsql, but installing the perl module Apache::OWA will give you the same functionality.

  • Jeewhizz

    I wrote an article on getting this working on a remote linux server, where no GUI is present. The above article is great for people using windows, but anyone planning on using this in a ‘real world’ solution would most likely not have the machine sitting in the same room, and would need to set it all up remotely.

    More info at: http://www.jellyandcustard.com/2005/11/15/running-oracle-10g-xe-php-on-linux/

    Cheers,

    Jee

  • Steve

    Any luck on the Ubuntu install of Oracle XE? I’m about to try myself and was wondering if you had any tips/gotchas to watch out for.

    Thanks, Steve

  • http://www.ubt-uni.net Fiska

    From the student perspective, I think it’s great opportunity to start and get hands dirty with 10g EX edition, but on the other hand 150MB download for just 4GB, and a lots of recrource requirements hmmm…
    This sounds more like a fishing, Oracle definately wants fish in the hook and once you’re there you can’t go back…

  • Daniel Santos

    It’s not Fishy.. it’s just like SQL Server 2005 Express Edition, with the same limits (4 GB data and 1GB RAM)

    It’s great to do simple software to manage simple things and, I suppose you can “BOX” it and sell along with your software.

  • Pingback: Jelly & Custard » Running Oracle 10g XE & PHP on Linux

  • Rafael

    Do you know about Oracle 10g XE and ASP? I am having problems with an ODBC connection(receiving an error invalid username and password)

  • waitecj

    To the guy at the top of this thread saying he can’t run an Oracle DB on his laptop:

    log into your database and:

    show parameter shared
    show parameter cache

    alter system set shared_pool_size=<a >M scope=both;
    alter system set db_cache_size=<a>M scope=both; -- Not sure whether these are both dynamic, if not scope=spfile then shutdown and startup).

    I am currently creating a website using PHPApacheOracle XELinux. Works just great for me.

    Hurrah for Oracle.

    P.S. Rafael – Which user are you connecting as through ODBC? Do you have your Oracle environment variables set? Can you connect with this user through SQLPLUS?

  • waitecj

    there was supposed to be a number before the “M” on those alter statements e.g. shared_pool_size=100M

    Also, you might want to consider reducing JAVA_POOL_SIZE if you don’t use Java, and LARGE_POOL_SIZE (but you may need to increase it again if you are planning to backup your database with RMAN).

  • Kamran

    i am not sure if it installs on windows xp home. anyone tried that? because it says only win xp pro is supported.

  • Jus

    Does anyone know how to install Java JVM on this Oracle Express Database

  • DESPERADO

    hi there i`m really new to oracle ans wanna know how to build a database and input data into it usinf the ORACLE XE . Or is Oracle XE already a database on its own??? i wanna use the SQL command line .

  • Ste

    Yeah it installs no problem on XP Home, Im running it on a laptop with 1GB ram and i don’t notice any slowdown. MySQL doesn’t even compare with Oracle, haven’t used version 5 yet but previous versions didn’t support stored procedures and query performance dropped off exponentially with every extra table used in your select. Oracle XE is free, the limitations in the license agreement refer mainly to the fact that its a beta. Also 4GB of data is a huge amount of data, databases with 100s of thousands of records can be as small as 30mb.

  • Ste

    DESPERADO, you can use the HTMLdb web interface to build the database which takes alot of the command line effort out of it. If you want to use the command line download SQL*Plus from the Oracle website

  • Danman

    Just a couple of points here from some of the posts i have seen.

    1) Please dont try and compase MySql to Oracle. MySql is good, but when you are used to enterprise level databases, MySql have some very odd and annoying ways of doing things.

    2) The Linux installation is even easier tha windows. It comes as an rpm for redhat for which there is only one prerequisite rpm being libaio (I installed mine on Fedora Core 4). Its even better than the standard Oracle install which on Linux especially can require more work than other operating systems.

    3) Some of the restrictions are as follows:

    – only one instance can be running at any one time. You can actually create multiple databases, but you can have only one operational. This isnt such an issue as Oracle is different than MS SQL Svr where one schema or user is analagous to a database. So you can have multiple schemas and therefor multiple applications phyically and logically seperated within the single Oracle XE DB.

    – No inbuilt java capabilities

    – No dataguard capabilities (ie database level replication)

    – hardware limitations to the equivelent of 1 CPU, 1 GB RAM and 4 GB disk. Mind you, you actually do a hell of a lot with Oracle even under those restictions.

    For those wanting to learn Oracle its a great start as you get the DB preinstalled. You can learn all the basics from backup and recovery to administration.

    For developers its fantastic. I installed vmware, fedora and oraclexe within 2 hours and had it all up and going. Another 20 mins and I had the oracle 9i client and toad installed on XP. With that I have managed to complete a corporate database migration and build all the scripts ready for the production run all on a 1GM RAM laptop. Now I am integrating PHP with Oracle and finding it a breeze.

    The only gotcha I have is that the autostart doesnot work under Fedora, but thats no biggie.

  • tiffercat

    Sorry Harry but the “lite” version of the Oracle Database is not Oracle XE it is Oracle Lite (funnily enough)

    Don’t confuse people bu saying it is the lite version of Oracle as they might donwload the wrong thing by accident and Oracle Lite is VERY different from Oracle XE.

  • Anonymous

    How to deinstall Oracle Express Edition ?
    Is it sufficient to delete file in the
    REGEDIT searching for ‘Oracle’ or have I got to do other operations?

  • Martin R

    If I’m reading the Oracle website correctly, it should read 1 Instance – which hopefully allows more than 1 database

    In Oracle an instance and a database are equivalent. You have only one database per instance. But in that database you can create several users, each of which can create a set of tables. So you can have several applications’ worth of database tables.

    In the SQL Server world you generally have several databases per instance (Server) but only one user in each database owns all the tables.

    So it amounts to the same thing – both databases can support multiple applications database tables on the same server.

    Hope this helps.

  • marvado

    Hi,

    I’m trying to use Oracle’s UTL_HTTP package with htp.p to show back PHP code in my browser, and it doesn’t seem to be showing back the php result.

    This is what I have in a plsql procedure:
    begin
    htp.p(‘phpinfo();’);
    end;

    it gives me a blank page when I call the package from the browser. What I expect to see is the environment variables for PHP with the phpinfo(); function call.

    my setup:
    - oracle 9i
    - Oracle’s HTTP Server with Apache 1.3
    - php 5

    anything I’m missing? any tips?
    thanks.

  • Anonymous

    When finished it fires up a browser so you can login to your local instance of HTMLDb—something roughly equivalent to phpMyAdmin.

    You may want to read the product description from Oracle.

    Specifically this

    Oracle Application Express (formerly called HTML DB) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional-looking applications that are both fast and secure.

    If you were to only use this tool for a web-based table generation tool, you would be severly under-using the tool.
    Further, if you’re creating your tables in a gui, instead of declaratively with XML, and letting a tool generate your DDL to create or alter your tables, I would call into question your level of competency as a programmer.

    Also, marvado . Why are you calling a piece of PHP code from inside of PL_SQL?! Especially the PHPInfo function? What possible use could a database have for that information!?

  • ervin

    In Oracle 10g XE I try to populate a database with DDL from an SQL script file, script.sql, which is about 8 MB in size. However, the Oracle gives me an error message saying that the script size is too big. I think it should be less than 100KB or something similar. How should I handle this problem apart from breaking the script file into a hundred or so pieces???

  • Simon

    Does Oracle XE use less Memory?

    Much less than standard oracle, it runs (almost) ok on a 512Mb PC.

    who would download a 150+ MB software with restrection applied and most importantly not really free! Personally, I’d rather use MySQL or PostgreSQL over Oracle XE

    It is free, and the restrictions of 4Gb data space and single processor will not affect most web apps.

    So what does oracle have that MS-SQL/mySQL5 doesn’t???

    Will I end up preferring it to MS-SQL/mySQL ?

    I heard it’s the database of choice from db gurus, if that’s the truth or not… i’d like to know

    Oracle is dramatically better and more sophisticated than MySQL and often necassary in large enterprise apps, but MySQL is sufficient for backing most web apps.

  • maxim

    To those who still thinks that mySQL is something comparable to Oracle.

    The two ARE NOT COMPARABLE AT ALL.

    MySQL is a File System with SQL interface (it stores data in files and reads data from files, simple as that) and Oracle is a RDBMS (Relational DataBase Management System – between selects and updates it kind of thinks, making sure not to screw data around, guarantees you the data integrity). You basically can trust Oracle ;)

    Ever seen an ATM machine? You pass your credit card, it goes to Visa or whatever, then connects to your bank account to do confirm availabiluty..ecc… Most of these systems tipically use Oracle or similar (few things as similar). Such system receives millions of connections from all over the world and manages them right. No exceptions, no faults, no crashes, no errors… It’s relational and works with human-like logic and its extremely fast.

    Oracle would grab your bank details out of million accounts just as fast as you do a simple “SELECT (*)” in mySQL. But what’s even more important than speed is that Oracle would guarantee you that while retrieving your data there was no modification pending for that record at that very time made by any other connection. And this means no concurent INSERTs or UPDATEs, DELETEs on your account so you don’t withdraw more than what you should. mySQL is unable to guarantee that at 100%.

    That is why mySQL is good for web applications and Oracle for some serious and responsible systems. With Oracle you first get database up that works on 100% and then write clients to access it. A totaly different approach. That is why they are uncomparable :)

    I’m very happy though to see Oracle releasing its lite version. This means that more web developers will get onto it, simple projects will be working better, bigger projects will have time to evaluate the product, other database, even mySQL itself will have to get better to compete for web applications…

    regards,
    maxim

  • ophir

    And this means no concurent INSERTs or UPDATEs, DELETEs on your account so you don’t withdraw more than what you should. mySQL is unable to guarantee that at 100%.

    the above statement is wrong.
    mysql can provide the same level of data integrity as oracle.

  • gabse

    Hi everybody,
    I know it says everywhere that Oracle 10g doesn’t work on XP Home Edition, but I am stubborn and want to see if it is true. I have installed it with no problem … and it seems to be working…up until I try to shutdown the database. I put in my username and password for the machine, the username and password for the database and I get an error: RemoteOperationException: ERROR: Wrong password for user. It has something to do with the allowing the user to logon as a “Batch Job”. To resolve the issue you have to go to Control Panel -> Admin tools -> Local Security Policy.
    The problem is that it the Home Edition doesn’t have Local Security Policy. Does anybody know where to find such a thing in Home Edition ? I would really appreciate your help.

  • Pingback: SitePoint Blogs » The Underground PHP and Oracle manual

  • sri

    I have created a user with DBA role and have built a Database( Tables)..now i want to grant acess to another user for these tables..is it possible. I tried creating another user. The user was created but i am unable to acess the Tables already created.Please help me overcome this

  • kurt

    I want to enable .cfm files to work with the Oracle XP download.
    I’m looking for the httpd.conf file and I can’t find it. I know XE is using some form of Apache to power it. I need to get coldfusion working on it too.

  • DHIRAJ PATRA

    Still I am awating to use it. Some people anxious about its memory hungry nature. It should be solved to some step out the MySql. Because in open source development MySql is the DB king. If any body even Oracle want to compete its region then need the effeciency and usefullness on that perticular region of user requirements otherwise Oracle will be in its own region and MySql in another. Bothe are good and stable but if one need to compete to other then, let see what happen in near future. Thanks

  • roberlamerma

    I’ve installed Oracle 10g Express Edition on a Linux box, for development and testing purposes, but still cannot make my applications work with it.

    It seems Oracle 10g EE uses a single database, and you must use one schema for each application. The thing is I haven’t been able to run any of my applications using this approach.

    Obviously Im doing something wrong. I created usernames/passwds for each application but Im not sure what to put in the dbname, because of the schema approach.

    I get a connection exception all the time.

    Thanks!

  • Selva

    I beleive Oracle 10g XE does not include JVM in it. Basically, i’m trying to create Java stored procedure. Can someone help how to add this loadjava utility to Oracle 10g.? Any help will be greatly appreciated.

    Thanks,
    Selva,
    nvselva@gmail.com