Hi,

we've just migrated to oracle 10g, and getting exceptions on ALL inserts of different object types (see the stack trace).

We are using the 'native' id generator in all mapping file (like this:

<id name="id" type="java.lang.Integer"
column="id_column" unsaved-value="-1">
<generator class="native"/>
</id>

then, when inserting the object (getHibernatTemplate.save()),
we get exception below;

I have these observations:
1) hibernate exectues this command "select hibernate_sequence.nextval from dual" to get the next sequence value for all object type

2) the "dual" sequence exists, with one attribute, called "DUMMY", it's value is "X"

3) we use the Oracle9Dialect.


Please, could give some hints:
a) do I need to inject an individual name of sequence into the generator, as parameter, to enforce hibernate to use distinct sequences for generation?

b) can I tell somehow to hibernate, to AUTMOATICALLY generate the seqeneces, if they doesn't exists

c) can I tell somehow to hibernate to automatically use a distinct sequence for each object (without specifiyng it in the generator as parameter), and to create the sequences if they doesn't exists?

thank you for answers, tips, notices

rob



Hibernate version: 3.0.5


Full stack trace of any exception that occurs:
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not get next sequence value; bad SQL grammar [select hibernate_sequence.nextval from dual]; nested exception is java.sql.SQLException: ORA-02289: sequence does not exists





Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):
select hibernate_sequence.nextval from dual


Debug level Hibernate log excerpt:

OK.
the problem was, that even if you use 'native' generator on Oracle, it seems, you MUST specify explicitly to hibernate, which sequence to use.

so, after updating the *.hbm.xml files like that:
<generator class="native">
<param name="sequence">S_13380_1_MYTABLE_SEQUENCE</param>
</generator>
</id>

hibernate correctly uses this seq to generate the id.

notice:
if the native generator choses different generator (for example uuid), the injected sequence parameter is simply ignored. so this modification won't affect your app. when running on different databases.

However, I'm still skeptical about this solution: why do I need to explicitly list the name of seq to use; why can't it Hibernate somehow detect?



SequenceGenerator.java
=======================

this.sequenceName = PropertiesHelper.getString(SEQUENCE, params, "hibernate_sequence");


PropertiesHelper.java:
=======================
public static String getString(String property, Properties properties, String defaultValue) {
String propValue = properties.getProperty(property);
return (propValue==null) ? defaultValue : propValue;
}

dual isn't sequence . this is helper table in oracle (from oracle 5 version) with one row.
it is for queries similar
select sysdate from dual
(select today) - oracle request 'from clause' for select and this is reson for dual table existence
you must sepcify sequence name because hibernate use different sequence for every mapping and it is good

ok. but I wonder, that guessing which sequence to use for the given table cannot be done automatically, by hibernate.