Oracle found syntax error in generated SQL

zdila · **Posted:** Wed Jun 15, 2005 4:14 am

Hi hibernate gurus

Please help me to solve this issue. Look at my mapping or suggest me a better one. I'll do one more post to the forum to explain what do I need. This post contains valid (i think) mapping which causes Hibernate 3. Please find a time to look at the generated SQL despite it is somewhat long. Thanks in advance. (it's a bit urgent form me; yeah, i know, all posts are urgent :-) )

Hibernate version: 3.0.5

Mapping documents:
only relevant:

Code:

<hibernate-mapping>
   <class name="com.kovine.kfe.dao.Widget" table="KFE_WIDGET" abstract="true">
      <id name="id" unsaved-value="null" column="WIDGET_ID">
         <generator class="sequence">
            <param name="sequence">KFE_WIDGET_ID_SEQ</param>
         </generator>
      </id>

      <one-to-one name="widgetType" class="com.kovine.kfe.dao.WidgetType" cascade="all" />
      
      <many-to-one class="com.kovine.kfe.dao.BusinessCase" name="businessCase" column="BUSINESS_CASE_ID" not-null="true" />
      <many-to-one unique="true" name="visualDefinition" class="com.kovine.kfe.dao.VisualDefinition" cascade="all,delete-orphan" column="VISUAL_DEFINITION_ID"/>

      <property name="sortOrder" column="SORT_ORDER" not-null="true" />
      <property name="labelMessageKey" column="LABEL_KEY_ID" not-null="true" />
      <property name="align" column="ALIGN" not-null="true" />
      
      <joined-subclass name="com.kovine.kfe.dao.WidgetForSingle" table="KFE_WIDGET_FOR_SINGLE">
         <key column="WIDGET_ID" />
         <property name="boxGroup" column="BOX_GROUP" not-null="false" />
      </joined-subclass>

      <joined-subclass name="com.kovine.kfe.dao.WidgetForTable" table="KFE_WIDGET_FOR_TABLE">
         <key column="WIDGET_ID" />
         <property name="width" column="WIDTH" not-null="false" />
         <property name="orderByPriority" column="ORDER_BY_PRIORITY" not-null="false" />
      </joined-subclass>
   </class>
</hibernate-mapping>
lass>
</hibernate-mapping>



<hibernate-mapping>
   <class name="com.kovine.kfe.dao.WidgetType" abstract="true">
<!--    table="KFE_WIDGET_TYPE_ABSTRACT" -->
      <id name="id" unsaved-value="null" column="WIDGET_ID">
         <generator class="foreign">
            <param name="property">widget</param>
         </generator>
      </id>
      
      <one-to-one name="widget" class="com.kovine.kfe.dao.Widget" constrained="true"/>
      
      <union-subclass name="com.kovine.kfe.dao.WidgetTypePicture" table="KFE_WIDGET_TYPE_PICTURE">
         <!-- <key column="WIDGET_ID" /> -->
         <property name="filename" column="FILENAME" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeSeparationLine" table="KFE_WIDGET_TYPE_SEPLINE">
         <!-- <key column="WIDGET_ID" /> -->
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeText" table="KFE_WIDGET_TYPE_INPUTTEXT">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <property name="width" column="WIDTH" not-null="true" />
         <property name="deletable" column="DELETABLE" not-null="true" />
         <property name="readonly" column="READONLY" not-null="true" />
         <property name="format" column="FORMAT" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeTextarea" table="KFE_WIDGET_TYPE_INPUTTEXTAREA">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <property name="width" column="WIDTH" not-null="true" />
         <property name="height" column="HEIGHT" not-null="true" />
         <property name="deletable" column="DELETABLE" not-null="true" />
         <property name="readonly" column="READONLY" not-null="true" />
         <property name="format" column="FORMAT" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeCheckbox" table="KFE_WIDGET_TYPE_CHECKBOX">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <property name="checkedValue" column="CHECKED_VALUE" not-null="true" />
         <property name="uncheckedValue" column="UNCHECKED_VALUE" not-null="true" />
         <property name="readonly" column="READONLY" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeLabel" table="KFE_WIDGET_TYPE_LABEL">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <many-to-one unique="true" name="visualDefinition" class="com.kovine.kfe.dao.VisualDefinition" cascade="all" column="VISUAL_DEFINITION_ID" not-null="true" />
         <property name="format" column="FORMAT" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeDropdown" table="KFE_WIDGET_TYPE_DROPDOWN">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <property name="strict" column="STRICT" not-null="true" />
         <property name="deletable" column="DELETABLE" not-null="true" />
         <property name="readonly" column="READONLY" not-null="true" />
         <property name="format" column="FORMAT" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeDropdownEx" table="KFE_WIDGET_TYPE_DROPDOWN_EX">
         <!-- <key column="WIDGET_ID" /> -->
         <many-to-one unique="true" name="tableColumn" class="com.kovine.kfe.dao.TableColumn" cascade="all" column="TABLE_COLUMN_ID" not-null="true" />
         <property name="lookupTable" column="LOOKUP_TABLE" not-null="true" />
         <property name="lookupTableKey" column="LOOKUP_TABLE_KEY" not-null="false" />
         <property name="lookupTableValue" column="LOOKUP_TABLE_VALUE" not-null="true" />
         <property name="strict" column="STRICT" not-null="true" />
         <property name="deletable" column="DELETABLE" not-null="true" />
         <property name="readonly" column="READONLY" not-null="true" />
         <property name="format" column="FORMAT" not-null="true" />
      </union-subclass>

      <union-subclass name="com.kovine.kfe.dao.WidgetTypeSpacer" table="KFE_WIDGET_TYPE_SPACER">
         <!-- <key column="WIDGET_ID" /> -->
         <property name="width" column="WIDTH" not-null="false" />
         <property name="height" column="HEIGHT" not-null="false" />
      </union-subclass>
   </class>
</hibernate-mapping>

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

Code:

Session session = HibernateUtil.currentSession();
BusinessCase businessCase = (BusinessCase) session.load(BusinessCase.class, new Integer(123));
return businessCase.getWidgets().isEmpty(); // causes Exception

Full stack trace of any exception that occurs:

Code:

java.sql.SQLException: ORA-00928: chýba kúové slovo SELECT

   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 org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
   at org.hibernate.loader.Loader.doQuery(Loader.java:391)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
   at org.hibernate.loader.collection.OneToManyLoader.initialize(OneToManyLoader.java:111)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:176)
   at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:48)
   at org.hibernate.collection.PersistentBag.isEmpty(PersistentBag.java:226)
   at com.kovine.kfe.beans.BusinessCaseForm.getHasWidgets(BusinessCaseForm.java:332)
inessCaseForm.java:332)

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

The generated SQL (show_sql=true):
now hold on your breath, get ready, go ...

Code:

select widgets0_.BUSINESS_CASE_ID as BUSINESS2_2_, widgets0_.WIDGET_ID as WIDGET1_2_, widgets0_.WIDGET_ID as WIDGET1_1_, widgets0_.BUSINESS_CASE_ID as BUSINESS2_14_1_, widgets0_.VISUAL_DEFINITION_ID as VISUAL3_14_1_, widgets0_.SORT_ORDER as SORT4_14_1_, widgets0_.LABEL_KEY_ID as LABEL5_14_1_, widgets0_.ALIGN as ALIGN14_1_, widgets0_1_.BOX_GROUP as BOX2_15_1_, widgets0_2_.WIDTH as WIDTH16_1_, widgets0_2_.ORDER_BY_PRIORITY as ORDER3_16_1_, case when widgets0_1_.WIDGET_ID is not null then 1 when widgets0_2_.WIDGET_ID is not null then 2 when widgets0_.WIDGET_ID is not null then 0 end as clazz_1_, widgettype1_.WIDGET_ID as WIDGET1_0_, widgettype1_.FILENAME as FILENAME18_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_20_0_, widgettype1_.WIDTH as WIDTH20_0_, widgettype1_.DELETABLE as DELETABLE20_0_, widgettype1_.READONLY as READONLY20_0_, widgettype1_.FORMAT as FORMAT20_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_21_0_, widgettype1_.WIDTH as WIDTH21_0_, widgettype1_.HEIGHT as HEIGHT21_0_, widgettype1_.DELETABLE as DELETABLE21_0_, widgettype1_.READONLY as READONLY21_0_, widgettype1_.FORMAT as FORMAT21_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_22_0_, widgettype1_.CHECKED_VALUE as CHECKED2_22_0_, widgettype1_.UNCHECKED_VALUE as UNCHECKED3_22_0_, widgettype1_.READONLY as READONLY22_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_23_0_, widgettype1_.VISUAL_DEFINITION_ID as VISUAL2_23_0_, widgettype1_.FORMAT as FORMAT23_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_24_0_, widgettype1_.STRICT as STRICT24_0_, widgettype1_.DELETABLE as DELETABLE24_0_, widgettype1_.READONLY as READONLY24_0_, widgettype1_.FORMAT as FORMAT24_0_, widgettype1_.TABLE_COLUMN_ID as TABLE1_25_0_, widgettype1_.LOOKUP_TABLE as LOOKUP2_25_0_, widgettype1_.LOOKUP_TABLE_KEY as LOOKUP3_25_0_, widgettype1_.LOOKUP_TABLE_VALUE as LOOKUP4_25_0_, widgettype1_.STRICT as STRICT25_0_, widgettype1_.DELETABLE as DELETABLE25_0_, widgettype1_.READONLY as READONLY25_0_, widgettype1_.FORMAT as FORMAT25_0_, widgettype1_.WIDTH as WIDTH26_0_, widgettype1_.HEIGHT as HEIGHT26_0_, widgettype1_.clazz_ as clazz_0_ from KFE_WIDGET widgets0_ left outer join KFE_WIDGET_FOR_SINGLE widgets0_1_ on widgets0_.WIDGET_ID=widgets0_1_.WIDGET_ID left outer join KFE_WIDGET_FOR_TABLE widgets0_2_ on widgets0_.WIDGET_ID=widgets0_2_.WIDGET_ID left outer join ( select null as UNCHECKED_VALUE, null as TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, null as READONLY, null as WIDTH, null as CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, FILENAME, null as DELETABLE, WIDGET_ID, null as STRICT, null as FORMAT, 1 as clazz_ from KFE_WIDGET_TYPE_PICTURE union all select null as UNCHECKED_VALUE, null as TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, null as READONLY, null as WIDTH, null as CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, null as DELETABLE, WIDGET_ID, null as STRICT, null as FORMAT, 2 as clazz_ from KFE_WIDGET_TYPE_SEPLINE union all select null as UNCHECKED_VALUE, TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, READONLY, WIDTH, null as CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, DELETABLE, WIDGET_ID, null as STRICT, FORMAT, 3 as clazz_ from KFE_WIDGET_TYPE_INPUTTEXT union all select null as UNCHECKED_VALUE, TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, READONLY, WIDTH, null as CHECKED_VALUE, HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, DELETABLE, WIDGET_ID, null as STRICT, FORMAT, 4 as clazz_ from KFE_WIDGET_TYPE_INPUTTEXTAREA union all select UNCHECKED_VALUE, TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, READONLY, null as WIDTH, CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, null as DELETABLE, WIDGET_ID, null as STRICT, null as FORMAT, 5 as clazz_ from KFE_WIDGET_TYPE_CHECKBOX union all select null as UNCHECKED_VALUE, TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, null as READONLY, null as WIDTH, null as CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, VISUAL_DEFINITION_ID, null as FILENAME, null as DELETABLE, WIDGET_ID, null as STRICT, FORMAT, 6 as clazz_ from KFE_WIDGET_TYPE_LABEL union all select null as UNCHECKED_VALUE, TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, READONLY, null as WIDTH, null as CHECKED_VALUE, null as HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, DELETABLE, WIDGET_ID, STRICT, FORMAT, 7 as clazz_ from KFE_WIDGET_TYPE_DROPDOWN union all select null as UNCHECKED_VALUE, TABLE_COLUMN_ID, LOOKUP_TABLE_KEY, READONLY, null as WIDTH, null as CHECKED_VALUE, null as HEIGHT, LOOKUP_TABLE_VALUE, LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, DELETABLE, WIDGET_ID, STRICT, FORMAT, 8 as clazz_ from KFE_WIDGET_TYPE_DROPDOWN_EX union all select null as UNCHECKED_VALUE, null as TABLE_COLUMN_ID, null as LOOKUP_TABLE_KEY, null as READONLY, WIDTH, null as CHECKED_VALUE, HEIGHT, null as LOOKUP_TABLE_VALUE, null as LOOKUP_TABLE, null as VISUAL_DEFINITION_ID, null as FILENAME, null as DELETABLE, WIDGET_ID, null as STRICT, null as FORMAT, 9 as clazz_ from KFE_WIDGET_TYPE_SPACER ) widgettype1_ on widgets0_.WIDGET_ID=widgettype1_.WIDGET_ID where widgets0_.BUSINESS_CASE_ID=? order by widgets0_.SORT_ORDER

Debug level Hibernate log excerpt: