Obsessed with Oracle PL/SQL

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

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...

This question was posted today on the OTN SQL-PL/SQL Forum: Is there a way, after compilation, to detect what level of optimization was used? Perhaps you have something already in production, and you just want to find some candidates for recompile (but not necessarily any changes in the source) with increased optimization level. The answer is: Yes! Just run a query against the ALL_PLSQL_OBJECT_SETTINGS data dictionary view (or the USER_PLSQL_OBJECT_SETTINGS, to see information only about program units you own). For a given schema and object name, the following information is provided through this view: So suppose you need to identify any program units that may have been accidentally compiled with an optimization level below 2 (the default, aggressive optimization, which can only be improved upon by level 3, which turns on inlining of subprograms globally). No problem! SELECT * FROM user_plsql_object_settings p WHERE p.plsql_optimize_level < 2 Or how about: for w...

FORALL is used to avoid row-by-row execution of the same DML statement (differing only in the values bound into it), thereby reducing context switching between the PL/SQL and SQL engines. I will assume in this post that you have a basic working knowledge of BULK COLLECT and FORALL. If not, check out: A Checklist for Conversion to Bulk Processing As noted in the checklist, you need to document your current transaction behavior, and then make sure that the same behavior manifests in your bulk implementation. I published a video that takes you through the key steps in the conversion from row-by-row to bulk. The code for this video is available on LiveSQL . A viewer asked: Hi Steven - @minute 21.56 in the video, function update_employee. in case of any update failures, you are handling an exception "bulk_error" but how do you ensure that the corresponding insert gets rolled back? You showed a way to communicate the failed employee records from insert_history function...

PL/SQL is a database programming language. This means that your source code is compiled into  and executed from within the Oracle Database. There are many fantastic consequences of this fact, many of which are explored in Bryn Llewellyn's Why Use PL/SQL? whitepaper. But this also can mean that developers see the database as the natural repository for the original source code , and this is a bad mistake to make. It's not the sort of mistake any JavaScript or Java or php developer would ever make, because that code is not compiled into the database (well, you can  compile Java into the database, but that's not where 99.99% of all Java code lives). But it's a mistake that apparently too many Oracle Database developers make. So here's the bottom line: Store each PL/SQL program unit in its own file . Use a source code control system to manage those files. Compile them into the database as needed for development and testing. In other words: you should never kee...

Back in June 2016, we announced "early adaptor" access to the Oracle Dev Gym, a new skin on top of the PL/SQL Challenge quiz platform. Since then, we've gotten lots of great feedback, and lots of usage on the site. Players have set up 543 monthly goals, with over 2,200 workout exercises (with 1,200+ actually completed :-) ). Plus more than 4,500 quizzes have been answered at the Dev Gym, vs. the "traditional" PL/SQL Challenge. All good news. When I held our first live Dev Gym workout with the Chicago Oracle User Group two weeks ago, however, I did get some very critical reviews of the quiz-taking experience. Basically, we are using too much real estate for players to easily see the code in the question and the various multiple choices, some of which can be very long in and of themselves. So we went back to the drawing board and made a number of changes, which we rolled into production today. To summarize: The left sidebar is closed by default ...

Most of the time that an exception is raised in your application, Oracle Database will do the raising. That is, some kind of problem has occurred during the execution of your code. You have no control over that; once the exception has been raised, all you can do is handle the exception - or let it "escape" unhandled to the host environment. You can, however, raise exceptions yourself in your own code. Why would you want to do this? Because not every error in an application is the result of a failure of internal processing in the Oracle database. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong. PL/SQL offers two ways for you to raise an exception: The RAISE statement The RAISE_APPLICATION_ERROR built-in procedure Of course, you could also force the raising of an exception by Oracle, with code like this:...