PL/SQL backtraces for debugging

For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces. When your application raises an error somewhere deep down in the call stack, you don't get immediate information about the exact source of the error. For large PL/SQL applications, this can be a … Continue reading PL/SQL backtraces for debugging

Integrating jOOQ with PostgreSQL: Partitioning

Introduction jOOQ is a great framework when you want to work with SQL in Java without having too much ORM in your way. At the same time, it can be integrated into many environments as it is offering you support for many database-specific features. One such database-specific feature is partitioning in PostgreSQL. Partitioning in PostgreSQL … Continue reading Integrating jOOQ with PostgreSQL: Partitioning

All You Ever Need to Know About Recursive SQL

Oracle SYNONYMs are a great feature. You can implement all sorts of backwards-compatibility tweaks simply by creating SYNONYMs in your database. Consider the following schema: CREATE TABLE my_table (col NUMBER(7)); CREATE SYNONYM my_table_old FOR my_table; CREATE SYNONYM my_table_bak FOR my_table_old; Now you can query your same old table through three different names, it'll all result … Continue reading All You Ever Need to Know About Recursive SQL

jOOQ Newsletter: August 15, 2014 – jOOQ 3.5 Outlook

Subscribe to this newsletter here jOOQ 3.5 Outlook We're working hard on the next release. Already 90 issues for jOOQ 3.5 are closed and counting! Today, we're going to look at the highlights of what will be implemented in the next, exciting minor release, due for Q4 2014: Support for new databases Our customers have … Continue reading jOOQ Newsletter: August 15, 2014 – jOOQ 3.5 Outlook

A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)

Have you ever wondered about the use-case behind SQL's ANY (also: SOME) and ALL keywords? You have probably not yet encountered these keywords in the wild. Yet they can be extremely useful. But first, let's see how they're defined in the SQL standard. The easy part: 8.7 <quantified comparison predicate> Function Specify a quantified comparison. … Continue reading A Wonderful SQL Feature: Quantified Comparison Predicates (ANY, ALL)

The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

One of the best features in SQL are window functions. Dimitri Fontaine put it bluntly: There was SQL before window functions and SQL after window functions If you're lucky enough to be using any of these databases, then you can use window functions yourself: CUBRIDDB2FirebirdH2InformixMariaDBMySQLOraclePostgreSQLSQLiteSQL ServerSybase SQL AnywhereTeradata (source here) One of the most obvious … Continue reading The Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()

The “Free”, “Standard”, “Open” Software Heresy

There are those people that have a strong, dogmatic belief in what they call "Free" or "Standard" or "Open" software. One of those individuals is Jimmie (let's call him Jimmie in this article) who has responded to an article about Java persistence by Marco Behler on TheServerSide. Let me cite Jimmie's response here: JPA is … Continue reading The “Free”, “Standard”, “Open” Software Heresy

SQL Tip of the Day: Be Wary of SELECT COUNT(*)

Recently, I've encountered this sort of query all over the place at a customer site: DECLARE v_var NUMBER(10); BEGIN SELECT COUNT(*) INTO v_var FROM table1 JOIN table2 ON table1.t1_id = table2.t1_id JOIN table3 ON table2.t2_id = table3.t2_id ... WHERE some_predicate; IF (v_var = 0) THEN do_something ELSE do_something_else END IF; END; Unfortunately, COUNT(*) is often … Continue reading SQL Tip of the Day: Be Wary of SELECT COUNT(*)

Join the No OFFSET Movement!

Markus Winand from Use The Index, Luke! did it again. He started an exciting battle against one the biggest flaws in the SQL language: We've blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn't even implement it correctly, yet (and your emulation … Continue reading Join the No OFFSET Movement!

Are You Using SQL PIVOT Yet? You Should!

Every once in a while, we run into these rare SQL issues where we'd like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You Using SQL PIVOT Yet? You Should!