Oracle PL/SQL Associative Arrays Example

I used this example in a presentation the other day, so I figured I’d post it up for anyone who is interested. This uses features that are new to Oracle 11g (such as Indexing by a VARCHAR2 key). Sample Code showing use of Associative Arrays declare — create an associative array data type type account_map [...]

How to select something “from dual” in a non-Oracle database

What is the dual equivelant in Postgres, MySQL, Sqlite, or Ms SQL Server? I had someone ask me yesterday what Sqlite calls the dual pseudotable (for those who don’t know, dual is a heavily used, Oracle specific concept). I started at him blankly for a minute and thought how best to answer without laughing. But [...]

Oracle: Insert All / Sequence Headaches (Error: ORA-02287)

I am really quite fond of the Oracle’s INSERT ALL statement, despite the occasionally critical blog entry. The flaw I wish to harp on today is the error that pops up when you try to use a sequence in the INSERT ALL statement. I, along with countless other DB developers, use sequences just about every [...]

Inserting multiple rows in one SQL statement

Background So, the other day, I opened up a MySQL dump file just to graze over it and see how they did things (you can call be weird if you want to, I prefer to think of it as “curious”). Anyway, I noticed something very cool that I thought was absolutely ingenious. They were inserting [...]

Comparison of Auto Incrementing Primary Key feature in several SQL Dialects

The Problem The whole point in using SQL to interact with a database is for developers to be able to use the same basic language to interact with any database. The intentions were good but, as it turns out, even something as universally desired as having a primary key that automatically increments itself is not [...]