when session.save,ORA-00942: table or view does not exist

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

Hi,all

hibernate+oracle9i
when I query the table:
Query query = session.createQuery("from Person p");
it works well,but if I do:
Person p=new Person();
p.setName("john");
p.setSex("m");
session.save(p);

ERROR TableGenerator:104 - could not read a hi value
java.sql.SQLException: ORA-00942: table or view does not exist

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at net.sf.hibernate.id.TableGenerator.generate(TableGenerator.java:94)
at net.sf.hibernate.id.TableHiLoGenerator.generate(TableHiLoGenerator.java:59)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:774)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:747)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1397)
at org.hibernate.sample.HiberOralce.main(HiberOralce.java:53)

ErikFK · **Posted:** Wed Sep 21, 2005 2:03 am

I'd say that your mapping doesn't match with your database.

Could you post the mapping and the create statements for Person (and any other involved sequences and such).

Erik

ErikFK · **Posted:** Wed Sep 21, 2005 2:04 am

One more thing: also post the SQL statement Hibernate generates (show_sql to true).

Erik

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

<hibernate-mapping>


<class
name="org.hibernate.sample.Person"
table="PERSONS"
>

<id
name="no"
type="java.lang.Integer"
column="NO"
>
<generator class="native">
<param name="sequence">PERSONS_SEQ</param>
</generator>
</id>

<property
name="name"
type="java.lang.String"
column="NAME"
length="10"
/>
<property
name="sex"
type="java.lang.String"
column="SEX"
length="2"
/>
<property
name="birday"
type="java.sql.Timestamp"
column="BIRDAY"
length="7"
/>



</class>
</hibernate-mapping>

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

create sequence persons_seq
start with 1
increment by 1
nominvalue
nomaxvalue
nocycle
nocache

insert into leonyuan.persons(no,name,sex,birday)
values(persons_seq.nextval,'leon','m',to_date('19790312','yyyymmdd'))

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

net.sf.hibernate.exception.SQLGrammarException: Could not save object

ErikFK · **Posted:** Wed Sep 21, 2005 2:30 am

leonyuan wrote:

insert into leonyuan.persons(no,name,sex,birday)
values(persons_seq.nextval,'leon','m',to_date('19790312','yyyymmdd'))

I suppose this is the statement Hibernate generates. Have you tried this interactively? As the root error is currently an Oracle error, this statement should not work.

Erik

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

I wrote the sql in oracle:
insert into leonyuan.persons(no,name,sex,birday)
values(persons_seq.nextval,'leon','m',to_date('19790312','yyyymmdd'))

it works well

ErikFK · **Posted:** Wed Sep 21, 2005 3:27 am

leonyuan wrote:

I wrote the sql in oracle:
insert into leonyuan.persons(no,name,sex,birday)
values(persons_seq.nextval,'leon','m',to_date('19790312','yyyymmdd'))

it works well

I'm still not sure I understand: is

Code:

insert into leonyuan.persons(no,name,sex,birday) 
values(persons_seq.nextval,'leon','m',to_date('19790312','yyyymmdd'))

what you expect or what Hibernate actually generates?

Something else: in the exception stack you can see net.sf.hibernate.id.TableHiLoGenerator which is quite strange. I wouldn't expect Hibernate to use a table Hilo generation under Oracle - which supports sequences.

Could you tell me which Hibernate version you're using and which dialect you've configured?

Erik

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

hibernate2.1

<property name="hibernate.connection.url">
jdbc:oracle:thin:@localhost:1521:orcl
</property>


<property name="hibernate.connection.driver_class">
oracle.jdbc.driver.OracleDriver
</property>

<property name="hibernate.connection.dialect">
net.sf.hibernate.dialect.Oracle9Dialect
</property>

leonyuan · **Joined:** Thu Jul 07, 2005 5:17 am **Posts:** 30

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

ora 942 is clear - tbale or view doesn't exists - it is persons_seq
- check username - you have persons_seq in another/schema (maybe)

ErikFK · **Posted:** Thu Sep 22, 2005 1:24 am

I tried to reproduce your problem, but everything was fine.

Hibernate issues the following statements in my case

Code:

select PERSONS_SEQ.nextval from dual
insert into PERSONS (NAME, NO) values (?, ?)

Note that I simplified your tables a bit - I'm so lazy ;-) - but this cannot be the issue.

My feeling is that some configuration stuff does not fit in a part of your application you didn't tell us about until now.

As snpesnpe said, the Oracle message is clear, and there is a reason to it - which has quite certainly something to do with your configuration.

Erik