0 Replies Latest reply on Dec 15, 2010 9:12 AM by grosueugen

    Changing to Oracle DB fails when using <xa-datasource>

    grosueugen Newbie

      Hello,

       

      I'm using JBoss 5.1.0.GA, jdk 1.6, on Linux

       

      I wanted to use the Oracle DB for messaging, so I started like this:

      - created a new schema in Oracle

      - creating a new oracle-xa-ds.xml file, using <xa-datasource> (we use distributed transaction because we write into another Oracle DB, while   adding to a Queue)

      - copied from JBOSS_HOME/docs/examples/jms/oracle-persistence-service.xml to xxx/deploy/messaging/oracle-persistence-service.xml.

      - replaced DefaultDS with MyOracleDS in all 3 places

      - deleted xxx/deploy/messaging/hsqldb-persistence-service.xml

       

      When starting JBoss, the error was:

      => java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

       

      After adding in jboss-log4j.xml

       

         <category name="org.jboss.messaging">
            <priority value="DEBUG"/>
         </category>

       

      => CREATE TABLE JBM_MSG (MESSAGE_ID INTEGER, RELIABLE CHAR(1), EXPIRATION INTEGER, TIMESTAMP INTEGER, PRIORITY INTEGER, TYPE INTEGER, HEADERS BLOB, PAYLOAD BLOB, PRIMARY KEY (MESSAGE_ID))
      java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session

       

      So, what I did was to replace <xa-datasource> with <local-datasource>, and it worked fine: all tables were added to the Oracle schema.

      Afterwords, I put all "CreateTablesOnStartup" to false oracle-persistence-service.xml and put back <xa-datasource>.

       

      I did a test, and it adds messages successfully to the Oracle DB.