Oracle FAQ   

General Oracle-related JDBC questions

• Where do I get JDBC drivers for Oracle (various versions)?
• What are all the driver files for?
• Oracle JDBC Developer's Guide: 10g, 10g Release 2, 11g Release 1, 11g Release 2
• Oracle JDBC Java API (Javadoc) (older version are available at the corresponding driver download page)
• Official Oracle JDBC FAQ
• Which version of JDBC driver shall I use?
• What should the URL for Oracle driver look like? (see also the immediatelly following point on that page)

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.

• How to use arrays of Oracle SQL objects?

Non-JDBC questions

• Is there a free edition of an Oracle database?
• How to use several different 'databases' on one Oracle server?

Other Resources and Valuable Links

• Oracle Technology Network. Downloads for the Database, the official documentation, and much more.
• oracle-developer.net. Great articles about what's new in the most recent Oracle versions
• Ask Tom. Where one of the most respected expert in the field will help you
• Richard Foote's Oracle Blog. Blog focusing specifically on Oracle Indexes and Database Administration (advanced)

---

  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.)

---

CategoryFaq JdbcFaq