Friday, May 13, 2011

Tomcat Resource Configuration for Oracle Database Connection

Tomcat has a Database Connection Pool mechanism that will work for most of the database including Oracle. The mechanism described in the tomcat web site will work for most situations. Using the above method will make the code not depend on a specific database.

However there are situations where the application code is dependent on Oracle database. One such example is when you use Oracle Stored procedures and you need to use Oracle Cursor as on output parameter. In such case you will have to cast the Statement to Oracle specific oracle.jdbc.OracleCallableStatement class to get the cursor.

However if you simply cast the java.sql.CallableStatement to oracle.jdbc.OracleCallableStatement you may notice that you get a class cast exception if you configured your DataSource using the above method.

To overcome the class cast exception, you need to configure the data source using a Oracle specific connection factory. The following example will server that purpose. The text in bold are the changes pertaining to the Oracle connection factory.

<Resource name="jdbc/OracleDS" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
user="DBUser"
password="xxxxxx"
driverClassName="oracle.jdbc.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=your.db.host)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=serviceName)))"
maxActive="?"
maxIdle="?"
maxWait="-1"/>

6 comments:

Lauraine said...

I have no words for this great post such a awe-some information i got gathered. Thanks to Author.
html5 converter

b. said...

Thanks a lot man

James said...

Oracle Database Link Tomcat Resource Configuration The Database Link Pool function in Tomcat works with most databases, including Oracle. If you want to take ebook writing services so you can contact us.

keven john said...

Oracle is a good database and secure data base as compare to sql and my sql.If you are looking to buy an online Copywriting Service In Karachi to help the students of college and university at an affordable and reasonable price.

Alexa Demie said...

Previously I had a little know how about Tomcat has a Database Connection Pool mechanism but after reading your post I am glad I know more now. I am a UK study visa consultant by profession and I like reading your blogs.

Hannah Walters said...

Ensures seamless integration with Oracle databases by configuring the DataSource with an Oracle-specific connection factory, addressing potential class cast exceptions encountered when utilizing Oracle-specific features like stored procedures with Oracle Cursors. This configuration is particularly beneficial for services such as 'nursing assignment writing service,' where reliance on Oracle database functionality is common and requires smooth interoperability.