[rules-users] Error When changing persistence from H2 to Oracle forTaskService

Thu Apr 8 12:36:21 EDT 2010

It is possible that the default mapping is not accepted by Oracle because it 
uses too long table names or keywords.  Iùve recently submitted a fix for 
similar issues with drools-persistence-jpa (basically reduced column names 
manually).

Could you maybe assist me in doing the same for the task service?  You can 
basically change the table or column name using a @table or @column 
(name="...") annotation.  If you could pinpoint the table/column names that 
are causing problems, I'm more than happy to apply these changes.

Kris

----- Original Message ----- 
From: "ramram" <ramram858 at gmail.com>
To: <rules-users at lists.jboss.org>
Sent: Tuesday, March 30, 2010 11:15 AM
Subject: [rules-users] Error When changing persistence from H2 to Oracle 
forTaskService

>
> Hi All,
>
>   I am trying to change the persistence of the HumanTaskService from the 
> in
> memory database to Oracle and I am having the below errors if some one can
> help. I have posted my persistence,xml and the Error that I am having.
>
>
> Persistence.xml
> ---------------
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <persistence version="1.0"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
> http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd
> http://java.sun.com/xml/ns/persistence/orm
> http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
> xmlns:orm="http://java.sun.com/xml/ns/persistence/orm"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns="http://java.sun.com/xml/ns/persistence">
>    <persistence-unit name="org.drools.task">
>        <provider>org.hibernate.ejb.HibernatePersistence</provider>
>        <class>org.drools.task.Attachment</class>
>        <class>org.drools.task.Content</class>
>        <class>org.drools.task.BooleanExpression</class>
>        <class>org.drools.task.Comment</class>
>        <class>org.drools.task.Deadline</class>
>        <class>org.drools.task.Delegation</class>
>        <class>org.drools.task.Escalation</class>
>        <class>org.drools.task.Group</class>
>        <class>org.drools.task.I18NText</class>
>        <class>org.drools.task.Notification</class>
>        <class>org.drools.task.EmailNotification</class>
>        <class>org.drools.task.EmailNotificationHeader</class>
>        <class>org.drools.task.PeopleAssignments</class>
>        <class>org.drools.task.Reassignment</class>
>        <class>org.drools.task.Status</class>
>        <class>org.drools.task.Task</class>
>        <class>org.drools.task.TaskData</class>
>        <class>org.drools.task.SubTasksStrategy</class>
>        <class>org.drools.task.OnParentAbortAllSubTasksEndStrategy</class>
>        <class>org.drools.task.OnAllSubTasksEndParentEndStrategy</class>
>        <class>org.drools.task.User</class>
>
>     <properties>
>         <property name="hibernate.dialect"
> value="org.hibernate.dialect.OracleDialect"/>
>         <property name="hibernate.connection.driver_class"
> value="oracle.jdbc.OracleDriver"/>
>         <property name="hibernate.connection.url" value="..... my databse
> url is placed here..." />
>         <property name="hibernate.connection.username" value="drools"/>
>         <property name="hibernate.connection.password" value="drools"/>
>
>
>         <property name="hibernate.connection.autocommit" value="false" />
>         <property name="hibernate.max_fetch_depth" value="3"/>
> <property name="hibernate.hbm2ddl.auto" value="create-drop" />
>                <property name="hibernate.show_sql" value="true" />
>     </properties>
>    </persistence-unit>
>
> </persistence>
>
>
>
>
>
> ERROR:
> --------
>
> SEVERE: Unsuccessful: create table Attachment (id number(19,0) not null,
> name varchar2(255), accessType number(10,0), contentType varchar2(255),
> attachedAt date, si
> number(10,0) not null, attachmentContentId number(19,0) not null,
> attachedBy_id varchar2(255), TaskData_Attachments_Id number(19,0), primary
> key (id))
> Mar 30, 2010 12:10:37 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00904: : invalid identifier
>
> Mar 30, 2010 12:10:37 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table Comment (id number(19,0) not null, text
> clob, addedAt date, addedBy_id varchar2(255), TaskData_Comments_Id
> number(19,0), primary
> (id))
> Mar 30, 2010 12:10:37 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00903: invalid table name
>
> Mar 30, 2010 12:10:38 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table Deadline (id number(19,0) not null, 
> date
> date, escalated number(1,0) not null, Deadlines_EndDeadLine_Id 
> number(19,0),
> Deadlines_S
> tDeadLine_Id number(19,0), primary key (id))
> Mar 30, 2010 12:10:38 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00904: : invalid identifier
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table Notification_BusinessAdministrators
> (task_id number(19,0) not null, entity_id varchar2(255) not null)
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table Notification_EmailNotificationHeader
> (Notification_id number(19,0) not null, emailHeaders_id number(19,0) not
> null, mapkey varcha
> 255), primary key (Notification_id, mapkey), unique (emailHeaders_id))
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table 
> PeopleAssignments_BusinessAdministrators
> (task_id number(19,0) not null, entity_id varchar2(255) not null)
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table PeopleAssignments_ExcludedOwners 
> (task_id
> number(19,0) not null, entity_id varchar2(255) not null)
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table PeopleAssignments_PotentialOwners
> (task_id number(19,0) not null, entity_id varchar2(255) not null)
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: create table PeopleAssignments_TaskStakeholders
> (task_id number(19,0) not null, entity_id varchar2(255) not null)
> Mar 30, 2010 12:10:39 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Attachment add constraint
> FK1C935432077E1A4 foreign key (attachedBy_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Attachment add constraint
> FK1C93543839A1819 foreign key (TaskData_Attachments_Id) references Task
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Comment add constraint 
> FK9BDE863F44E05A35
> foreign key (TaskData_Comments_Id) references Task
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00903: invalid table name
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Comment add constraint 
> FK9BDE863FC17237C8
> foreign key (addedBy_id) references OrganizationalEntity
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00903: invalid table name
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Deadline add constraint 
> FK21DF3E78F9CD9DE3
> foreign key (Deadlines_StartDeadLine_Id) references Task
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Deadline add constraint 
> FK21DF3E78B92DDCCA
> foreign key (Deadlines_EndDeadLine_Id) references Task
> Mar 30, 2010 12:10:40 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Escalation add constraint
> FK67B2C6B5F8479DB0 foreign key (Deadline_Escalation_Id) references 
> Deadline
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table I18NText add constraint 
> FK2349686B63D84819
> foreign key (Deadline_Documentation_Id) references Deadline
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00942: table or view does not exist
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Notification_BusinessAdministrators add
> constraint FK4DCCC4FAB009A012 foreign key (entity_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Notification_BusinessAdministrators add
> constraint FK4DCCC4FA9A939BFA foreign key (task_id) references 
> Notification
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Notification_EmailNotificationHeader add
> constraint FK92169CC0379F97EA foreign key (emailHeaders_id) references
> EmailNotificationH
> er
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table Notification_EmailNotificationHeader add
> constraint FK92169CC01B1E66DC foreign key (Notification_id) references
> Notification
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_BusinessAdministrators
> add constraint FK3D56AB6EB009A012 foreign key (entity_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_BusinessAdministrators
> add constraint FK3D56AB6EC834E294 foreign key (task_id) references Task
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_ExcludedOwners add
> constraint FK19190ED2B009A012 foreign key (entity_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_ExcludedOwners add
> constraint FK19190ED2C834E294 foreign key (task_id) references Task
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_PotentialOwners add
> constraint FK9C9C2A96B009A012 foreign key (entity_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_PotentialOwners add
> constraint FK9C9C2A96C834E294 foreign key (task_id) references Task
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_TaskStakeholders add
> constraint FKE6997FFAB009A012 foreign key (entity_id) references
> OrganizationalEntity
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: Unsuccessful: alter table PeopleAssignments_TaskStakeholders add
> constraint FKE6997FFAC834E294 foreign key (task_id) references Task
> Mar 30, 2010 12:10:41 PM org.hibernate.tool.hbm2ddl.SchemaExport create
> SEVERE: ORA-00972: identifier is too long
>
> Mar 30, 2010 12:10:42 PM org.hibernate.tool.hbm2ddl.SchemaExport execute
> INFO: schema export complete
> Hibernate: select task0_.id as col_0_0_, deadline1_.id as col_1_0_,
> deadline1_.date as col_2_0_ from Task task0_, Deadline deadline1_ where
> (deadline1_.id in (selec
> tartdeadl2_.id from Deadline startdeadl2_ where
> task0_.id=startdeadl2_.Deadlines_StartDeadLine_Id) or deadline1_.id in
> (select enddeadlin3_.id from Deadline enddead
> 3_ where task0_.id=enddeadlin3_.Deadlines_EndDeadLine_Id)) and
> deadline1_.escalated=0 order by deadline1_.date
> Mar 30, 2010 12:10:43 PM org.hibernate.util.JDBCExceptionReporter
> logExceptions
> WARNING: SQL Error: 1747, SQLState: 42000
> Mar 30, 2010 12:10:43 PM org.hibernate.util.JDBCExceptionReporter
> logExceptions
> SEVERE: ORA-01747: invalid user.table.column, table.column, or column
> specification
>
> javax.persistence.PersistenceException:
> org.hibernate.exception.SQLGrammarException: could not execute query
>        at
> org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
>        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
>        at org.drools.task.service.TaskService.<init>(TaskService.java:65)
>        at org.drools.task.service.TaskService.<init>(TaskService.java:48)
>        at RunTaskService.start(RunTaskService.java:48)
>        at RunTaskService.main(RunTaskService.java:31)
> Caused by: org.hibernate.exception.SQLGrammarException: could not execute
> query
>        at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
>        at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
>        at org.hibernate.loader.Loader.doList(Loader.java:2223)
>        at
> org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
>        at org.hibernate.loader.Loader.list(Loader.java:2099)
>        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
>        at
> org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
>        at
> org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
>        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
>        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
>        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
>        ... 4 more
> Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column,
> table.column, or column specification
>
>        at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
>        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
>        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
>        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
>        at
> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
>        at
> oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:503)
>        at
> oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:965)
>        at
> oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:535)
>        at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1051)
>        at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
>        at
> oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3026)
>        at
> org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
>        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
>        at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
>        at org.hibernate.loader.Loader.doList(Loader.java:2220)
>        ... 12 more
> Exception in thread "main" java.lang.Exception
>        at RunTaskService.start(RunTaskService.java:53)
>        at RunTaskService.main(RunTaskService.java:31)
> -- 
> View this message in context: 
> http://n3.nabble.com/Error-When-changing-persistence-from-H2-to-Oracle-for-TaskService-tp685431p685431.html
> Sent from the Drools - User mailing list archive at Nabble.com.
> _______________________________________________
> rules-users mailing list
> rules-users at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/rules-users 

Disclaimer: http://www.kuleuven.be/cwis/email_disclaimer.htm