JavaRanch Home    
 
This page:         last edited 06 December 2012         What's Changed?         Edit

Oracle FAQ   

General Oracle-related JDBC questions

Oracle-specific JDBC questions

Warning: this section deals with Oracle-specific JDBC code. If you follow advice in this section, your code won't be portable to other databases.

Non-JDBC questions

Other Resources and Valuable Links



  Q: Which version of JDBC driver shall I use?

Firstly, have a look at the driver compatibility matrix. You need to choose a version that is compatible with the version(s) of the database you're going to connect to.

You might also want to make sure the driver version you choose supports certain version of JDBC specification.

That still leaves a lot of options, though. Our suggestion is to use the newest compatible version, since it may contain improvements and optimizations not available in prior versions. A good example is the JDBC update batching, which offers the best performance in the 11g version of the driver. Prior to that, you'd have to use the Oracle's own way of update batching to attain the topmost performance.

The above paragraphs do not distinguish different types of drivers. The JDBC Type 4 (thin) is mostly used nowadays, a brief introduction of other types of drivers can be found here.



  Q: How to use several different 'databases' in one database instance?

This question is often asked by people who move to Oracle from other RDBMs. Some database servers allow to create separate 'databases' on a single server, thereby logically separating data from different applications being run on the same server.

Oracle database does not offer such a functionality. To separate data from other applications you need to put them into different schemas (where schema roughly corresponds to a user in Oracle database).

In practice, it is often desirable to use at least two distinct schemas in an application anyway: one to actually hold the database objects, and another to connect to the database and actually access the data. This setup provides better security, as only the minimal set of privileges can be granted to the user(s) through which the connection will be made.

However, this brings in one more complication: the database objects, which now reside in a different schema, must be referred to using fully qualified name. This difficulty can be resolved by issuing the following statement:

ALTER SESSION SET CURRENT_SCHEMA=MY_SCHEMA

in the JDBC connection, where MY_SCHEMA is the name of the schema containing the database objects. This command affects the entire session until it is closed, or a new schema is set using the same statement. If you're using a connection pool, it means that setting the schema again on a connection that was previously returned to a pool is useless. Some connection pools allow you to assign values to connections so that you could mark the connection when you first set the schema and avoid setting the schema unless actually required, saving some resources and database roundtrips.

(Please note that you cannot use bind variable to fill in the schema name, and therefore need to pay attention not to be subject of SQL injection attack when issuing this statement.)



  Q: How to correctly use pagination queries in Oracle?

Pagination queries are used whenever the result of an SQL query has to be split into several parts, typically to be displayed over several pages.

The subject of pagination queries is a little bit complicated in Oracle. Oracle doesn't provide a LIMIT clause, so the output of queries has to be limited to the desired set of rows using proper WHERE clause. The widely known ROWNUM pseudocolumn can be used for this, but it is not as straightforward as it might seem. For example, the following query (intended to get rows 6 to 10 as ordered by a NAME column) doesn't work:

SELECT * FROM MY_TABLE WHERE ROWNUM > 5 AND ROWNUM <= 10 ORDER BY NAME;

This query will never return any row. The problem is that ROWNUM numbers are assigned only to the rows that are selected by the WHERE clause, always starting at 1. To get a row with number greater than 5, rows numbered 1 to 5 would have to be generated first, but these rows will never be generated, since the WHERE condition doesn't ever select them.

It is possible to overcome this arcane limitation by computing the ROWNUM in an inner (nested) query. This way, the row numbers assigned by to the ROWNUM pseudocolumn while processing the query can be "freezed". Correct pagination queries for row with numbers 1-5, 6-10 and 11-15 will therefore look like this:

SELECT * FROM (SELECT MY_TABLE.*, ROWNUM RN FROM MY_TABLE WHERE ROWNUM <= 5 ORDER BY NAME) WHERE RN >=  1;  
SELECT * FROM (SELECT MY_TABLE.*, ROWNUM RN FROM MY_TABLE WHERE ROWNUM <=10 ORDER BY NAME) WHERE RN >=  6;  
SELECT * FROM (SELECT MY_TABLE.*, ROWNUM RN FROM MY_TABLE WHERE ROWNUM <=15 ORDER BY NAME) WHERE RN >= 11;  

A slightly less convoluted is pagination using ROW_NUMBER analytic function. Unlike the ROWNUM, this function always assigns numbers, even if the rows are not selected by a WHERE clause. Unfortunately, analytic functions cannot be directly used in WHERE clauses, so an inner query is still required:

SELECT * FROM (SELECT MY_TABLE.*, ROW_NUMBER() OVER (ORDER BY NAME) RN FROM MY_TABLE) WHERE RN >=  1 AND RN <=  5;  
SELECT * FROM (SELECT MY_TABLE.*, ROW_NUMBER() OVER (ORDER BY NAME) RN FROM MY_TABLE) WHERE RN >=  6 AND RN <= 10;  
SELECT * FROM (SELECT MY_TABLE.*, ROW_NUMBER() OVER (ORDER BY NAME) RN FROM MY_TABLE) WHERE RN >= 11 AND RN <= 15;  

In real-world code, you'd use parameters instead of hard-coded numbers, of course (see PreparedStatement). And don't ever forget to use an ORDER BY in a pagination query -- paginating some data without specifying the order doesn't really make much sense.



CategoryFaq JdbcFaq

JavaRanchContact us — Copyright © 1998-2014 Paul Wheaton