Uploaded image for project: 'Hibernate ORM'
  1. Hibernate ORM
  2. HHH-7232

Hibernate ignores 'schema' attribute of @SequenceGenerator for Oracle sequences

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.1.2, 4.3.6
    • Fix Version/s: None
    • Component/s: hibernate-core
    • Labels:
      None
    • Environment:
      Hibernate 4.1.2.Final, Oracle 11gR2
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      I wrote a mapping for a sequence such as:

      @SequenceGenerator( schema="jaseadm", name="applicantSignupSequence", sequenceName="seq_applicant_signup" )

      And the generated SQL is as follows:

      DEBUG org.hibernate.SQL - select seq_applicant_signup.nextval from dual

      Which leads to this:

      WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 2289, SQLState: 42000
      ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-02289: sequence does not exist

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            rmohta Rohit Mohta added a comment -

            Hi Steve,

            Are you using Spring Transaction or some other framework? If yes, please read on

            I am assuming you have multiple schemas in the same datasource. I see two workarounds depending on your use case

            (a) Can you use two EntityManager and JpaTransactionManager. Reference the transaction managers using the qualifier attribute in XML or equivalent in Javaconfig.
            Then annotate them accordingy and use them.

            (b) Make use of PrePersist annotation. Create a native SQL (get next value from sequence, just like Hibernate will do but with your schema name hardocded or picked from a property) and populate the value in the id attribute. This defeats the purpose of ORM, but might be useful in your case.

            Hope it helps

            Show
            rmohta Rohit Mohta added a comment - Hi Steve, Are you using Spring Transaction or some other framework? If yes, please read on I am assuming you have multiple schemas in the same datasource. I see two workarounds depending on your use case (a) Can you use two EntityManager and JpaTransactionManager. Reference the transaction managers using the qualifier attribute in XML or equivalent in Javaconfig. Then annotate them accordingy and use them. (b) Make use of PrePersist annotation. Create a native SQL (get next value from sequence, just like Hibernate will do but with your schema name hardocded or picked from a property) and populate the value in the id attribute. This defeats the purpose of ORM, but might be useful in your case. Hope it helps
            Hide
            johnlmorgan morgan added a comment -

            Hi,

            also exact same issue here. We are using Hibernate 4.3.6.Final, with Spring 4.1.4.RELEASE, on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, JPA 2.1 api. We have two schemas in one datasource. I have annotated the Entity correctly:

            @Table(name = "APP_CHECK", schema = "APS_OTHER_DEV")

            I also reference the same schema in @SequenceGenerator, but the sql generated by Hibernate has no schema in it. select APP_CHECK_SEQ_ID.nextval from dual.. gives ORA-00942: table or view does not exist

            I'm not in a position to introduce the use of EntityManager.

            regards
            John

            Show
            johnlmorgan morgan added a comment - Hi, also exact same issue here. We are using Hibernate 4.3.6.Final, with Spring 4.1.4.RELEASE, on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production, JPA 2.1 api. We have two schemas in one datasource. I have annotated the Entity correctly: @Table(name = "APP_CHECK", schema = "APS_OTHER_DEV") I also reference the same schema in @SequenceGenerator, but the sql generated by Hibernate has no schema in it. select APP_CHECK_SEQ_ID.nextval from dual.. gives ORA-00942: table or view does not exist I'm not in a position to introduce the use of EntityManager. regards John
            Hide
            alprab al prab added a comment -

            This is true for Postgresql sequences too.
            The schema generator ignores the schema attribute of the @SequenceGenerator annotation so that the sequence gets generated in the default schema 'public'.

            Example:
            For:

            @Id
            @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ACR_SEQ")
            @SequenceGenerator(schema = "ACR", name = "ACR_SEQ", sequenceName = "ACR_SEQ", allocationSize = 1)
            @Column(name = "ID", updatable = false, nullable = false)
            protected long id;

            The sequence "ACR_SEQ" gets created in the Postgresql default schema 'public'.

            Show
            alprab al prab added a comment - This is true for Postgresql sequences too. The schema generator ignores the schema attribute of the @SequenceGenerator annotation so that the sequence gets generated in the default schema 'public'. Example: For: @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ACR_SEQ") @SequenceGenerator(schema = "ACR", name = "ACR_SEQ", sequenceName = "ACR_SEQ", allocationSize = 1) @Column(name = "ID", updatable = false, nullable = false) protected long id; The sequence "ACR_SEQ" gets created in the Postgresql default schema 'public'.
            Hide
            steve Steve Ebersole added a comment -

            I do not planning on fixing this in the older id generators. Is anyone able to reproduce this using the new generator (preferably using 5.0)?

            Show
            steve Steve Ebersole added a comment - I do not planning on fixing this in the older id generators. Is anyone able to reproduce this using the new generator (preferably using 5.0)?
            Hide
            membersound member sound added a comment -

            I'm having the same issue in 4.3.10.Final.

            Show
            membersound member sound added a comment - I'm having the same issue in 4.3.10.Final.

              People

              • Votes:
                8 Vote for this issue
                Watchers:
                13 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Development