Table Functions: 9i Feature Worth Revisiting (Part 2)
In part I of this series I showed a simple example of how table functions can be used to treat PL/SQL collection types as tables in a SQL query. We used a nested table collection type to which we manually added a few entries. In this example, I intend to show a more practical use of table functions using all three PL/SQL collection types: associative arrays, nested tables, and VARRAYs.
Get Oracle Instant Client Working on Mac OS X Lion
CJ Travis has an excellent and thorough article on installing Oracle Instant Client on Mac OS X 10.7. The gist: you have to install the 32-bit version. As a bonus, he tells you how to download and install the handy rlwrap command line utility, which gives you the ability to navigate the statement history in SQL*Plus in a very bash-like fashion, using the up and down keys.
Table Functions: 9i Feature Worth Revisiting (Part 1)
Yes, they’ve been around for a while, but if you haven’t made use of table functions lately you might want to reacquaint yourself with this old gem. Table functions allow you to query the contents of PL/SQL collection types using SQL’s TABLE operator. Here is a simple example.
Invalidating Toplink Session Cache To Avoid Stale Data
Toplink is a Java framework that maps objects to database tables. It is the reference implementation for the Java Persistence Architecture, or JPA. Developers use Toplink to manage data persistence (storage), queries, and transactions in an Oracle database. By default, Toplink makes use of a special cache called a Session Cache, maintained on the server, which is meant to speed up performance. Queried data is stored in the Session Cache and used in subsequent queries to reduce or eliminate calls to the database.
One problem with this approach is how to deal with “stale data”. In other words, what happens when data is changing in the database? How does the developer ensure that the cached data is current?
JDBC Connection Pooling for Oracle Databases
While Database Connection Pooling is not a new concept, it is becoming increasingly more important with the proliferation of both public and private web-based applications. This post will cover using a JDBC connection pool against an Oracle database. The general idea of Database Connection Pooling is universal and these concepts can be used with any app server / database combination. If you have never used a database connection pool before, Apache has an open source product called DBCP which is fairly well documented for use with the Tomcat app server.
What is Database Connection Pooling
Database Connection Pooling involves creating a persistent group of Oracle sessions, all waiting to fulfill database requests. When a pooled connection is used, it is not destroyed like a regular database session. It is returned to the pool so that it can be used to fulfill another request. The reason for the existence of Database Connection Pools is to eliminate the process (and time required) to create and destroy database connections. Because Oracle is a multi-process server, establishing a database session is considered expensive as it involves creating an operating system process and allocation of server memory. A busy web-based applicaiton with a few hundred users can easily generate thousands of logins per minute (we’ve seen this situation more than once). Although logins look like they happen pretty fast, the time spent waiting adds up very quickly. The bottom line is that logins just burn CPU.
