Spent an embarrassingly long period of time on an error with a FORALL loop. Can you see what my error is? Every time I ran the above block of code, I would get the following error: Error report – ORA-06550:… Read More
Oracle Network Toubleshooting Guide
I hate troubleshooting Oracle Network Problems the most!!! I would MUCH rather see an ORA-600 error. Because I’ve come across these errors and I know how painful they can be, I’ve set up this little troubleshooting guide that will hopefully… Read More
Why Is My Cursor Not Shared?
Many times, Oracle generates a new cursor for a query that already has a child cursor in the shared pool. When this happens, people often ask the question “why is my cursor not being shared?” Fortunately, there’s an Oracle view… Read More
Summing Interval Data Types
Something I’ve occasionally thought would be really nice is if Oracle had a good way of summing interval data types, much the same way that you can SUM() number data types. Occasionally I’ve designed processes that record start and stop… Read More
How to tell if you have a multi-pass hash join
A hash join can be either an optimal hash join, a one-pass hash join, or a multi-pass hash join. Optimal hash joins – The entire build table fits nicely into memory One-pass hash joins – The build table did not… Read More
Handy trick to avoid ORA-54013: INSERT operation disallowed on virtual columns
In some regards, virtual columns are great: Virtual columns don’t take up physical disk space, which allow for skinnier tables. However, an annoyance with virtual columns is Oracle will give you an ORA-54013 error if you try to insert a… Read More
How to cancel a query in SQL Developer
A common question that user’s of Oracle’s SQL Developer ask is “How can I cancel a query in SQL Developer?” Many times, the reason for this is, out of the box, SQL Developer uses a jdbc “thin” driver. This thin… Read More
ORA-54012: virtual column is referenced in a column expression
ORA-54012 is caused when you try to create a table where one virtual column is attempting to reference a different virtual column. Oracle has created the restriction of preventing one virtual column from referencing another. This helps to prevent complexities… Read More
Reasons Oracle might pick a bad execution plan
Below is a list of possibilities as to why Oracle might pick a subpar execution plan: Your tables/indexes have missing or stale statistics (or stats are missing in table partitions/subpartitions or index partitions/subpartitions) Your data violates “uniformity” assumptions that Oracle… Read More
ORA-21000: error number argument to raise_application_error of %s%s is out of range
For some reason when using the raise_application_error() function in my code, I can never remember the valid numeric values. The answer is a simple mnemonic that’s in the title of this blog post. The valid range for an error is… Read More