Doctrine DBAL

When inserting decimals into Oracle, getting ORA-01722: invalid number

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 2.2.2
  • Fix Version/s: 2.3
  • Component/s: None
  • Security Level: All
  • Environment:
    Windows 7 (German), Oracle XE 11.2.0, Doctrine 2.2.2, Symfony 2.0.15

Description

When I insert decimals into database I get the following error message:

ORA-01722: invalid number

Oracle wants decimals (e.g. NUMBER(5,2)) separated by comma instead of a dot. I think the reason is that I use the german version of Windows. I tried to modify the following method of \Doctrine\DBAL\Driver\OCI8\OCI8Statement::bindValue() just for testing of course:

    public function bindValue($param, $value, $type = null)
    {
        if (is_float($value)) {
            $value = str_replace('.', ',', (string) $value); // <--
        }
        return $this->bindParam($param, $value, $type);
    }

With this modification I do not get the error anymore.
It would be great if a solution could be found. The strange thing is, if I query "SELECT * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS'" I get ".," which means that a dot is already used as decimal separator. Maybe Oracle preferes the Locale of the OS?!
Maybe you could provide a setting which defines what separator should be used for decimals.

I found some Links that might be interesting regarding this issue:

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Christian Stoller added a comment -

I found out that the database session has the parameter 'NLS_NUMERIC_CHARACTERS', too.
When I query it by the following SQL query:

SELECT parameter,value FROM v$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS'

I get this result:

,.

With doctrine I can get the parameter with this (in Symfony2):

$conn = $this->getDoctrine()->getEntityManager()->getConnection(); /* @var $conn \Doctrine\DBAL\Connection */
$query = $conn->executeQuery("SELECT parameter,value FROM v\$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS'"); /* @var $query \Doctrine\DBAL\Driver\OCI8\OCI8Statement */
$result = $query->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

In this case I get this output:

Array
(
[0] => Array
(
[PARAMETER] => NLS_NUMERIC_CHARACTERS
[VALUE] => ,.
)

)

And with

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '

I can change the parameter.

When I execute the above query before I do my insert with decimal values, everything works as expected
But it would be nicer if I could do that with a configuration.

Show
Christian Stoller added a comment - I found out that the database session has the parameter 'NLS_NUMERIC_CHARACTERS', too. When I query it by the following SQL query: SELECT parameter,value FROM v$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS' I get this result: ,. With doctrine I can get the parameter with this (in Symfony2): $conn = $ this ->getDoctrine()->getEntityManager()->getConnection(); /* @ var $conn \Doctrine\DBAL\Connection */ $query = $conn->executeQuery( "SELECT parameter,value FROM v\$nls_parameters WHERE parameter = 'NLS_NUMERIC_CHARACTERS'" ); /* @ var $query \Doctrine\DBAL\Driver\OCI8\OCI8Statement */ $result = $query->fetchAll(\PDO::FETCH_ASSOC); print_r($result); In this case I get this output: Array ( [0] => Array ( [PARAMETER] => NLS_NUMERIC_CHARACTERS [VALUE] => ,. ) ) And with ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. ' I can change the parameter. When I execute the above query before I do my insert with decimal values, everything works as expected But it would be nicer if I could do that with a configuration.
Hide
Permalink
Benjamin Eberlei added a comment -

There is an Oci8SessionInitListener inside Doctrine DBAL. It does not yet contain the numeric character change. Can you open a pull request on Github DBAL to add this?

Show
Benjamin Eberlei added a comment - There is an Oci8SessionInitListener inside Doctrine DBAL. It does not yet contain the numeric character change. Can you open a pull request on Github DBAL to add this?
Hide
Permalink
Christian Stoller added a comment -

Hi Benjamin.
I have opened a pull request on Githup: https://github.com/doctrine/dbal/pull/197
I hope everything is correct, because this is my first Pull-Request

Show
Christian Stoller added a comment - Hi Benjamin. I have opened a pull request on Githup: https://github.com/doctrine/dbal/pull/197 I hope everything is correct, because this is my first Pull-Request
Hide
Permalink
Benjamin Eberlei added a comment -

A related Github Pull-Request [GH-197] was closed
https://github.com/doctrine/dbal/pull/197

Show
Benjamin Eberlei added a comment - A related Github Pull-Request [GH-197] was closed https://github.com/doctrine/dbal/pull/197
Benjamin Eberlei made changes -
Field Original Value New Value
Project Doctrine 2 - ORM [ 10032 ] Doctrine DBAL [ 10040 ]
Key DDC-2013 DBAL-345
Affects Version/s 2.2.2 [ 10197 ]
Affects Version/s 2.2.2 [ 10195 ]
Component/s ORM [ 10012 ]
Component/s Mapping Drivers [ 10044 ]
Benjamin Eberlei made changes -
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.3 [ 10184 ]
Resolution Fixed [ 1 ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-345, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Christian Stoller
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: