Have problems making a query to an Oracle Database, for an extrange reason it does not recognize a table colum.

Thanks...


Hibernate version:
Hibernate 2.1.6

Mapping documents:

--------------------------------------
Mapping for: Usuario.hbm.xml
------------------------------------
<hibernate-mapping>

<class name="saguapac.gis.comercial.modelo.seguridad.Usuario" table="CM_USUARIO" lazy="false">

<id column="USRID" name="usrID" type="long" >
<generator class="sequence">
<param name="sequence">USRID_SEC</param>
</generator>
</id>

<property column="USRNOMBRE" name="usrNombre" type="string" />
<property column="USRCODIGO" name="usrCodigo" type="string" />
<property column="USRLOGIN" name="usrLogin" type="string" />
<property column="USRPASSWORD" name="usrPassword" type="string" />

<set name="usrGrupos" table="CM_USUARIO_GRUPO" lazy="true">
<key column="USRID" />
<many-to-many column="GRPID" class="saguapac.gis.comercial.modelo.seguridad.Grupo" />
</set>

</class>

<query name="queryMenuAdm">
<![CDATA[
SELECT DISTINCT rec, mnu
FROM Usuario AS u, Grupo AS grp, Recurso AS rec, Menu AS mnu
WHERE
u.usrGrupos = grp AND
grp.grpRecursos = rec AND
rec.recMenu = mnu AND
u.usrLogin = :login AND
grp.grpNombre = 'Administradores'
ORDER BY mnu.mnuNombre
]]>
</query>
</hibernate-mapping>

-------------------------------------
Mappin for: Grupo.hbm.xml
-------------------------------------
<hibernate-mapping>

<class name="saguapac.gis.comercial.modelo.seguridad.Grupo" table="CM_GRUPO" lazy="false">
<id column="GRPID" name="grpID" type="long" >
<generator class="sequence">
<param name="sequence">GRPID_SEC</param>
</generator>
</id>

<property
column="GRPNOMBRE"
name="grpNombre"
not-null="true"
type="string"
/>

<set name="grpRecursos" table="CM_GRUPO_RECURSO" lazy="true">
<key column="GRPID" />
<many-to-many column="RECID" class="saguapac.gis.comercial.modelo.seguridad.Recurso" />
</set>

</class>
</hibernate-mapping>


----------------------------------------
Mapping for: Recurso.hbm.xml
----------------------------------------

<hibernate-mapping>

<class name="saguapac.gis.comercial.modelo.seguridad.Recurso" table="CM_RECURSO" lazy="false">
<id column="RECID" name="recID" type="long" >
<generator class="sequence">
<param name="sequence">RECID_SEC</param>
</generator>
</id>

<property
column="RECNOMBRE"
name="recNombre"
not-null="true"
type="string"
/>

<property
column="RECDESCRIPCION"
name="recDescripcion"
not-null="true"
type="string"
/>

<many-to-one
class="saguapac.gis.comercial.modelo.seguridad.Menu"
column="RECMNUID"
name="recMenu"
/>


</class>
</hibernate-mapping>

-------------------------------------------------
Mapping for: Menu.hbm.xml
-------------------------------------------------
<hibernate-mapping>
<class name="saguapac.gis.comercial.modelo.seguridad.Menu" table="CM_MENU" lazy="false">

<id column="MNUID" name="mnuID" type="long" >
<generator class="sequence">
<param name="sequence">MNUID_SEC</param>
</generator>
</id>
<property column="MNUNOMBRE" name="mnuNombre" not-null="true" type="string" />

</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():


List opciones = sess.getNamedQuery( "queryMenuAdm" )
.setString("login", login)
.list();
Iterator it = opciones.iterator();
while ( it.hasNext() ) {
Object[] o = (Object[]) it.next();
Recurso r = (Recurso) o[0];
Menu m = (Menu) o[1];
Map datos = new HashMap();
datos.put("menu", m.toMap() );
datos.put("recurso", r.toMap() );
lista.add( datos );
}


Full stack trace of any exception that occurs:
INFO: building session factory

22-feb-2005 9:55:30 net.sf.hibernate.impl.SessionFactoryObjectFactory addInstance

INFO: Not binding factory to JNDI, no JNDI name configured

unindexed collection before []: usuario0_.usrGrupos [
SELECT DISTINCT rec, mnu
FROM saguapac.gis.comercial.modelo.seguridad.Usuario AS u, saguapac.gis.comercial.modelo.seguridad.Grupo AS grp, saguapac.gis.comercial.modelo.seguridad.Recurso AS rec, saguapac.gis.comercial.modelo.seguridad.Menu AS mnu
WHERE
u.usrGrupos = grp AND
grp.grpRecursos = rec AND
rec.recMenu = mnu AND
u.usrLogin = :login AND
grp.grpNombre = 'Administradores'
ORDER BY mnu.mnuNombre
]

Hibernate: select distinct recurso7_.RECID as RECID0_, menu8_.MNUID as MNUID1_, recurso7_.RECNOMBRE as RECNOMBRE0_, recurso7_.RECDESCRIPCION as RECDESCR3_0_, recurso7_.RECMNUID as RECMNUID0_, menu8_.MNUNOMBRE as MNUNOMBRE1_ from CM_USUARIO usuario0_, CM_GRUPO grupo1_, CM_RECURSO recurso2_, CM_MENU menu3_ inner join CM_USUARIO_GRUPO usrgrupos4_ on usuario0_.USRID=usrgrupos4_.USRID inner join CM_GRUPO grupo5_ on usrgrupos4_.GRPID=grupo5_.GRPID inner join CM_GRUPO_RECURSO grprecurso6_ on grupo5_.GRPID=grprecurso6_.GRPID inner join CM_RECURSO recurso7_ on grprecurso6_.RECID=recurso7_.RECID inner join CM_MENU menu8_ on recurso7_.RECMNUID=menu8_.MNUID where (usuario0_.USRLOGIN=? )AND(grupo5_.GRPNOMBRE!='Administradores' ) order by menu8_.MNUNOMBRE

22-feb-2005 9:55:30 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

ADVERTENCIA: SQL Error: 904, SQLState: 42000

22-feb-2005 9:55:30 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

GRAVE: ORA-00904: "USUARIO0_"."USRID": invalid identifier


22-feb-2005 9:55:30 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

ADVERTENCIA: SQL Error: 904, SQLState: 42000

22-feb-2005 9:55:30 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

GRAVE: ORA-00904: "USUARIO0_"."USRID": invalid identifier


22-feb-2005 9:55:30 net.sf.hibernate.JDBCException <init>

GRAVE: Could not execute query

java.sql.SQLException: ORA-00904: "USUARIO0_"."USRID": invalid identifier


at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:677)

at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2371)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2660)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:387)

at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)

at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)

at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)

at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)

at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)

at net.sf.hibernate.loader.Loader.list(Loader.java:1024)

at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:853)

at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)

at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:37)

at saguapac.gis.comercial.logica.seguridad.MgrSeguridad.obtenerMenu(MgrSeguridad.java:500)

at saguapac.gis.comercial.logica.seguridad.MgrSeguridad.obtenerMenuOpc(MgrSeguridad.java:526)

at saguapac.gis.comercial.controlador.servicios.SeguridadDelegate.obtenerMenuOpc(SeguridadDelegate.java:133)

at saguapac.gis.comercial.pruebas.TestMenu.crearMenus(TestMenu.java:12)

at saguapac.gis.comercial.pruebas.TestMenu.main(TestMenu.java:20)

Could not execute query

Process exited with exit code 0.

Name and version of the database you are using:
Oracle 9i

The generated SQL (show_sql=true):

select distinct recurso7_.RECID as RECID0_, menu8_.MNUID as MNUID1_, recurso7_.RECNOMBRE as RECNOMBRE0_, recurso7_.RECDESCRIPCION as RECDESCR3_0_, recurso7_.RECMNUID as RECMNUID0_, menu8_.MNUNOMBRE as MNUNOMBRE1_ from CM_USUARIO usuario0_, CM_GRUPO grupo1_, CM_RECURSO recurso2_, CM_MENU menu3_ inner join CM_USUARIO_GRUPO usrgrupos4_ on usuario0_.USRID=usrgrupos4_.USRID inner join CM_GRUPO grupo5_ on usrgrupos4_.GRPID=grupo5_.GRPID inner join CM_GRUPO_RECURSO grprecurso6_ on grupo5_.GRPID=grprecurso6_.GRPID inner join CM_RECURSO recurso7_ on grprecurso6_.RECID=recurso7_.RECID inner join CM_MENU menu8_ on recurso7_.RECMNUID=menu8_.MNUID where (usuario0_.USRLOGIN=? )AND(grupo5_.GRPNOMBRE!='Administradores' ) order by menu8_.MNUNOMBRE


Debug level Hibernate log excerpt:
NONE, have nothing.
-------------------------------------------------

I have exactly the same issue here. The statement generated (taken from the logs) works perfectly well when executed on the sqlplus console, but throws ORA-00904 when executed via Hibernate (2.1.8). Have you or anyone else come upon a solution?

Best Regards,

Thorsten

Have you already tried to take another JDBC driver?

You might test the query using hand-written JDBC in order to see whether to look for the error at Hibernate or the JDBC driver.

Best regards
Sven

904 is error from oracle server and it is 'invalid column name' (invalid identidier in newer database)

You have bad mapping - You have any property mapped to wrong column

regards

"USRID" is a reserved name, you can´t use it as a column name.

Well, ORA-00904 says that you have tried to "execute an SQL statement that included an invalid column name or the column name is missing".

To be a valid column name the following criteria must be met:

• The column name must begin with a letter.
• The column name can not be longer than 30 characters.
• The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
• The column name can not be a reserved word.

In this case the column name might be a reserved word, BUT
- why would it work with SQL*Plus?
- I don't find a list which says that "USRID" is a reserved name (I can only find "USER*")

Why do some people think that Hibernate has some super-secret means of communicating with the database? It uses JDBC. If you cannot perform a query through JDBC, guess what? Hibernate will not magically be able to do the same.

Have you tried issuing this query through JDBC?



What happens?
Also, can you post the result of running "desc CM_USUARIO" in sqlplus?