06/01/2012   Linklog:  Craig Shallahamer: Oracle Database Row versus Set Processing, Surprise!

To folks writing database-driven reports for the web: Craig Shallahamer shows why you should let the (Oracle) database handle as much of your selection and transformation logic as possible.


05/25/2012   Linklog:  Jake Feasel: SQLFiddle

Build your own mini-schema and run queries against it. Publish your experiments for your friends to tweak. Or try out the examples from your favorite SQL book without having to build a database first. Works for Oracle, PostgreSQL, MySQL, and SQL Server flavors. Pretty cool!



I ran into an interesting problem recently when attempting to publish an MS Access application with Citrix XenApp. The application pulled data from an Oracle database, so an ODBC connection was created on the Citrix server. What we found was that when a non-privileged user tried to run the application, the connection failed. When the Citrix server admin ran it, however, it worked. Furthermore, as long as the admin was logged in, anybody else could run the application successfully; when he logged out, the users’ connections broke again.
Read more


At my current client I am responsible for administering a third-party Java appplication that unfortunately generates a lot of performance complaints. It’s a multi-tiered system with the middle-tier running on Oracle Appication Server 10.1.3. I am already familiar with GUI profiling tools like jconsole and jvisualvm, both of which are included with Oracle’s JDK, and I have played around with the profiling capabilities in NetBeans. All of these profiling tools are fairly similar to one another in look and functionality.
Read more


In part one 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.
Read more


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. CREATE OR REPLACE PACKAGE example AS TYPE nested_table_typ IS TABLE OF VARCHAR2(100); FUNCTION get_nested_table_data RETURN nested_table_typ PIPELINED; END example; / CREATE OR REPLACE PACKAGE BODY example AS FUNCTION get_nested_table_data RETURN nested_table_typ PIPELINED IS BEGIN PIPE ROW ('one'); PIPE ROW ('two'); PIPE ROW ('three'); RETURN; END get_nested_table_data; END example; / The package function example.
Read more


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:
Read more


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.
Read more