beware of the file name, save them in the server instance's deploy directory, eg. $JBOSS_HOME/server/default/deploy/oracle-ds.xml

Local-TX

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

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

<!-- $Id: oracle-ds.xml,v 1.6 2004/09/15 14:37:40 loubyansky Exp $ -->
<!-- ==================================================================== -->
<!--  Datasource config for Oracle originally from Steven Coy             -->
<!-- ==================================================================== -->


<datasources>
  <local-tx-datasource>
    <jndi-name>OracleDS</jndi-name>
    <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>
     <!--
          See on WIKI page below how to use Oracle's thin JDBC driver to connect with enterprise RAC.
      -->
     <!--
          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>x</user-name>
    <password>y</password>
 
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>

    <!-- 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>

XA

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

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

<!-- $Id: oracle-xa-ds.xml,v 1.13 2004/09/15 14:37:40 loubyansky Exp $ -->

<!-- ===================================================================== -->
<!-- ATTENTION:  DO NOT FORGET TO SET Pad=true IN transaction-service.xml  -->
<!-- ===================================================================== -->

<datasources>
  <xa-datasource>
    <jndi-name>XAOracleDS</jndi-name>
    <track-connection-by-tx></track-connection-by-tx>
    <isSameRM-override-value>false</isSameRM-override-value>
    <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
    <xa-datasource-property name="URL">jdbc:oracle:oci8:@tc</xa-datasource-property>
    <xa-datasource-property name="User">scott</xa-datasource-property>
    <xa-datasource-property name="Password">tiger</xa-datasource-property>
    <!-- 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>
    <!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa -->
    <no-tx-separate-pools></no-tx-separate-pools>

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

  <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter" 
         name="jboss.jca:service=OracleXAExceptionFormatter">
    <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends>
  </mbean>

</datasources>

Configuring a DataSource using Oracle's thin JDBC driver to connect with enterprise RAC

The magic is in the long connection-url. The two hostnames provide a load balancing and failover layer to the underlying physical database:

...
<connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=xxxxhost1)(port=1521))(address=(protocol=tcp)(host=xxxxhost2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=(type=select)(method=basic))))</connection-url>
...

Note: Example only confirmed against Oracle 10g.

Changes in Oracle 10g JDBC driver

Preliminary testing seems to suggest that it is no longer necessary to enable the Pad option in your jboss-service.xml file. Further, the need for the <no-tx-seperate-pool/>

configuration appears to have also been removed. Note, to date, testing has been limited and only against the latest version of Oracle with the latest JDBC drivers.

Type mapping for Oracle 10g

You have to specify Oracle9i type mapping for Oracle10g datasource configuration :

....
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
....

How to retrieve the underlying Oracle connection object?

   Connection conn = myJBossDatasource.getConnection();
   WrappedConnection wrappedConn = (WrappedConnection)conn;
   Connection underlyingConn = wrappedConn.getUnderlyingConnection();
   OracleConnection oracleConn = (OracleConnection)underlyingConn;

Referenced by: