GENERATED ALWAYS in Oracle Databases wont work with Doctrine #5136

New Issue

admin · 2026-01-22T14:59:12+01:00

admin commented

2026-01-22 14:59:12 +01:00

Originally created by @uidp5666 on GitHub (Jun 2, 2016).

Hallo,

i am currently working on an Project which uses Oracle Databases und Tables with Identifier generated in this way:

ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000 INCREMENT BY 1 PRIMARY KEY

To work with Oracle Databases in Doctrine we have to Annotate our Models with

    /**
     * @Id
     * @Column
     * @GeneratedValue
     * @SequenceGenerator(sequenceName="iseq$$_UNIQUEIDINORACLE")
     */
    private $id;

The problem is now that i cannot persist Objects with Doctrine because first he gets the unique Sequence-Number from Oracle (with the specified SequenceGenerator) and afterwards tries to insert my Object together with that new unique Identifier. Basically he tries to do something like that

INSERT INTO TABLE (id, ... VALUES ($id, ...)

But Oracle wont let you insert Rows into Tables if you specify the identifier and use the option GENERATED ALWAYS. This option prevents every insert with an identifier. If i use GENERATED BY DEFAULT Doctrine works like a charm. Is this a bug or do i use Doctrine wrong?

Originally created by @uidp5666 on GitHub (Jun 2, 2016). Hallo, i am currently working on an Project which uses Oracle Databases und Tables with Identifier generated in this way: ``` ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000 INCREMENT BY 1 PRIMARY KEY ``` To work with Oracle Databases in Doctrine we have to Annotate our Models with ``` /** * @Id * @Column * @GeneratedValue * @SequenceGenerator(sequenceName="iseq$$_UNIQUEIDINORACLE") */ private $id; ``` The problem is now that i cannot persist Objects with Doctrine because first he gets the unique Sequence-Number from Oracle (with the specified SequenceGenerator) and afterwards tries to insert my Object together with that new unique Identifier. Basically he tries to do something like that ``` INSERT INTO TABLE (id, ... VALUES ($id, ...) ``` But Oracle wont let you insert Rows into Tables if you specify the identifier and use the option GENERATED ALWAYS. This option prevents every insert with an identifier. If i use GENERATED BY DEFAULT Doctrine works like a charm. Is this a bug or do i use Doctrine wrong?

admin commented

2026-01-22 14:59:13 +01:00

@Ocramius commented on GitHub (Jun 3, 2016):

Hi Günther,

Are you generating the schema via Doctrine ORM or is the schema
custom-defined?

Marco Pivetta

http://twitter.com/Ocramius

http://ocramius.github.com/

On 2 June 2016 at 12:54, Günther Wutz notifications@github.com wrote:

Hallo,

i am currently working on an Project which uses Oracle Databases und
Tables with Identifier generated in this way:

ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000 INCREMENT BY 1 PRIMARY KEY

To work with Oracle Databases in Doctrine we have to Annotate our Models
with
/**
 * @Id
 * @Column
 * @GeneratedValue
 * @SequenceGenerator(sequenceName="iseq$$_UNIQUEIDINORACLE")
 */
private $id;
The problem is now that i cannot persist Objects with Doctrine because
first he gets the unique Sequence-Number from Oracle (with the specified
SequenceGenerator) and afterwards tries to insert my Object together with
that new unique Identifier. Basically he tries to do something like that

INSERT INTO TABLE (id, ... VALUES ($id, ...)

But Oracle wont let you insert Rows into Tables if you specify the
identifier and use the option GENERATED ALWAYS. This option prevents every
insert with an identifier. If i use GENERATED BY DEFAULT Doctrine works
like a charm. Is this a bug or do i use Doctrine wrong?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/5845, or mute the thread
https://github.com/notifications/unsubscribe/AAJakF6RsPEo7wzRp0-IRw28WS7vHzodks5qHraDgaJpZM4Isb3H
.

@Ocramius commented on GitHub (Jun 3, 2016): Hi Günther, Are you generating the schema via Doctrine ORM or is the schema custom-defined? Marco Pivetta http://twitter.com/Ocramius http://ocramius.github.com/ On 2 June 2016 at 12:54, Günther Wutz notifications@github.com wrote: > Hallo, > > i am currently working on an Project which uses Oracle Databases und > Tables with Identifier generated in this way: > > ID NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000 INCREMENT BY 1 PRIMARY KEY > > To work with Oracle Databases in Doctrine we have to Annotate our Models > with > > ``` > /** > * @Id > * @Column > * @GeneratedValue > * @SequenceGenerator(sequenceName="iseq$$_UNIQUEIDINORACLE") > */ > private $id; > ``` > > The problem is now that i cannot persist Objects with Doctrine because > first he gets the unique Sequence-Number from Oracle (with the specified > SequenceGenerator) and afterwards tries to insert my Object together with > that new unique Identifier. Basically he tries to do something like that > > INSERT INTO TABLE (id, ... VALUES ($id, ...) > > But Oracle wont let you insert Rows into Tables if you specify the > identifier and use the option GENERATED ALWAYS. This option prevents every > insert with an identifier. If i use GENERATED BY DEFAULT Doctrine works > like a charm. Is this a bug or do i use Doctrine wrong? > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > https://github.com/doctrine/doctrine2/issues/5845, or mute the thread > https://github.com/notifications/unsubscribe/AAJakF6RsPEo7wzRp0-IRw28WS7vHzodks5qHraDgaJpZM4Isb3H > .

admin commented

2026-01-22 14:59:13 +01:00

@uidp5666 commented on GitHub (Jun 7, 2016):

Hi Ocramius,

the schema is a custom-defined one.

Günther Wutz

@uidp5666 commented on GitHub (Jun 7, 2016): Hi Ocramius, the schema is a custom-defined one. Günther Wutz

admin commented

2026-01-22 14:59:17 +01:00

@cyframepaul commented on GitHub (Nov 30, 2017):

@Ocramius This is Oracles version of AUTO_INCREMENT. It was introduced in Oracle 12c.
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

It is possible that the MySQL implementation (autoincrement in column def) could be reused here without too much modification. At present Doctrine uses a sequence+trigger with Oracle which was a hack that did not work very well in my experience.

That being said, it may be worthwhile to give Oracle a decade or two before attempting to use any new Oracle feature in production. By then they should have sorted out all the bugs\limitations.

I tried to get more info from Metalink (Oracle Paid Support) but got this message whenever I searched for "Identity Column":

It's funny that the message is quick to blame the network and webpage for a search related error.. I would have assumed it had something to do with the database...

@cyframepaul commented on GitHub (Nov 30, 2017): @Ocramius This is Oracles version of AUTO_INCREMENT. It was introduced in Oracle 12c. https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1 It is possible that the MySQL implementation (autoincrement in column def) could be reused here without too much modification. At present Doctrine uses a [sequence+trigger with Oracle](https://github.com/doctrine/dbal/blob/4f7c1b7231e64e1c0b3ed39136120f9e9bb246fb/lib/Doctrine/DBAL/Platforms/OraclePlatform.php#L492) which was a hack that did not work very well in my experience. That being said, it may be worthwhile to give Oracle a decade or two before attempting to use any new Oracle feature in production. By then they should have sorted out all the bugs\limitations. I tried to get more info from Metalink (Oracle Paid Support) but got this message whenever I searched for "Identity Column": ![image](https://user-images.githubusercontent.com/11637172/33429393-ac88fcce-d59a-11e7-9eb2-8082a34a63b9.png) It's funny that the message is quick to blame the network and webpage for a search related error.. I would have assumed it had something to do with the database... ![image](https://user-images.githubusercontent.com/11637172/33429547-42815190-d59b-11e7-8db8-8d567045053f.png)

admin commented

2026-01-22 14:59:17 +01:00

@Ocramius commented on GitHub (Nov 30, 2017):

it may be worthwhile to give Oracle a decade or two before attempting to use any new Oracle feature in production.

Pretty much

@Ocramius commented on GitHub (Nov 30, 2017): > it may be worthwhile to give Oracle a decade or two before attempting to use any new Oracle feature in production. Pretty much

admin commented

2026-01-22 14:59:18 +01:00

@wbelhomsi commented on GitHub (Oct 11, 2018):

Facing same problem any updates on this?

@wbelhomsi commented on GitHub (Oct 11, 2018): Facing same problem any updates on this?

admin commented

2026-01-22 14:59:18 +01:00

@Ocramius commented on GitHub (Oct 15, 2018):

Oracle wont let you insert Rows into Tables if you specify the identifier and use the option GENERATED ALWAYS.

I must admit that this is correct behaviour by OracleDB: if you are using a generated identifier, the identity must indeed be generated. The fact that it is possible to set an identifier in an entity with a post-insert generated identifier seems more like a useful quirk of the ORM.

Anyway, this has to be fixed in doctrine/dbal. If anybody is willing to write a test case for a schema with an auto-incremental column, in which we verify the generated DDL statements, this will be easy to fix.

@Ocramius commented on GitHub (Oct 15, 2018): > Oracle wont let you insert Rows into Tables if you specify the identifier and use the option GENERATED ALWAYS. I must admit that this is correct behaviour by OracleDB: if you are using a generated identifier, the identity must indeed be generated. The fact that it is possible to set an identifier in an entity with a post-insert generated identifier seems more like a useful quirk of the ORM. Anyway, this has to be fixed in `doctrine/dbal`. If anybody is willing to write a test case for a schema with an auto-incremental column, in which we verify the generated DDL statements, this will be easy to fix.

admin commented

2026-01-22 14:59:19 +01:00

@wbelhomsi commented on GitHub (Oct 15, 2018):

is there anyway to insert with including the primary key in the insert query?

Like for example instead of
INSERT INTO DIVISIONS (DIVISIONID, DIVISIONNAME, ISDELETED) VALUES (?, ?, ?)
i can do this:
INSERT INTO DIVISIONS (DIVISIONNAME, ISDELETED) VALUES (?, ?)

@wbelhomsi commented on GitHub (Oct 15, 2018): is there anyway to insert with including the primary key in the insert query? Like for example instead of `INSERT INTO DIVISIONS (DIVISIONID, DIVISIONNAME, ISDELETED) VALUES (?, ?, ?)` i can do this: `INSERT INTO DIVISIONS (DIVISIONNAME, ISDELETED) VALUES (?, ?)`

admin commented

2026-01-22 14:59:19 +01:00

@Ocramius commented on GitHub (Oct 15, 2018):

@wbelhomsi no, the ORM will attempt an INSERT with all values read from the entity. I've described how this can be fixed, please put your focus there if this is blocking you.

@Ocramius commented on GitHub (Oct 15, 2018): @wbelhomsi no, the ORM will attempt an `INSERT` with all values read from the entity. I've described how this can be fixed, please put your focus there if this is blocking you.

admin commented

2026-01-22 14:59:19 +01:00

@wbelhomsi commented on GitHub (Oct 16, 2018):

@Ocramius excuse my stupid questions but i'm new to php and doctrine so im kind of confused.. if what i understood from you is right you mean the only fix is to auto-increment the column before sending the insert command? What if the database is already there and there are already records in it how can you start at the last number? My problem lies there.. i have a database with thousands of records and im trying to create a web service for it.. i was thinking of making my ID a GUID (30 char characters) instead of an integer and send it from my web service when an insert occurs..
if there is a solution and i did not see it/understand it please try to point me to it.. would appreciate it!

PS: my unique id column is now "GENERATED BY DEFAULT ON NULL"

Thanks for the help

@wbelhomsi commented on GitHub (Oct 16, 2018): @Ocramius excuse my stupid questions but i'm new to php and doctrine so im kind of confused.. if what i understood from you is right you mean the only fix is to auto-increment the column before sending the insert command? What if the database is already there and there are already records in it how can you start at the last number? My problem lies there.. i have a database with thousands of records and im trying to create a web service for it.. i was thinking of making my ID a GUID (30 char characters) instead of an integer and send it from my web service when an insert occurs.. if there is a solution and i did not see it/understand it please try to point me to it.. would appreciate it! PS: my unique id column is now "GENERATED BY DEFAULT ON NULL" Thanks for the help

admin commented

2026-01-22 14:59:20 +01:00

@Ocramius commented on GitHub (Oct 16, 2018):

@wbelhomsi the GENERATED ALWAYS must become GENERATED BY DEFAULT in doctrine/dbal, where this DDL snippet is generated.

What's needed is:

an OracleDB-specific test that generates the DDL for a Doctrine\DBAL\Schema\Table with an AUTO_INCREMENT column in it
alter that test to make it generate GENERATED BY DEFAULT in the DDL (test should become red)
then we can discuss implementation details to get there

@Ocramius commented on GitHub (Oct 16, 2018): @wbelhomsi the `GENERATED ALWAYS` must become `GENERATED BY DEFAULT` in [`doctrine/dbal`](https://github.com/doctrine/dbal), where this DDL snippet is generated. What's needed is: 1. an OracleDB-specific test that generates the DDL for a [`Doctrine\DBAL\Schema\Table`](https://github.com/doctrine/dbal/blob/50d3c465d026ecf38c1e477f36da5f9124bf8bd4/lib/Doctrine/DBAL/Schema/Table.php) with an `AUTO_INCREMENT` column in it 2. alter that test to make it generate `GENERATED BY DEFAULT` in the DDL (test should become red) 3. then we can discuss implementation details to get there

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5136