What are some common pitfalls when working with Oracle from an outside program? How do you support connection pooling from an external application with Oracle? Finally, how are Oracle connections handled from a provider?
More Oracle database answers:
Learn how to manage SQL parent-child table relations
Where to look for Oracle DBA learning materials
All answers from Brian Peasland
You need to make sure that the application is able to connect to the database and that the connectivity provider is not hampering performance between the database and the application. How this is handled depends on the specific application provider. For instance, with an Oracle Open Database Connectivity (ODBC) driver, you can set the fetch buffer size to control how much data is sent across the network in each batch for your Oracle connections.
Connection pooling configuration depends on your application platform. For instance, if your application is Java and you are using Java Database Connectivity (JDBC) drivers for your Oracle connections, you can set up JDBC connection pools. This document describes how to do this in Oracle 11g. This document describes the same thing for .NET applications using the Data Provider for Oracle.
Oracle connections are handled differently for different providers. The Oracle network stack provides a level of abstraction. It does not make a difference to an Oracle instance if the connection is using ODBC, JDBC or .NET Data Provider for Oracle. To the Oracle instance, they are all the same thing. Any optimization has to be done on the application side.
This was first published in July 2013