DBAL-345: When inserting decimals into Oracle, getting ORA-01722: invalid number #1532

Closed
doctrinebot opened this Issue Sep 4, 2012 · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user naitsirch:

When I insert decimals into database I get the following error message:
{quote}ORA-01722: invalid number{quote}

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 nlsdatabase_parameters where PARAMETER='NLS_NUMERICCHARACTERS'" 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:

@doctrinebot

Comment created by naitsirch:

I found out that the database session has the parameter 'NLSNUMERICCHARACTERS', too.
When I query it by the following SQL query:
sqlSELECT parameter,value FROM v$nls*parameters WHERE parameter = 'NLS_NUMERIC*CHARACTERS'

I get this result:
{quote},.{quote}

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:
{quote}
Array
(
[0] => Array
(
[PARAMETER] => NLSNUMERICCHARACTERS
[VALUE] => ,.
)

)
{quote}

And with
sqlALTER 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.

@doctrinebot

Comment created by @beberlei:

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?

@doctrinebot

Comment created by naitsirch:

Hi Benjamin.
I have opened a pull request on Githup: #197
I hope everything is correct, because this is my first Pull-Request ;-)

@doctrinebot

Comment created by @beberlei:

A related Github Pull-Request [GH-197] was closed
#197

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.3 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment