Monthly Archives: April 2018

COLLECT INTO inside a Cursor Definition?

Oren Nakdimon 1 Reply

I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well. So when I saw the following piece of code a few days ago, I was willing to bet it would not compile: declare […]

Continue reading

Excessive Locking when Dropping a Table

Oren Nakdimon Leave a Reply

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”. That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys. A quick investigation revealed the cause – the DROP […]

Continue reading

A Single Query with Many Filter Combinations

Oren Nakdimon 10 Replies

Let’s assume the EMPLOYEES table (from the HR schema) contains many records, and we want to write an (efficient) SQL query that filters it by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME. For example: in one execution we may want to get all the employees with DEPARTMENT_ID = 80 in […]

Continue reading