can not perform DDL/DML over objects in Recycle Bin

I got this error today while trying to run jBPM drop/create scripts.

I didn't know Oracle had a recycle bin?

Theoretically the solution is to run, "purge recyclebin;" and wait. I say theoretically because my solution required dropping and recreating the user. Somehow my foreign keys didn't get moved to the recycle bin at the same time as my tables.

Additionally the "purge" command needs to be added to delete statements.

jBPM 3.3.0.GA and Oracle 10g Express Server

I grabbed the jBPM 3.3.0.GA release. I got a process deployed under hsqldb to make sure everything worked. The next step is to switch out hsqldb for Oracle XE. I run Oracle XE under parallels.

The user guide chapter 7 lays out the changes.

One "gotcha: I had was the type mapping in the datasource. "Oracle9i" was the appropriate setting. It took me a couple of tries.

The datasource, located in /jbpm-3.3.0.GA/jboss-4.2.2.GA/server/default/deploy/jbpm, I ended up with :

<?xml version="1.0" encoding="UTF-8"?>

<!-- ===================================================================== -->
<!--                                                                       -->
<!--  JBoss Server Configuration                                           -->
<!--                                                                       -->
<!-- ===================================================================== -->

<!-- $Id: oracle-ds.xml 71535 2008-04-01 07:05:03Z adrian@jboss.org $ -->
<!-- ==================================================================== -->
<!--  Datasource config for Oracle originally from Steven Coy             -->
<!-- ==================================================================== -->


<datasources>
<local-tx-datasource>
 <jndi-name>JbpmDS</jndi-name>
 <connection-url>jdbc:oracle:thin:@IP_ADDRESS:1521:xe</connection-url>
 <!--

     Here are a couple of the possible OCI configurations.
     For more information, see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm

 <connection-url>jdbc:oracle:oci:@youroracle-tns-name</connection-url>
     or
 <connection-url>jdbc:oracle:oci:@(description=(address=(host=youroraclehost)(protocol=tcp)(port=1521))(connect_data=(SERVICE_NAME=yourservicename)))</connection-url>

     Clearly, its better to have TNS set up properly.
  -->
 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
 <user-name>USER_NAME</user-name>
 <password>PASSWORD</password>
 <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
 <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->
 <!-- Checks the Oracle error codes and messages for fatal errors -->
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
     <!-- sql to call when connection is created
     <new-connection-sql>some arbitrary sql</new-connection-sql>
     -->

     <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered
     <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
     -->

   <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
   <metadata>
      <type-mapping>Oracle9i</type-mapping>
   </metadata>
</local-tx-datasource>

</datasources>

The hibernate.cfg.xml, located in /jbpm-3.3.0.GA/jboss-4.2.2.GA/server/default/deploy/jbpm/jbpm-service.sar, I ended up with :

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>

  <!-- hibernate dialect -->
  <!--
  <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
  -->
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</property>

  <!-- JDBC connection properties (begin) ===
  <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
  <property name="hibernate.connection.url">jdbc:hsqldb:mem:jbpm</property>
  <property name="hibernate.connection.username">sa</property>
  <property name="hibernate.connection.password"></property>
  ==== JDBC connection properties (end) -->


  <!-- DataSource properties (begin)
  <property name="hibernate.connection.datasource">java:comp/env/jdbc/JbpmDataSource</property>
  -->
  <property name="hibernate.connection.datasource">java:/JbpmDS</property>
  <!-- DataSource properties (end) -->

  <!-- JTA transaction properties (begin) -->
  <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
  <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
  <!-- JTA transaction properties (end) -->

  <!-- CMT transaction properties (begin) ===
  <property name="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</property>
  <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
  ==== CMT transaction properties (end) -->

  <!-- ################################### -->
  <!-- # common settings                 # -->
  <!-- ################################### -->

  <!-- Automatic schema creation (begin) ===
  <property name="hibernate.hbm2ddl.auto">create</property>
  ==== Automatic schema creation (end) -->

  <!-- Simple memory-only cache -->
  <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

  <!-- logging properties -->
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.use_sql_comments">true</property>

  <!-- ############################################ -->
  <!-- # mapping files with external dependencies # -->
  <!-- ############################################ -->

  <!-- Additional mappings defined per module go here -->
  <mapping resource="hibernate.extra.hbm.xml" />
  <mapping resource="hibernate.identity.hbm.xml" />


 <!-- ###################### -->
 <!-- # jbpm mapping files # -->
 <!-- ###################### -->

 <!-- hql queries and type defs -->
 <mapping resource="org/jbpm/db/hibernate.queries.hbm.xml" />
 <!-- hql queries used in simulation for querying historical data
      uncomment if you want to use the GetSimulationInputCommand
      or maybe you also want to use the queries yourself
      be patient: the queries need the stddev function to be enabled in your dialect
      more information on this can be found here: http://www.camunda.com/business_process_simulation_news/mysql_and_stddev.html -->
 <!--
 <mapping resource="org/jbpm/sim/bam/hibernate.queries.hbm.xml" />
 -->

 <!-- graph.action mapping files -->
 <mapping resource="org/jbpm/graph/action/MailAction.hbm.xml"/>

 <!-- graph.def mapping files -->
 <mapping resource="org/jbpm/graph/def/ProcessDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Node.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Transition.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Event.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/Action.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/SuperState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/def/ExceptionHandler.hbm.xml"/>
 <mapping resource="org/jbpm/instantiation/Delegation.hbm.xml"/>

 <!-- ############################################ -->
 <!-- # another mapping file with external dependencies # -->
 <!-- ############################################ -->
 <!-- following mapping file has a dependency on   -->
 <!-- 'bsh-{version}.jar'.                         -->
 <!-- uncomment this if you don't have bsh on your -->
 <!-- classpath.  you won't be able to use the     -->
 <!-- script element in process definition files   -->
 <!-- has to be defined below org/jbpm/graph/def/Action.hbm.xml -->
 <!-- due to the inline collection-cache elements below -->
 <mapping resource="org/jbpm/graph/action/Script.hbm.xml"/>

 <!-- graph.node mapping files -->
 <mapping resource="org/jbpm/graph/node/StartState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/EndState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/ProcessState.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Decision.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Fork.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/Join.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/MailNode.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/State.hbm.xml"/>
 <mapping resource="org/jbpm/graph/node/TaskNode.hbm.xml"/>

 <!-- context.def mapping files -->
 <mapping resource="org/jbpm/context/def/ContextDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/context/def/VariableAccess.hbm.xml"/>

 <!-- bytes mapping files -->
 <mapping resource="org/jbpm/bytes/ByteArray.hbm.xml"/>

 <!-- module.def mapping files -->
 <mapping resource="org/jbpm/module/def/ModuleDefinition.hbm.xml"/>

 <!-- file.def mapping files -->
 <mapping resource="org/jbpm/file/def/FileDefinition.hbm.xml"/>

 <!-- taskmgmt.def mapping files -->
 <mapping resource="org/jbpm/taskmgmt/def/TaskMgmtDefinition.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/Swimlane.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/Task.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/def/TaskController.hbm.xml"/>

 <!-- scheduler.def mapping files -->
 <mapping resource="org/jbpm/scheduler/def/CreateTimerAction.hbm.xml"/>
 <mapping resource="org/jbpm/scheduler/def/CancelTimerAction.hbm.xml"/>

 <!-- graph.exe mapping files -->
 <mapping resource="org/jbpm/graph/exe/Comment.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/ProcessInstance.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/Token.hbm.xml"/>
 <mapping resource="org/jbpm/graph/exe/RuntimeAction.hbm.xml"/>

 <!-- module.exe mapping files -->
 <mapping resource="org/jbpm/module/exe/ModuleInstance.hbm.xml"/>

 <!-- context.exe mapping files -->
 <mapping resource="org/jbpm/context/exe/ContextInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/TokenVariableMap.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/VariableInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/ByteArrayInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/DateInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/DoubleInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/HibernateLongInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/HibernateStringInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/LongInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/NullInstance.hbm.xml"/>
 <mapping resource="org/jbpm/context/exe/variableinstance/StringInstance.hbm.xml"/>

 <!-- job mapping files -->
 <mapping resource="org/jbpm/job/Job.hbm.xml"/>
 <mapping resource="org/jbpm/job/Timer.hbm.xml"/>
 <mapping resource="org/jbpm/job/ExecuteNodeJob.hbm.xml"/>
 <mapping resource="org/jbpm/job/ExecuteActionJob.hbm.xml"/>
 <mapping resource="org/jbpm/job/CleanUpProcessJob.hbm.xml"/>

 <!-- taskmgmt.exe mapping files -->
 <mapping resource="org/jbpm/taskmgmt/exe/TaskMgmtInstance.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/TaskInstance.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/PooledActor.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/exe/SwimlaneInstance.hbm.xml"/>

 <!-- logging mapping files -->
 <mapping resource="org/jbpm/logging/log/ProcessLog.hbm.xml"/>
 <mapping resource="org/jbpm/logging/log/MessageLog.hbm.xml"/>
 <mapping resource="org/jbpm/logging/log/CompositeLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ActionLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/NodeLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessInstanceCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessInstanceEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/ProcessStateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/SignalLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TokenCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TokenEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/graph/log/TransitionLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableDeleteLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/VariableUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/ByteArrayUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/DateUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/DoubleUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/HibernateLongUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/HibernateStringUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/LongUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/context/log/variableinstance/StringUpdateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskAssignLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/TaskEndLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneCreateLog.hbm.xml"/>
 <mapping resource="org/jbpm/taskmgmt/log/SwimlaneAssignLog.hbm.xml"/>

<!-- ################################### -->
<!-- # cache settings                  # -->
<!-- # strategy="nonstrict-read-write" # -->
<!-- # can be used with hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider # -->
<!-- ################################### -->

<class-cache class="org.jbpm.context.def.VariableAccess" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.file.def.FileDefinition.processFiles" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.graph.action.Script.variableAccesses" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.Action" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.Event" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Event.actions" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.ExceptionHandler" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ExceptionHandler.actions" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.Node" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Node.events" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Node.exceptionHandlers" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Node.leavingTransitions" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Node.arrivingTransitions" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.ProcessDefinition" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ProcessDefinition.events" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ProcessDefinition.exceptionHandlers" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ProcessDefinition.nodes" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ProcessDefinition.actions" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.ProcessDefinition.definitions" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.graph.def.SuperState.nodes" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.graph.def.Transition" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Transition.events" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.graph.def.Transition.exceptionHandlers" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.graph.node.Decision.decisionConditions" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.graph.node.ProcessState.variableAccesses" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.graph.node.TaskNode.tasks" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.instantiation.Delegation" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.module.def.ModuleDefinition" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.taskmgmt.def.Swimlane.tasks" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.taskmgmt.def.TaskController" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.taskmgmt.def.TaskController.variableAccesses" usage="nonstrict-read-write" />

<class-cache class="org.jbpm.taskmgmt.def.Task" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.taskmgmt.def.Task.events" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.taskmgmt.def.Task.exceptionHandlers" usage="nonstrict-read-write" />

<collection-cache collection="org.jbpm.taskmgmt.def.TaskMgmtDefinition.swimlanes" usage="nonstrict-read-write" />
<collection-cache collection="org.jbpm.taskmgmt.def.TaskMgmtDefinition.tasks" usage="nonstrict-read-write" />


</session-factory>
</hibernate-configuration>

Seam-gen and Oracle Express

I installed Oracle Express.  I created a user, "local," with the creative password, "local."  I created a project with seam-gen.  I created an entity object and annotated the id column with :

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "id", unique = true, nullable = false)
private Long id;

On deploy I saw in the logs :

09:42:14,281 INFO  [SchemaExport] exporting generated schema to database
09:42:14,609 ERROR [SchemaExport] Unsuccessful: create table TABLE_NAME (id number(19,0) not null unique, name varchar2(30) not null, description varchar2(200) not null, inputChannel varchar2(100) not null, outputChannel varchar2(100) not null, parser varchar2(100), primary key (id))
09:42:14,609 ERROR [SchemaExport] ORA-02261: such unique or primary key already exists in the table

The message, "such unique or primary key already exists in the table," led me to believe that the table existed.  However, I couldn't see it using the Oracle web interface.  Or SQL Developer.  Or MyEclipse. 

So I ran the statement in the web interface.  Same error. 

The problem turned out to be that the statement was creating the id twice.  First with, "id number(19,0) not null unique," and second with, "primary key (id)."

I changed the syntax, and it worked.  As for the annotations, I removed the "Column" annotation :

@Column(name = "id", unique = true, nullable = false)

When I redeployed it worked fine.

The working annotation was :

@Id
@SequenceGenerator(name = "TABLE_NAME_SEQUENCE_GENERATOR", sequenceName = "TABLE_NAME_SEQUENCE")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TABLE_NAME_SEQUENCE_GENERATOR")
private Long id;