Obsessed with Oracle PL/SQL

Connor McDonald offered a handy reminder of the ability to declare cursors at the package level in his Public / private cursors blog post. I offer this follow-on to make sure you are aware of the use cases for doing this, and also the different behavior you will see for package-level cursors. Generally, first of all, let's remind ourselves of the difference between items declared at the package level ("global") and those declared within subprograms of the package ("local"). Items declared locally are automatically cleaned up ("closed" and memory released) when the block terminates.   Items declared globally are kept "open" (memory allocated, state preserved) until the session terminates. Here's are two LiveSQL scripts that demonstrates the difference for a numeric and string variable (not that the types matter). Global vs Local variable  (community contribution) Global (Package-level) vs Local (Declared in block) Variables ...

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 ...

This question just came in via Twitter DM, so I thought it could do with a blog answer. Ref cursor types are the datatypes from which we declare cursor variables. A cursor variable is, well, just that: a variable pointing back to a cursor/result set. Some really nice aspects of cursor variables: you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL);  you can pass the cursor variable as a parameter or function RETURN value. Specifically: you can pass a cursor variable back to a host language like Java for consumption. Check out the cursor variable documentation .  Explore this LiveSQL script on cursors, including multiple examples of cursor variables. OK, to get to it, then: Before you can declare a cursor variable, you need to have a ref cursor type defined. There are two, ahem, types of types: strong and weak.  With a strong type, you include a RETURN clause that specifies the number and datatyp...