Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Connecting to Oracle with Symfony2 and Doctrine 2. 1. Map Oracle's DATE type to Doctrine's "date" type instead of "datetime" for Oracle driver. 2. Properly configures Doctrine to use most common Oracle Date and DateTime environment configurations. You may or may not need this. Check your Oracle server configuration and see \Doctrine\DBAL\Event\L…
imports:
- { resource: services_oracle.yml }
# Pick one of the following
# If connecting via Service Name
doctrine:
dbal:
default_connection: default
connections:
default:
driver: "%database_driver%"
host: "%database_host%"
port: "%database_port%"
dbname: "%database_name%"
service: %database_service%
user: "%database_user%"
password: "%database_password%"
charset: "%database_charset%"
# OR
# If connecting via SID
doctrine:
dbal:
default_connection: default
connections:
default:
driver: "%database_driver%"
host: "%database_host%"
port: "%database_port%"
dbname: "%database_name%"
user: "%database_user%"
password: "%database_password%"
charset: "%database_charset%"
# ... other config values below
<?php
namespace Acme\PeopleSoftBundle\Listener;
use Doctrine\DBAL\Event\ConnectionEventArgs;
use Doctrine\DBAL\Events;
use Doctrine\Common\EventSubscriber;
/**
* Changes Doctrine's default Oracle-specific column type mapping to Doctrine
* mapping types. This listener modifies doctrine type mapping for
* OraclePlatform.
*
* See:
* Doctrine Field Mapping: https://doctrine-orm.readthedocs.org/en/latest/reference/basic-mapping.html#doctrine-mapping-types
* Relevant Bug Report: http://www.doctrine-project.org/jira/browse/DBAL-434
* Oracle DATE docs: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847
*/
class OracleDoctrineTypeMappingListener implements EventSubscriber
{
public function getSubscribedEvents()
{
return array(Events::postConnect);
}
/**
* Doctrine defines its primary database abstraction information in what it
* calls "Platform" classes (e.g. Doctrine\DBAL\Platforms\AbstractPlatform).
* Each database Doctrine supports implements a Platform file
* (e.g. OraclePlatform or MySqlPlatform).
*
* \Doctrine\DBAL\Platforms\OraclePlatform maps "DATE" fields to Doctrine's
* own "datetime" type, which returns it as \DateTime. The problem is that
* internally, Oracle DOES store time data as part of its "DATE" field (even
* if it's not visible in its default representation DD-MON-RR ==
* "30-JUL-13"). Thus the Doctrine core devs thought it best to map the
* database tyep "DATE" to Doctrine's "datetime" type.
*
* But if in your case you will never require time data with your DATE
* fields this will change Oracle's "DATE" fields to be mapped
* to Doctrine's "date" mapping type. This is the same behavior as almost
* every other DBAL driver (except SQLServer, which does its own crazy
* stuff).
*
* @param ConnectionEventArgs $args
* @return void
*/
public function postConnect(ConnectionEventArgs $args)
{
$args
->getConnection()
->getDatabasePlatform()
->registerDoctrineTypeMapping('date', 'date');
}
}
# Pick one of the following
# If connecting via Service Name
parameters:
database_driver: oci8
database_host: 'your.oracle.server.com'
database_port: '1521'
database_name: 'service.name.server.com'
database_service: true
database_user: 'USERXXXXX'
database_password: 'xxxxxxxxxxxxx'
database_charset: AL32UTF8
# OR
# If connecting via SID
parameters:
database_driver: oci8
database_host: 'your.oracle.server.com'
database_port: '1521'
database_name: 'YOUR_SID_HERE'
database_user: 'USERXXXXX'
database_password: 'xxxxxxxxxxxxx'
database_charset: AL32UTF8
services:
acme.doctrine.dbal.events.oracle_session_init.listener:
class: %doctrine.dbal.events.oracle_session_init.class%
tags:
- { name: doctrine.event_listener, event: postConnect }
acme.doctrine.dbal.oracle_platform.type_mapping.listener:
class: Acme\PeopleSoftBundle\Listener\OracleDoctrineTypeMappingListener
tags:
- { name: doctrine.event_listener, event: postConnect }
@trashbat

This comment has been minimized.

Copy link

@trashbat trashbat commented Dec 13, 2013

Cheers, this really helped me out! Just wanted to add that if, as in my case, you have multiple named connections only one of which is using the Oracle driver, then you'll want to add the connection name in the services.yml tags:

services:
    acme.doctrine.dbal.events.oracle_session_init.listener:
        class: %doctrine.dbal.events.oracle_session_init.class%
        tags:
            - { name: doctrine.event_listener, event: postConnect, connection: myoracleconnection }

    acme.doctrine.dbal.oracle_platform.type_mapping.listener:
        class: Acme\PeopleSoftBundle\Listener\OracleDoctrineTypeMappingListener
        tags:
            - { name: doctrine.event_listener, event: postConnect, connection: myoracleconnection }
@mrjayviper

This comment has been minimized.

Copy link

@mrjayviper mrjayviper commented Jan 20, 2014

Hello. I have 2 bundles in my src folder (aside from Acme which came by default).

Can I create a third bundle to store this listener?
e.g. /src/MyNewApp/GenericBundle/Listener/OracleListener.php

or do I need to have this listener in both bundles?
e.g. /src/MyWork/StudentBundle/Listener/OracleListener.php
/src/MyWork/StaffBundle/Listener/OraclerListener.php
Do I also need to create 2 services_oracle YML files?

Thanks very much :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment