How to Write Multiset Conditions With Oracle VARRAY Types

Oracle is one of the few databases that implements the SQL standard ORDBMS extensions, which essentially allow for nested collections. Other databases that have these features to some extent are CUBRID, Informix, PostgreSQL. Oracle has two types of nested collections: -- Nested tables CREATE TYPE t1 AS TABLE OF VARCHAR2(10); / -- Varrays CREATE TYPE … Continue reading How to Write Multiset Conditions With Oracle VARRAY Types

How to Generate at Least One Row in SQL

There are some situations where you would like to have at least one (empty) row in your result set in SQL. Imagine the following situation. We're querying the Sakila database for actors and their films: SELECT first_name, last_name, title FROM actor JOIN film_actor USING (actor_id) JOIN film USING (film_id) ORDER BY 1, 2, 3 yielding … Continue reading How to Generate at Least One Row in SQL

Oracle 12c Goodies: { CROSS | OUTER } APPLY

I can't believe my eyes. Has this been openly communicated by Oracle? I haven't seen too many blog posts on that matter. Apart from introducing the awesome SQL Standard OFFSET .. FETCH clause (just like SQL Server 2012), there seems to be now also { CROSS | OUTER } APPLY in Oracle 12c! Check out the … Continue reading Oracle 12c Goodies: { CROSS | OUTER } APPLY