Doctrine and Oracle on Windows

For one of our clients we are developing a touchscreen application with a symfony backend (Flash + AMFPHP amongst others).

One of the technical requirements is that it has to work with an Oracle database. Since we don’t have any experience whatsoever with Oracle, this was a tough cookie to crumble. This post is meant to be a quick start for other developers who have to use symfony/Oracle in a Windows environment.
Below are the most important things i learned and figured out on my “journey” towards a working Windows/Oracle/symfony setup.

1. Install the free Oracle Express Edition database

I downloaded the OracleXEUniv.exe Univeral edition from this page.
After installation you can login at this (local) url: http://127.0.0.1:8080/apex

Or use a pre-installed database, and get the right connection parameters from the DBA.

2. Create a user in the database with the right permissions

I Just gave the new user all permissions so the symfony doctrine:build -all command will be able to create triggers, sequences etc. Of course you have to ask (or beg) your Oracle DBA to give you these permissions so you can still use your symfony cli commands

3. Download the Oracle Instant Client from the Oracle website

You can find all sorts of downloads here (you need to create an account to download) Important thing is that you have to add the installation path to your windows PATH environment variable.

This is necessary so the Oracle PHP extension can locate the right libraries to work with (which are included in the Instant Client software).

4. Use the free SQL Developer program or a similar db management program

I used SQL Developer to manage / inspect the Oracle database. There will be other programs i don’t (yet) know about. This worked for me.

5. Configure the database connection

I had some trouble connecting to the database, since with Oracle you can connect in a few ways.

There is a file called tnsnames.ora where you can define all connection settings under a short SID (service id). You can then connect to the database, referring to this shortcut SID. The client software (i suppose) reads the tnsnames.ora file an does the rest. Because the express edition always has the same SID (which is XE) it will be simple to connect.

The location of this file is (in my case):
<Oracle-XE-install-dir>\xe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora

There i found the “XE” SID for the Oracle Express Edition DB i just installed:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

You probably will get this SID from your DBA.

Don’t use the (experimental) Oracle PDO driver (crashes, crashes…)

Of course it states on the PHP Oracle PDO manual page that it’s highly experimental.  But somehow (stubbornness?) i kept trying this, and believing that i did something wrong myself, or that my configuration was faulty etc. etc. Until i stumbled upon this post . Many thanks for that. This put me on the right track finally.

So,  use the latest  oci8 driver from the windows PECL build page at  http://downloads.php.net/pierre

When creating the connection, Doctrine will first check if the PDO extension is enabled. If it is enabled (most servers will have this enabled is suppose?) it will check if a PDO driver is available. So if you use oci: in your DSN it will use the experimental PDO OCI extension (don’t, don’t!) If PDO is not loaded or if no matching PDO driver is found, it will try to create an instance of Doctrine_Adapter_<DB_TYPE>. In our case this is just fine, because we want a Doctrine_Adapter_Oracle to connect to our database. SO that’s why you have to use oracle: als the dbtype in your DNS.

For more information and details, check the post i mentined above

Just to be clear, here’s the combination of enabled / disabled extensions in my php.ini:

...
extension=php_mysql.dll
extension=php_oci8.dll
extension=php_pdo.dll
extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_oci8.dll
...

So use the oracle: db type in your databases.yml. This is the only db type setting that will use the right oci extension and not the PDO one. Here’s the corresponding databases.yml setup:

doctrine:
 class: sfDoctrineDatabase
 param:
   dsn: oracle:dbname=//127.0.0.1/XE;charset=AL32UTF8
   username: yourdbuser
   password: yourdbpass
   attributes:
     quote_identifier: true

Other things

Date formats / decimals.

I had some trouble with decimals, because the default setting , (comma) and not a . (dot) as the decimal separator.
Maybe this can be done in an easier way, but i created a trigger to achieve this. This trigger will be executed after every logon. I also added the preferred way for dates, but i believe symfony already tries to set this before every query.

  CREATE OR REPLACE TRIGGER "DRIEBIT_CUSTOM_FORMATS"
AFTER LOGON ON DATABASE
BEGIN
  execute immediate 'alter session set nls_numeric_characters = ''. '' ' ;
  execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS'' ' ;
END;
/
ALTER TRIGGER "DRIEBIT_CUSTOM_FORMATS" ENABLE;

N.B. In the live environment this wasn’t necessary because it had already been done, but out of the box i had to do this to be able to succesfully save decimal values.

Column types

Because our project didn’t need to store large amounts of textual data, i could get away with using the standard string column type (which gets translated to the VARCHAR2 type in Oracle).

But there are other column types like CLOB (Character Large Object) which can store up to 8 Tb (or even more)  I don’t know how well this works with Doctrine though.

Purge Recyclebin…

In the SQL developer tool i noticed (after running the doctrine:build commands a few times) a lot of “old” database objects / triggers / sequences.
After some reseacrh i figured out that these could be purged with the query: PURGE RECYCLEBIN;
I don’t know if this is Oracle specific behavior or just a feature of the SQL Developer tool.

I will  try to update this post with new insights, corrections etc. as my insight in the combination Symfony/Oracle/Windows evolves.

Please let me know if you have any additions, clarifications, comments or corrections to this post.