Obsessed with Oracle PL/SQL

"Persistent code base"? What's that? Well, I suppose I could have said "aging" or "legacy" code base, but that doesn't capture my point, which starts with: If I were a Javascript programmer, I would probably be excited if my code lasted more than a year . As an Oracle Database developer, though, it is not unreasonable to expect that my code will be in production for years, perhaps decades . Now, that's persistent code. And why is that? Because, well, DATABASE . The database is the repository for your enterprise. Certainly it contains the data, and if you fully leverage the database properly , it will also contain your business logic. And while it is not all that painfully disruptive to rewrite your UI code, it can be business-threatening to do any of the following: Switch your database technology Upgrade your database software too quickly Rewrite your business logic in a new scripting language  So your database...

Probably once a month, I receive an email from a programmer who either wants to tune up their Oracle Database programming skills (SQL and PL/SQL, primarily) or wants to help me or the community in some way. Here's the latest, via LinkedIn Messages: My friends and I are just in love with Oracle PL/SQL and the whole concept of database development and database design. This is like a religion for us. Can you you provide us some work to do? I don't have a handy set of projects for people to work on, especially now that I am back with Oracle. But there are plenty of opportunities for you out there, especially if you'd like to help build community tools. So here's a short list. I hope readers will offer others via the Comments section. 1. OraOpenSource - github.com/oraopensource A project of oraopensource.com, led by Martin Giffy D'Souza, this repo offers an ever-growing set of utilities, with lots of room for expansion. 2. utPLSQL - github.com/utpls...

Building a script for an upcoming PL/SQL Challenge quiz, I wrote a nested procedure as follows: PROCEDURE show_cursor_status IS BEGIN IF all_in_one_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('all_in_one_cur is still open'); ELSE DBMS_OUTPUT.put_line ('all_in_one_cur is closed'); END IF; IF department_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('department_cur is still open'); ELSE DBMS_OUTPUT.put_line ('department_cur is closed'); END IF; IF employee_cur%ISOPEN THEN DBMS_OUTPUT.put_line ('employee_cur is still open'); ELSE DBMS_OUTPUT.put_line ('employee_cur is closed'); END IF; END; Nothing wrong with that, of course. Works just fine. But there's a lot of repetition. I hate that. I like to normalize my code. And when the repetitive code is based on an IF statement, I immediately think of CASE - exp...

Received this via email today: I have just found the PL/SQL Challenge and I am eager to get started. Under Instructions on the Quiz Introduction page is something to which I would like to call your attention.   "For most quizzes, however, it is possible that more than one choice is correct, that all choices are correct, or that none of the choices are correct. You must check at least one box from those offered before you can submit your answer."   How might one check a box and then submit if none of the choices are correct? On first reading, I groaned. Really? Did I really leave a big hole like that in our quiz-taking process or - not quite as alarming - in my text? Is that a contradiction? No! No! No! There is an explanation which leaves me firmly on the right side of Logic. Can you see what it must be?  I will post this first as a small logic puzzle via Twitter, then I will update this post in a day or two with my answer. A day goes by. Another day g...

You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything . Ha. Not so. :-) Of course, there are always the new features, such as those coming in 12.2 (I will be blogging about those extensively in the coming months). But even for features that have been in the language for decades, I still encounter small gaps in my knowledge. For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement. What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post: Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well. You can see this behavior in action in the script below, which is generated from a recent PL/SQL Challenge quiz  and can be run direct...

We've been offering quizzes on the PL/SQL Challenge (and now the new Oracle Dev Gym - still in an "early adaptor" user testing phase) since April 2010. We've covered hundreds of topics in PL/SQL, and hundreds more in SQL. Most quizzes are multiple choice, and one of my favorite question style is to ask: what code in my program unit is unnecessary? By "unnecessary" we mean that the code can be removed without affecting the behavior of the program unit. There can be two reasons, roughly, for a chunk of code to be unnecessary: 1. The code "reinforces" or explicitly defines default behavior. If you remove it, the default comes into play. So no harm done, but it is often beneficial to be explicit. 2. You misunderstand how the language works and therefore write code that should not be there at all, and is likely to cause maintenance issues later (and maybe even lead to bugs). I offer an exercise below in identifying unnecessary code. See if you...

I recently (well, OK, not all that recently) received this request: Our Java developers think that the work can be done faster with Hibernate by basically eliminating the "middle-men" and "middle-women" (ie. the database developers) for what they think is the simpler database access tasks for basic forms and reports.  It is true that Java developer out number PL/SQL developers by about 10:1 at my company - so we do/would have a lot of work to do.   But I've talked with the other PL/SQL developers and while we are all rather busy. there are almost no cases of PL/SQL tasks needing to rollover from one sprint to the next (meaning, or course, that a PL/SQL task didn't get done on time).  We want to make sure that our company continues to use PL/SQL for the database access layer in our application. Can you help? Hibernate offers an ORM (Object Relational Mapping) tool: "Hibernate ORM enables developers to more easily write applications whose data o...