Obsessed with Oracle PL/SQL

Write code for a living? Feeling kind of lazy? Then maybe you should find a way to generate  some code. That's the focus of this blog post. The bulk processing feature of PL/SQL was introduced a long, long time ago. It lets us, among other things, do this: Lovely, concise syntax. One downside, however, is that the target collection in the BULK COLLECT INTO clause must be indexed by integer (which means all nested tables and varrays, but only INDEX BY - associative array - collections that are index by PLS_INTEGER or variations therein). This means that if you do want to use a string-indexed collection, you need to first "dump" it into an integer-indexed array, and then move it over to a string-indexed array. I've done this, and my code usually looks like this: DECLARE CURSOR c IS select last_name, first_name, employee_id from employees where department_id = 30; TYPE t IS TABLE OF c%ROWTYPE; c_limit CONSTANT PLS_INTEGER := 10...

My post on Conditional Logic in PL/SQL generated some interesting discussion concerning whether to use an implicit cursor (SELECT-INTO) or an explicit cursor (OPEN-FETCH-CLOSE) when fetching a single row of data. "Hmmm," you might be saying to yourself, "a conversation about SQL queries in a post on conditional logic? That's strange." Ah, but maybe not so strange. Because as one person put it in the comments: Yes, this is true. You can use exception handling as a kind of conditional branching logic. But should you? Generally, no. It's a good idea to keep application logic out of exception sections. Developers don't expect or look for business logic in those sections. They are, after all, for dealing with exceptions - errors. So if you put lots of code in EXCEPTION WHEN clauses, they can be easily overlooked, making your code harder to debug and enhance. But that question - and the recommendation to avoid the exception section - then led to ...