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.
Make bash scripts crontab-compatible
When writing shell scripts in bash, consider whether your script can be automated in a batch scheduler like cron. If you reference any external resources in your script relative to the directory in which it resides, those references may break when running your script through cron.
By default, crontab runs scripts in the user’s home directory, as specified in /etc/passwd. For user “Tim” with home directory /home/tim, a sample crontab might look something like the following:
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?
