Here’s a fun little experiment (I’m using Oracle 19.0 for this). First, let’s start off by creating a couple of tables. Cool! Now that we got our tables, let’s join them in a query and look a the plan. Here’s… Read More
How to Fix “ORA-01489: Result of String Concatenation is Too Long” When Using Listagg
If you’re using the LISTAGG() function in Oracle, and you’ve encountered the error ORA-01489: result of string concatenation is too long it’s because you’re trying to create a comma-delimited list that is too long for a VARCHAR2. In Oracle, a… Read More
ORA-12727: Invalid Back Reference In Regular Expression
If you’ve run across ORA-12727: invalid back reference in regular expression, it’s because you’ve referenced a group (a group is an expression enclosed in parenthesis) either that doesn’t exist, or doesn’t exist yet. There are two common reasons for this… Read More
Index Range Scan vs. Leading Wildcard
Can you use an index range scan with a LIKE if you have a leading wildcard in your expression? e.g. Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables,… Read More
Oracle: Coding Around NULL Values
In Oracle, NULLs can complicate your code–a lot. Suppose, for example, you want to see if two different values are the same in a SQL query. You might be tempted to write something like this: The problem with the above… Read More