1 Reply Latest reply on Mar 13, 2006 2:36 PM by Joshua Preston

    Specifying Database Schema

    Joshua Preston Newbie

      Hey guys,

      I am running JBoss 4.0.3SP1 utilizing a poorly clustered Oracle database. I have run into a problem where the user that logs into the database must use a different database schema than the default one. How can I specify what database schema to use?

      By default, if I use the user: db_username to login to the database, the schema it uses is DB_USERNAME. I am however obliged to use the schema DB_ANOTHERUSER for one specific web application. I must however, log in as db_username to perform this duty.

      Below is my oracle-ds.xml which works, but does not specify the database schema to use, which of course causes all sorts of errors because the database schema in use, is not the correct one.

      <?xml version="1.0" encoding="UTF-8"?>
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>OracleDS</jndi-name>
       <connection-url>jdbc:oracle:thin:@db4:1521:DB01</connection-url>
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <user-name>db_username</user-name>
       <password>db_password</password>
      
       <!-- 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>
      
       <metadata>
       <type-mapping>Oracle8</type-mapping>
       </metadata>
       </local-tx-datasource>
      
      </datasources>
      


      Any ideas?

      Thanks!

      Joshua Preston.


        • 1. Re: Specifying Database Schema
          Joshua Preston Newbie

          Nevermind,

          As a side note, I have smacked myself and yelled: RTFM, or at least, RTFFYI. Anyhow, if anyone else gets stuck doing this, I've figured out that you can specify SQL statements to be run upon valid connection. Therefore, modifying my orace-ds.xml by looking at the WIKI where it states:


          <new-connection-sql> - an sql statement that is executed against each new connection. This can be used to set the connection schema, etc.


          I've added a line to my oracle-dx.xml that looks like:

          <new-connection-sql>ALTER SESSION SET current_schema=DB_ANOTHERUSER</new-connection-sql>
          


          I will not say this is the most elegant way nor it is the proper way, but it does work for me :-D Anyhow, maybe it will help someone down the road!

          Thanks!

          Joshua Preston.