Win a copy of The Java Performance Companion this week in the Performance forum!
    Bookmark Topic Watch Topic
 
friki data migration
Ranch Hand
Posts: 772
  • Mark post as helpful
  • send pies
  • Report post to moderator
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?
  • How to correctly use pagination queries in Oracle?


  • 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:



    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:



    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:



    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:



    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
     
      Bookmark Topic Watch Topic
    • New Topic