Migrating Spring application from Postgres to Oracle: defining table's schema

Question

FINAL EDIT

The reason why I didn't see any logs is that I was using an old version of log4j. Hibernate hbm2ddl uses slf4j for logging and so all the logs were ignored by log4j 1. Now I upgraded to log4j2 that has a slf4j bridge and I see all the errors in the log.

Godd new is that from here I can pick the conversion errors one by one and fix them, bad news (for me) is that they are a lot!

Thanks to everyone!

FINAL EDIT END

I've a very complex java7+Spring3.2.0+hibernate3 web application that works with a Postgresql database. A client now imposed as requirement to use Oracle as a database for a project. I'm having trouble figuring out the Schema use in Oracle.

The Postgresql database is divided in about 10 different schemas, plus separated schemas for the audit records. I define tables and schemas in the annotation. I.e.:

@Entity
@Table(name = "language", schema = "live")
@Audited
@AuditTable(value="language_aud", schema="live_aud")
public class Language implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;
etc...

This works quite well in Postgres. To create the database I create manually the schemas first:

CREATE SCHEMA live;

And then start the application with hibernate.hbm2ddl.auto=create. This works like a charm and the database is correctly built.

Now moving to Oracle I have problems with the schema definition. I tried the CREATE SCHEMA statement with no succes but then found out that in Oracle schema=user (very strange to me but there must be a reason). So I created all the users to match the Schema structure. I.e.:

CREATE USER live IDENTIFIED BY 'password';

** EDIT **

But this still doesn't work, when I run the application the tables are not created by hibernate (I fixed the previous exception adding try/catch to postProcess methods, they failed as the database was not created)

I can get the connection correctly with Oracle, I use the parameter:

jdbc.driver=oracle.jdbc.driver
jdbc.url=jdbc:oracle:thin:@//localhost:1521/XE
jdbc.username=system
jdbc.password='password'
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

What I am doing wrong? do I have to create the schemas in a different way? or is the system user not to have the right privileges?

The application is shared by many clients so a requirement I have is to try to avoid to change completely the structure removing the schemas. In a perfect world the application should be database indipendant so work with any database.

Thank you for any help you can give and please ask if you need more info.

Mattia

Can you enable hibernate logging and see what sql query is generated. Also did you update the dialect in your hibernate config? — jny
– jny, Commented Dec 6, 2013 at 16:06
Thanks for the hint, the dialect was indeed wrong, but I still get the error. I'll edit with a full log to the point when the error comes. I have how sql = true — malavock
– malavock, Commented Dec 6, 2013 at 16:55
Ends up that nothing was logged as I had a old version of log4j... — malavock
– malavock, Commented Dec 27, 2013 at 15:36

tshenolo · Accepted Answer · 2013-12-10 19:59:04Z

3

In oracle id is not auto generated. You have to manage it manually or use a sequence.

Do not use

@GeneratedValue(strategy = GenerationType.AUTO)

Try this

@Id 
@SequenceGenerator(name="language_generator", sequenceName="language_sequence")
@GeneratedValue(generator="language_generator")
@Column(name = "id")
private Integer id;

Make sure you do this for all your models

** EDIT ** Try this when creating the Oracle user:

SQL> CREATE USER live IDENTIFIED BY 'password'
2  QUOTA UNLIMITED ON SYSTEM
3  QUOTA UNLIMITED ON SYSAUX;

SQL> GRANT CREATE SESSION TO live;

SQL> GRANT CREATE TABLE TO live;

SQL> GRANT SELECT_CATALOG_ROLE TO live; 

SQL> GRANT EXECUTE_CATALOG_ROLE TO live;

SQL> EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'live');


SQL> GRANT UNLIMITED TABLESPACE TO live;

SQL> GRANT RESOURCE TO live;

Lock it down when you move to production. As in take away some of the privileges like ability to drop tables etc. PS do not call your model user. Just as a precaution.

edited Dec 10, 2013 at 19:59

answered Dec 7, 2013 at 4:53

tshenolo

4632 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

malavock Over a year ago

Thanks for the tip, that was the only class without a sequence generator. I fix it but I still have the issue.

tshenolo Over a year ago

what is to the exception?

tshenolo Over a year ago

List of reserved words from oracle: docs.oracle.com/cd/B19306_01/server.102/b14200/ap_keywd.htm

Collectives™ on Stack Overflow

Migrating Spring application from Postgres to Oracle: defining table's schema

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related