Native SQL Query -->get Column_name from all_tab_columns

Semmerl · **Posted:** Thu Nov 03, 2005 5:55 am

Hi!

I'm using Hibernate 3.1beta and an Oracle 9i database. Now I want to the the column_names of special tables.

How can I do the following SQLQuery in Hibernate?

Code:

session.createSQLQuery("select column_name from all_tab_columns where table_name='ET_EDISECTOR'")

Do I have to map the all_tab_columns?

THX!

max · **Posted:** Thu Nov 03, 2005 6:09 am

no - did you try and read the docs ?

session.createSQLQuery("select column_name from all_tab_columns where table_name='ET_EDISECTOR'").addScalar("column_name", Hibernate.INTEGER).uniqueResult();

Semmerl · **Posted:** Thu Nov 03, 2005 6:34 am

Thx for your quick reply!

The problem is, that if I do the following sqlquery:

Code:

String name =(String) session.createSQLQuery("select column_name from all_tab_columns where table_name='ET_EDISECTOR'").addScalar("all_tab_columns", Hibernate.INTEGER).uniqueResult();

I get the following stack:

Code:

Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2150)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
   at org.hibernate.loader.Loader.list(Loader.java:2021)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:109)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1575)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:165)
   at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
   at com.magnasteyr.editool.hibernate.helpers.QuerySelectHelper.ListColumnNames(QuerySelectHelper.java:1952)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
   ... 23 more
Caused by: java.sql.SQLException: Ungültiger Spaltenname
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
   at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:6218)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1557)
   at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1589)
   at com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt(NewProxyResultSet.java:2677)
   at org.hibernate.type.IntegerType.get(IntegerType.java:28)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:140)
   at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:130)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:594)
   at org.hibernate.loader.Loader.doQuery(Loader.java:689)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
   at org.hibernate.loader.Loader.doList(Loader.java:2147)
   ... 35 more

I don't know why the Rowname is not valid, if I execute the SQL Query via SQLPlus, i get the normal results...
What have I done wrong?

Thx!

max · **Posted:** Thu Nov 03, 2005 6:35 am

compare the code you wrote with what is the actual posting ;)

(p.s. I *might* have updated the posting a little bit 2 secs after posting it ;)

Semmerl · **Posted:** Thu Nov 03, 2005 6:44 am

Thx it works:

The only thing I had to change was the Hibernate type from hibernate.INTEGER to Hibernate.STRING

I don't know, what this parameter exactly does. Does it just tell, of which type the column_name is?

THX AGAIN for the quick help!

max · **Posted:** Thu Nov 03, 2005 6:49 am

that would be an extremely good guess ;)