hi,

i wanted to get a little bit into hiberante, unfortunatly i've got the following exception:

Hibernate: select person0_.UID as UID, person0_.VORNAME as VORNAME, person0_.NACHNAME as NACHNAME, person0_.ALTER as ALTER from PERSONS person0_
SQL Error: 1747, SQLState: 42000
ORA-01747: invalid user.table.column, table.column, or column specification

SQL Error: 1747, SQLState: 42000
ORA-01747: invalid user.table.column, table.column, or column specification

Could not execute query
java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

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.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1536)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1505)
at com.pironet.different.hibernate.Manager.getPersons(Manager.java:105)
at com.pironet.different.hibernate.Manager.list(Manager.java:81)
at com.pironet.different.hibernate.Manager.<init>(Manager.java:50)
at com.pironet.different.hibernate.Manager.main(Manager.java:30)
Exception in thread "main" net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1539)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1505)
at com.pironet.different.hibernate.Manager.getPersons(Manager.java:105)
at com.pironet.different.hibernate.Manager.list(Manager.java:81)
at com.pironet.different.hibernate.Manager.<init>(Manager.java:50)
at com.pironet.different.hibernate.Manager.main(Manager.java:30)
Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

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.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:834)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1536)
... 6 more

i just have a bean called Person with getters and setters for:
String vorname, nachname;
int alter, id;

mapping document for the class:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.pironet.different.hibernate.Person" table="PERSONS">
<id name="id" type="int">
<column name="UID" not-null="true"/>
<generator class="sequence"/>
</id>

<property name="vorname" type="string">
<column name="VORNAME"/>
</property>

<property name="nachname" type="string">
<column name="NACHNAME"/>
</property>

<property name="alter" type="int">
<column name="ALTER"/>
</property>
</class>
</hibernate-mapping>

database: oracle 9i
hibernate version: 2.1.2

script to create table:
connect test/test@ORCL;

drop table test.PERSONS;
create table test.PERSONS("uid" number(5) not null, "vorname" varchar(255), "nachname" varchar(255), "alter" number(2));
commit;
disconnect;
exit;

code i used:

List persons = new ArrayList();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
persons = session.find("from Person");
tx.commit();
session.close();

things i noticed:
doing this in sqlplus:
select person0_.UID as UID, person0_.VORNAME as VORNAME, person0_.NACHNAME as NACHNAME, person0_.ALTER as ALTER from PERSONS person0_

gives the same ora-error as hibernate
doing this in sqlplus:
select "person0_"."UID" as "UID", "person0_"."VORNAME" as "VORNAME", "person0_"."NACHNAME" as "NACHNAME", "person0_"."ALTER" as "ALTER" from PERSONS "person0_"

works fine

here my hibernate config:
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:orcl</property>
<property name="dialect">net.sf.hibernate.dialect.Oracle9Dialect</property>
<property name="hibernate.connection.username">test</property>
<property name="hibernate.connection.password">test</property>
<property name="show_sql">true</property>
<property name="use_outer_join">true</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.cache.provider_class">net.sf.hibernate.cache.HashtableCacheProvider</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<mapping resource="com/pironet/different/hibernate/Person.hbm.xml"/>
</session-factory>

</hibernate-configuration>

any clue why the query is not as oracle wants it, even when using the oracle9dialect in hibernate cfg?

Most likely a keyword in the generated SQL, I suspect "ALTER" is not allowed. Change your mapping to use another column name.

so, removed the alter column from table and mapping doc
but still got the same error, just with 1 less col in the query :D

Probably start by looking at the generated SQL and the Oracle Help to find out what this error really means?

By the way...

You should define the sequence name like this:

These eaisiest way to figure out the error is copy and paste the SQL into SQL Plus.

So were you able to resolve this problem?

yes, the column name such as "index","date", will have problem in oracle using hibernate.

i googled and found solution:
change

UPDATE suppliers
SET number = 10000;
to
UPDATE suppliers
SET "number" = 10000;

so i think hibernate should do this for user.

So you think Hibernate should know *all* keywords, of *all* supported databases??

I don't think so.

*You* should quote identifiers in the mapping document,

I've had to change sample code because "UID" is an Oracle keyword.

*I* think Hibernate can put *all* columnames between double quotes and thusly solve these kinda problems for the user.

Why does *Gavin* think so limited, inside a box of *only* keywords-matching columnnames?