I was tasked with modifying some triggers to remove hard coding and use a lookup table. This seemed like a trivial task. However I ran into a number of interesting problems. One of the triggers would not compile. It kept giving me a PLS-00405 error. The text of this error is that a subquery is not allowed in this context.Here is what I was trying to do. There was a test in a large IF statement which checked whether a certain column was in a list. Previously the list was hard coded. So I just replaced that list with a SELECT from my new lookup table. I figured if you could choose from a list, you could just as easily choose from the results of a SELECT statement.
It turns out I was wrong. You cannot do a subquery like that in an IF statement. You need to break the SELECT out into its own statement, storing the result in a temporary variable. Then you can use this temporary variable in you IF clause. Go figure.
A few weeks ago I went through a week long instructor led training course on PL/SQL. I know we went over triggers. However I don’t recall this detail being mentioned. Sometimes you can only learn a language’s nuances when you are deep in the trenches doing real work. I am glad that my current project gives me plenty of these opportunities.
