CODEWITHSUNDEEP
javaspringoracle-databasehibernatepostgresql
malavock
malavock

Reputation: 371

Hibernate fails to create foreign key in Oracle between different schemas

I have a java7+Spring3.2.0+hibernate3 application that used a Postgresql database. Now I have to migrate this application to use the latest Oracle.

When at startup I set hbm2ddl to "create" the tables are created correctly, but all the foreign keys that involve tables located in different schemas fails with the error: ORA-00942: table or view does not exist

The database is quite big and involve many different schemas. I can't change the structure of the database. Here is an example of the mapping:

@Entity                                                                                     
@Table(name="brands", schema="live", uniqueConstraints=@UniqueConstraint(columnNames={"name"}))
public class Brand implements Serializable {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="brands_seq_gen")
    @SequenceGenerator(name="brands_seq_gen", sequenceName="brands_seq",schema="live",allocationSize=1)
    @Column(name = "id")
    private Integer id;

    ...
}


@Entity                                                                                     
@Table(name="campaign_group", schema="campaign")
public class CampaignGroup implements Serializable {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="campaign_group_seq_gen")
    @SequenceGenerator(name="campaign_group_seq_gen", sequenceName="campaign_group_seq",schema="campaign",allocationSize=1)
    @Column(name = "id")
    protected Long id;

    @ManyToOne(fetch = FetchType.LAZY, cascade={CascadeType.MERGE})
    @JoinColumn(name="brand_id")
    protected Brand brand;

At start up the tables are created correctly and they are in the database but then I get this error:

DEBUG org.hibernate.tool.hbm2ddl.SchemaExport [] - alter table campaign.campaign_group add constraint FKB1854E905348D509 foreign key (brand_id) references live.brands
ERROR org.hibernate.tool.hbm2ddl.SchemaExport [] - Unsuccessful: alter table campaign.campaign_group add constraint FKB1854E905348D509 foreign key (brand_id) references live.brands
ERROR org.hibernate.tool.hbm2ddl.SchemaExport [] - ORA-00942: table or view does not exist

I've created all users/schema with the SQL: CREATE USER campaign IDENTIFIED BY dsa123 QUOTA UNLIMITED ON system;

I'm using the SYSTEM user to create the database through the application and hbm2ddl.

Anyone knows why this fails? SYSTEM seems to have all provileges on the other schemas too, perhaps I'm missing something in the annotation for Oracle that Postgres does not require?

Upvotes: 2

Views: 1313

Answers (1)

user330315
user330315

Reputation:

In Oracle a schema is basically the same as a user. So the user CAMPAIGN needs to have the necessary privileges to select and reference the tables from the LIVE user.

While logged in as the LIVE user, you need to do something like this:

grant select, reference on brands to campaign;

If the CAMPAIGN user also needs to change the live table, you also need to grant update, insert and delete privilege.

You need to do that for each table the CAMPAIGN user wants to access.

Upvotes: 3

Related Questions