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:
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
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):
There i found the “XE” SID for the Oracle Express Edition DB i just installed:
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(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
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.
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.
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:
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.