OracleSessionInit is NOT loaded properly in the Dependency Injection

[iftah]

Hi,
I moved my project from MySQL to Oracle, and I get this error when I try to insert into the database.

An exception occurred while executing 'INSERT INTO arborescence (IDNT_ARBR, NOM_ARBR, DATE_CREATION, IDNT_SERV, IDNT_UTLS) VALUES (?, ?, ?, ?, ?)' with params [7, "B", "2013-10-09 00:00:00", 7, 1]:
ORA-01861: literal does not match format string

I solved this problem by adding the following code to : app/config/config.yml

services:
oracle.listener:
class: Doctrine\DBAL\Event\Listeners\OracleSessionInit
tags:
- { name: doctrine.event_listener, event: postConnect }

This seems to be a bug in the DoctrineBundle. If we use the MySQL driver, the corresponding MysqlSessionInit is loaded properly in the Dependency Injection extension. But this doesn't happen with the OracleSessionInit class, if you we the Oracle driver.

Respectfully,
iftah

[stof]

@beberlei is the OracleSessionInit listener always needed when using Oracle or only in some cases ?

[iftah]

I only get an error when I'm trying to insert a DateTime field type, so I'm not sure if it is always needed when using Oracle.

[stof]

@iftah If it is required as soon as you have a date field in the project, I would still consider it as required as this is a common case. My question was more about being always required or only for some versions of oracle

[iftah]

I'm only using oracle 11g, I can't tell about the other versions.

[elemele]

Hi
I have the same problem with Oracle 10g / zend framework / doctrine2
ORA-01861: literal does not match format string

I have try to setup OracleSessionInit
'eventmanager' => array(
'orm_oracle' => array(
'subscribers' => array('Doctrine\DBAL\Event\Listeners\OracleSessionInit'),
),
),
credits to: http://raymondkolbe.com/2012/06/19/doctrineormmodule-and-oraclesessioninit/

but it does not help.

My Entity

/** @Orm\Column(type="string") */
protected $ColumnDate;

public function __construct() {
$this->setColumnDate(date("Y-m-d H:i:s"));
}

OR

/** @Orm\Column(type="datetime") */
protected $ColumnDate;

public function __construct() {
$this->setColumnDate(date_create(date("Y-m-d H:i:s")));
}

But always is the same error:
ORA-01861: literal does not match format string

Any ideas what is wrong ?

Thanks
P

[deeky666]

@elemele What is the column declaration in your database (not mapping)? I guess the string format to be inserted depends on the TIMESTAMP or DATE declaration. Maybe you have something wrong there so that it does not actually match your mapping definition? Are you sure your OracleSessionInit is actually initialized and invoked? Can you provide a stacktrace for further investigation?

[elemele]

@deeky666 In database I have type DATE.
I think You have right... OracleSessionInit is not initialized.

I have made simple test in my action and it helped:

public function detailsAction()
{
$em = $this->getEntityManager('orm_oracle');
$dbh = $em->getConnection();
$sth = $dbh->prepare("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
$sth->execute();
}

if I do this in the configuration file I will let you know.

Thanks
P

[xocasdashdash]

The fixed proposed by @iftah works for me too, but I don't really know why do I have to initialize this. ¿Is this considered a bug?

[yLark]

@iftah fix works for me too (Oracle 11g).
Is this fix documented somewhere in Doctrine online help?
I added the following lines to the services.yml file in my main bundle.

services:
    oracle.listener:
        class: Doctrine\DBAL\Event\Listeners\OracleSessionInit
        tags:
            - { name: doctrine.event_listener, event: postConnect }

The OracleSessionInit class description is explicit about this issue:

Should be used when Oracle Server default environment does not match the Doctrine requirements.
The following environment variables are required for the Doctrine default date format:

NLS_TIME_FORMAT="HH24:MI:SS"
NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
NLS_TIMESTAMP_FORMAT="YYYY-MM-DD HH24:MI:SS"
NLS_TIMESTAMP_TZ_FORMAT="YYYY-MM-DD HH24:MI:SS TZH:TZM"

@DoctrineCoreDevelopers: Since this configuration is required, why not making it default in Doctrine?

[Ocramius]

This is documented in http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/events.html

We don't want to make it the default, since this is affects only oracle sessions. Additionally, changing defaults is a major BC break in this case.

[jljunior3]

Can anyone help me where I set this in laravel?

services:
oracle.listener:
class: Doctrine\DBAL\Event\Listeners\OracleSessionInit
tags:
- { name: doctrine.event_listener, event: postConnect }

[stof]

well, I don't know how the DBAL integration into Laravel is configured, but I know for sure that this is the wrong place to ask. DoctrineBundle is only about integrating Doctrine with Symfony 2. you need to contact the maintainers of the laravel integration

[yezongyang]

I am using oracle11g, I have fixed the problem with @iftah config code when using query builder. But why?

[manfredjb]

@jljunior3, maybe is too late. But I have solved this in laravel at the moment when you create the connection passing the Event Manager as third parameter:

$eventManager = new \Doctrine\ORM\EntityManager();
$eventManager->addEventSubscriber(new \Doctrine\DBAL\Event\Listeners\OracleSessionInit());

$em = EntityManager::create($dbParams, $config, $eventManager);

[yaziderman]

For those who may face the same problem with Zend2, and Basing on the answer of @deeky666 I have get it fixed by replacing the line

return EntityManager::create($connection, $config);

in the file: EntityManageFactory.php, under the Function createService.php
with the following lines:

$dbh = $em->getConnection();
$sth = $dbh->prepare("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
$sth->execute();
return $em;

[Ocramius]

in the file: EntityManageFactory.php, under the Function createService.php

@yaziderman please don't monkey-patch libraries. Also, absolutely DO NOT suggest others to do that.

What you want in your case is using a delegator factory (docs here) that acts on the doctrine.configuration.orm_default service, adding a listener to it.

</rant>

[karousn]

i have the same problem, and by added oracle listener haven't any effect on problem when we use more then database other oracle in the same time.
So @Ocramius : are they any possibility/PR/solutions to inject the listener on ORM configuration level to decoupling using the same listener by other declared ORM ?

[kimhemsoe]

@karousn Did you specify connection name with the tag?

[karousn]

i forget to specify the connection, thank's @kimhemsoe it is work now