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

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

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

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

Attachments: File example.php    


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

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.

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?

Comment by Steve Müller [ 03/Jan/14 ]

Max Milaney Can you please test if this still exists in the current master branch? If so, can you please provide the information requested by Benjamin Eberlei ? Otherwise hunting this down is rather hard... Thank you!

Generated at Fri Oct 09 13:59:50 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.