[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 04/Apr/13

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver


Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278



 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?





[DBAL-402] Fatal error: Uncaught exception Created: 29/Dec/12  Updated: 08/Apr/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Blocker
Reporter: Ruslan Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, oracle


 Description   
Fatal error: Uncaught exception 'Doctrine\DBAL\Driver\OCI8\OCI8Exception' with message 'ORA-00904: "T0"."ID": invalid identifier' in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28 Doctrine\DBAL\Driver\OCI8\OCI8Exception: ORA-00904: "T0"."ID": invalid identifier in /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php on line 28

Call Stack: 0.0002 665368
1. {main}() /var/www/doctrine/doctrine/tools/sandbox/index.php:0 0.3389 6023864
2. Doctrine\ORM\EntityManager->find() /var/www/doctrine/doctrine/tools/sandbox/index.php:71 0.3514 8264024
3. Doctrine\ORM\Persisters\BasicEntityPersister->load() /var/www/doctrine/doctrine/lib/Doctrine/ORM/EntityManager.php:444 0.3521 8413576 
4. Doctrine\DBAL\Connection->executeQuery() /var/www/doctrine/doctrine/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php:725 0.3532 8625920
5. Doctrine\DBAL\Driver\OCI8\OCI8Statement->execute() /var/www/doctrine/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php:635 Variables in local scope (#5): $hasZeroIndex = *uninitialized* $key = *uninitialized* $params = NULL $ret = FALSE $val = *uninitialized* 


 Comments   
Comment by Benjamin Eberlei [ 06/Jan/13 ]

Format code sample

Comment by Benjamin Eberlei [ 04/Apr/13 ]

What is wrong here? The error alone is not very helpful





[DBAL-367] Reverse engnering do not work with Oracle DB Created: 18/Oct/12  Updated: 23/Jan/13

Status: Open
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Zelenin Alexandr Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: Cli, oracle, schematool
Environment:

PHP 5.3.3-1ubuntu9.10 with Suhosin-Patch (cli) (built: Feb 11 2012 06:21:15)
oci8-1.4.7 as PHP extension builded from pecl repository with instantclient-basic-linux.x64-11.2.0.3.0.zip and instantclient-sdk-linux.x64-11.2.0.3.0.zip
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production



 Description   
$ doctrine orm:convert-mapping --filter="ms$ions" xml .

  [Doctrine\DBAL\DBALException]
  Unknown database type binary_float requested, Doctrine\DBAL\Platforms\OraclePlatform may not support it.

cli-config.php:


use Doctrine\Common\Annotations\AnnotationReader;
use Doctrine\Common\Annotations\AnnotationRegistry;

require_once 'Doctrine/Common/ClassLoader.php';

define('APPLICATION_ENV', "development");
error_reporting(E_ALL);

$classLoader = new \Doctrine\Common\ClassLoader('Doctrine');
$classLoader->register();

$config = new \Doctrine\ORM\Configuration();
$config->setProxyDir(__DIR__);
$config->setProxyNamespace('Proxies');

$config->setAutoGenerateProxyClasses((APPLICATION_ENV == "development"));

AnnotationRegistry::registerFile("Doctrine/ORM/Mapping/Driver/DoctrineAnnotations.php");
$reader = new AnnotationReader();
$driverImpl = new \Doctrine\ORM\Mapping\Driver\AnnotationDriver($reader, array(__DIR__ . "/../php/ru/niifhm/bioinformatics/biodb/model"));
$config->setMetadataDriverImpl($driverImpl);

if (APPLICATION_ENV == "development") {
    $cache = new \Doctrine\Common\Cache\ArrayCache();
} else {
    $cache = new \Doctrine\Common\Cache\ApcCache();
}

$config->setMetadataCacheImpl($cache);
$config->setQueryCacheImpl($cache);

$connectionOptions = array(
    'driver'   => 'oci8',
    'host'     => 'host.name',
    'dbname'   => 'db.name',
    'user'     => 'user.name',
    'password' => 'user.password'
);

$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);
$platform = $em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

$em->getConfiguration()->setMetadataDriverImpl(
    new \Doctrine\ORM\Mapping\Driver\DatabaseDriver(
        $em->getConnection()->getSchemaManager()
    )
);

$helperSet = new \Symfony\Component\Console\Helper\HelperSet(array(
    'db' => new \Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper($em->getConnection()),
    'em' => new \Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper($em)
));


 Comments   
Comment by Marco Pivetta [ 23/Jan/13 ]

Formatting





[DBAL-345] When inserting decimals into Oracle, getting ORA-01722: invalid number Created: 04/Sep/12  Updated: 17/Sep/12  Resolved: 17/Sep/12

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: 2.3
Security Level: All

Type: Bug Priority: Major
Reporter: Christian Stoller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: decimal, number, oracle, separator
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:



 Comments   
Comment by Christian Stoller [ 04/Sep/12 ]

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.

Comment by Benjamin Eberlei [ 05/Sep/12 ]

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?

Comment by Christian Stoller [ 13/Sep/12 ]

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

Comment by Benjamin Eberlei [ 17/Sep/12 ]

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





[DBAL-316] Incorrect parameter SERVICE_NAME Created: 07/Aug/12  Updated: 10/Feb/13  Resolved: 10/Feb/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Leandro GuimarĂ£es Fernandes Assignee: Alexander
Resolution: Invalid Votes: 0
Labels: oracle
Environment:

Windows 7, ZendServer-CE-php-5.4.0-5.6.0-Windows_x86 and Oracle 10



 Description   

At line 63, the driver PDOOracle, the parameter SERVICE_NAME is receiving the value of 'dbname' and not 'service'.

Sorry, my English is bad!



 Comments   
Comment by Alexander [ 10/Feb/13 ]

This is not a bug. Read the code. It checks if the parameter is set and if it's "true".





Generated at Wed Jun 19 23:28:01 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.