Steven Feuerstein's Blog

Binding sparse arrays in a FORALL statement

Steven Feuerstein — Mon, 23 Nov 2009 15:44:00 GMT

FORALL was introduced into PL/SQL in Version 8i. It is a fantastic feature; you should use it in place of all loops that contain DML statements performing row-by-row processing. You will generally see performance improvements of at least an order of magnitude.

If you are not familiar with FORALL, make it a priority to get familiar. You can start with my brand new 5^th edition of Oracle PL/SQL Programming, complemented by the Oracle documentation. Follow up with a perusal of my 21^st Century PL/SQL course materials.

I assume a working knowledge of FORALL for the remainder of this post.

The typical (and, in Oracle8i and Oracle9i, the only) way to construct a FORALL statement is to use a header that is very similar to a numeric FOR loop. Here is an example:

DECLARE
   TYPE namelist_t IS TABLE OF employees.first_name%TYPE;

   l_enames   namelist_t := namelist_t ('ABC', 'DEF', 'SMITHIE');
BEGIN
   FORALL indx IN 1 .. l_enames.COUNT
      UPDATE employees
         SET first_name = l_enames (indx);
END;

More generally, the FORALL header looks like this:

FORALL integer_index IN low_value .. high_value
where integer_index is an implicitly declared integer iterator, low_value is the low end of the integer range and high_value is the high end. Each integer between low and high must reference a defined index value in any of the collections that are bound into the FORALL's DML statement (in the above case, there is just one: l_enames).

In other words, the binding array must be densely filled between the low and high values.

If the collection is sparse and the FORALL statement tries to read an element at an undefined element, it will raise an exception as shown below:

Notice that it does not raise NO_DATA_FOUND. I point this out because it seems like Oracle goes out of its way to raise this exception whenever the error bears even the remotest resemblance to "I tried to get something and it wasn't there" (SELECT INTO, read an element in a collection at an undefined index value, read past the end of a file).

Notice also that even if I include the SAVE EXCEPTIONS clause in my FORALL statement, this exception is not "saved" to the SQL%BULK_EXCEPTIONS pseudo-collection. That's because this is a "meta-error" for the FORALL statement. The exception is not raised by the SQL engine as it performs the DML statement. It is, instead, raised by the PL/SQL engine before the SQL statements are passed along to the SQL engine.

So what's a programmer to do if your binding array is not densely-filled?

In Oracle10g Release 2, Oracle added support for two new ways to construct the FORALL header: INDICES OF and VALUES OF. With either of these, your FORALL header no longer looks like a numeric FOR loop. Instead you specify that you want the FORALL statement to reference only those elements in the binding array whose index value is defined in the INDICES OF array.

The simplest form uses the same collection as the binding and INDICES OF arrays, as in:

   FORALL indx IN INDICES OF l_enames
      UPDATE employees SET first_name = l_enames (indx);

Now, an update statement will be sent to the PL/SQL engine only for this index values that are defined in the collection.

But the collection referenced in the INDICES OF clause need not be the same as the binding array. Here is an example of using INDICES OF with a distinct collection:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices   boolean_aat;
BEGIN
   l_employees (1) := 137;
   l_employees (100) := 126;
   l_employees (500) := 147;
   
   l_employee_indices (1) := FALSE;
   l_employee_indices (500) := TRUE;
   l_employee_indices (799) := NULL;

   FORALL l_index IN INDICES OF l_employee_indices 
                  BETWEEN 1 AND 500
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;

Updates will be run only for employee IDs 137 and 147.

The VALUES_OF clause offers an additional layer of "indirection." With VALUES_OF, the FORALL statement executes a DML statement for each index value in the binding array that is an element in (not an index value in) the VALUES OF collection.

Here is an example:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE indices_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   l_employee_indices   indices_aat;
BEGIN
   l_employees (-77) := 134;
   l_employees (13067) := 123;
   l_employees (99999999) := 147;
   l_employees (1070) := 429;
   
   l_employee_indices (100) := -77;
   l_employee_indices (200) := 13067;
   l_employee_indices (300) := 1070;
   
   FORALL l_index IN VALUES OF l_employee_indices
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;

In this block of code, an UPDATE is executed for employee IDs134, 123 and 429.

So if you are using FORALL but running into situations where your binding array may not be sequentially filled, give INDICES OF or VALUES_OF a try. It might simplify your life and code dramatically.

The Subtleties of Programming

Steven Feuerstein — Mon, 16 Nov 2009 15:54:00 GMT

I recently published the following puzzle in the ToadWorld newsletter:

Which of the following queries return the names of programs (without duplication) defined in the currently connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

A.   SELECT name FROM USER_DEPENDENCIES
WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'

B.   SELECT name from ALL_SOURCE
WHERE owner = USER
AND name = 'DBMS_OUTPUT.PUT_LINE'

C.   SELECT DISTINCT name from ALL_SOURCE
    WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

D.   SELECT DISTINCT name from USER_SOURCE
WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0

I believed that (D) was correct. Certainly, A, B and C do not do the trick (click here to see my explanation). But I recently received the following email from Wim de Lange of the Netherlands:

"None of the answers below is correct. (D) could be the solution, but DBMS_OUTPUT.PUT_LINE could be spelled as DBMS_OUTPUT . PUT_LINE (spaces around the dot) and that code will still compile and run. So the spaces are destroying the solution here."

Wim is absolutely correct. You could put spaces between the package name, dot and subprogram name, and the PL/SQL compiler will have no trouble with that code.

Thanks, Wim, for your very close reading of my puzzle, and for reminding me that programming can be a very subtle and nuanced craft. Oh, and also that it is so difficult to come up with all the test cases for a program.

And just to show you that I am not the least bit upset about someone finding an error in my code and writing, Wim is hereby declared another winner of that contest and will receive a Toad World t-shirt in addition to the randomly selected four winners.

The Most Important Language to Learn

Steven Feuerstein — Mon, 02 Nov 2009 13:54:00 GMT

I was recently in Germany, and did an interview with Erik Franz of database|pro. One of the questions he asked me was: "Which programming language would you recommend a college student learn to give them the strongest chance at a good job upon graduation?" I would have loved to be able to answer: "Learn PL/SQL!

An Amazing Visit to Mexico City

Steven Feuerstein — Fri, 23 Oct 2009 12:38:00 GMT

As I write this, I am flying back to Chicago now, 9 PM Oct 21, from Mexico City, after a very nice two day visit.

Quest Mexico asked me to come down to help celebrate the release of Toad Version 10. I said "OK."

And, wow, they did a really fantastic job of making me feel appreciated. Besides putting me up in a fabulous, old but very excellent hotel, El Camino Real Polenco, and making sure I enjoyed outstanding meals of (surprise) Mexican cuisine, they pulled together an enormous gathering of over 250 Oracle technologists for an evening presentation at the beautiful http://www.haciendadelosmorales.com/index.php by Claudia Fernandez, Quest product manager supreme, to talk about Toad 10, and me to talk about, well, pretty much whatever I felt like talking about, which turned out to be the function result cache feature of Oracle11g.

Quest Mexico also ordered 100 of my Oracle PL/SQL Best Practices books to give away to the first 100 people who arrived on the 20th. That turned out to be a fine motivator. Developers started arriving at 2 PM or so for a 5 PM start time. Unfortunately, due to problems with Customs in Mexico, we didn't have those orange books. Fortunately, we had a nice big pile of a book that Claudia co-authored, Database Benchmarking. So attendees received those. And then they lined up for autographs. As I signed books, I got to meet a lot of very enthusiastic PL/SQL developers - several of which said that they benefited greatly from my writing. How satisfying!

On the wall behind our signing table was a banner with a reproduction of my head that must have been five feet high. I kid you not - in fact, you can see it here. Fortunately, I am a humble fellow, so I only posed in front of the banner for a few photos.

Many attendees also asked to have their photo taken with me. And Ricardo of Quest was kind enough to use my camera to also capture those moments. You can view all those photos here.

I must tell you that years ago, when people first asked for autographs, asked to have their picture taken with me, I was a little bit embarrassed by the whole situation. C'mon, I just wrote a few (well, 10) books about this one language, PL/SQL. Why are they getting so excited? They are being weird, ridiculous. But I eventually came to realize that it really doesn't matter what I think. What matters is that for whatever reason (I have some idea of why this happened, but it is still a bit of a mystery to me) my books, my writings, my trainings have had a substantial impact on the lives of these developers. My ideas and my ways of expressing them have improved their lives.

Now, that realization is/was a continually humbling experience. Now, I am honored that anyone would care to have me sign their book or share a photo with them. And I have decided to start celebrating this honor and, at least this is my intention, honor my readers by posting on Flickr the photos we took together (and others from the event in La Hacienda Morales in Mexico City).

If anyone sees their photo on this page and does not want it to be available publicly, please let me know immediately and I will remove it. My apologies in advance for any liberties taken.

Many thanks to Diana, Belen, Daniel, Pablo, Ricardo and others at Quest Mexico for organizing a fantastic set of events (beyond just the public program), media interviews and overall stay. I look forward to a triumphant return, or perhaps a visit to Monterey.

Once again, I find myself marveling at my good fortune and the fine life I have. I only wish I was able to spend time with all my PL/SQL friends around the world and not be away from wife, Veva, and my boys. That is a substantial price to pay. Sigh....

Finally, I very much enjoyed having an opportunity to resurrect my rusty (not) command of the Spanish language. I am not sure whether it is a blessing or a curse, but they tell me that I am able to pronounce words in Spanish very well (roll those r's, put the accent on the correct syllable, etc.). This leads native speakers to believe that I can speak and understand much more than I actually can. So I start out strong, but immediately stumble over a serious lack of vocabulary, and very poor comprehension (I can talk better than I can understand - big surprise, I spend so much of my life talking at others).

Resolving an LTRIM mystery

Steven Feuerstein — Thu, 15 Oct 2009 13:42:00 GMT

Steven discusses how LTRIM works and an alternative solution for a user's problem.

Compare Nested Tables of Non-Scalar Datatypes

Steven Feuerstein — Mon, 12 Oct 2009 14:28:00 GMT

A little known, but very handy feature of PL/SQL is the ability to apply set operators, like union, intersect and minus, to nested tables.

PL/SQL on DB2 9.7 ?!

Steven Feuerstein — Wed, 16 Sep 2009 13:36:00 GMT

Check out this video. IBM claims to now provide native support for PL/SQL programs, including support for many built-in packages, collections, etc. Wow! Lots more details here, but you will find below some

I think this is wonderful news for the PL/SQL community. Not only can I rebrand all of my books for DB2 (as in "DB2 PL/SQL Programming" - wow!), but PL/SQL developers should have growing opportunities for jobs.

Online Training for the New Economy

Steven Feuerstein — Tue, 08 Sep 2009 18:23:00 GMT

When the going gets tough and budgets get tight, money for training is often cut first. You still, however, need to learn how to best leverage the PL/SQL language and write high quality code. To help you, Quest is offering a three-part, webcast training series that I will be teaching. Each session consists of two hours of in-depth coverage of a specific functionality area or best practice.

The cost is far less than would be for an onsite or public training - and I don't have to travel (and neither do you)!

More information about session logistics and registration are available through these links below. For much more detail on each session, please check out the descriptions at the end of this posting:

Webcast Details

Session 1: "Turbo-charge PL/SQL Execution with Bulk Processing"
Thursday, October 22, 2009 at 2 p.m. ET
Session 2: "Writing Maintainable PL/SQL Code"
Thursday, November 12, 2009, at 2 p.m. ET
Session 3: "Error Management Features of Oracle PL/SQL"
Thursday December 10, 2009, at 2:00 p.m. ET

I hope that you can take advantage of this opportunity. Click on the link below to:

Get Details and Register

Optimizing PL/SQL Execution with Bulk Processing

This session focuses on how to turbo-charge the performance of multi-row SQL statements executed from within a PL/SQL using FORALL and BULK COLLECT. These features have been around since Oracle8i, but many developers still don't use them or only scratch the surface of what is possible.

The Bulk Processing webinar will talk about why these features were needed and how they work. Steven will start with simple examples and syntax and drill down into all nuances, including the LIMIT clause for BULK COLLECT and the SAVE EXCEPTIONS, INDICES OF and VALUES OF clauses for FORALL.

After attending this webinar, you will be able to both create new programs built around these features and also redesign existing programs safely to improve performance.

Writing Maintainable PL/SQL Code

Software managers (and, therefore, their developers) are very focused on delivering applications "on time" (that is, to meet the current production deadline). Sure, that's important. But as (or maybe even more) important is to build applications so that they can be maintained and enhanced without consuming all the resources of the development team (thereby interfering with new development).

This webinar offers a wide-ranging set of techniques to make your code easy to understand and maintain over time. These techniques include: the use of subtypes and local modules; how to activate and check compliance with standards; and encapsulation (information hiding).

After attending this webinar, you will be able to write code that minimizes the amount of development resources needed to maintain that code.

Error Management Features of Oracle PL/SQL

Consistent, robust error management is a critical feature of any successful application. Developers need to know all that is possible in PL/SQL regarding the raising, handling and logging of errors, and that standardize the way in which those tasks are performed.

This webinar takes you beyond the basics of exception handling in PL/SQL to explore the wide range of specialized error management features in Oracle, plus recommendations for best practices for consistent, robust application construction.

We will cover FORALL's SAVE EXCEPTIONS, DML error logging with the DBMS_ERRLOG package, the AFTERSERVERERROR trigger, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, and more. In addition, we will talk about hiding error management functionality behind a PL/SQL packaged API and then deploying it as a set of services for application developers.

After attending this webinar, you will be able to fully leverage PL/SQL error management features and follow best practices for consistent error management across your entire development team.

Oracle Database 11g Release 2 Has Arrived!

Steven Feuerstein — Fri, 04 Sep 2009 22:25:00 GMT

What a big relief....I was in the beta program, checking out the new features of PL/SQL in this latest release of the Oracle database. That's quite a privilege, but also a burden; Oracle is very severe about not wanting us to talk about upcoming features before they officially release the product to their adoring public.

That's tough on me, because I have a hard time keeping my mouth shut about anything, especially wondrous new features in PL/SQL. So what I usually do is say something like this:

"It is possible, just possible that in the next release of Oracle you will be able to...."

And I leave it up to my audience to draw their own conclusions.

No longer. I can now disclose to you the new features for PL/SQL in Oracle Database 11g Release 2. Drum roll, please!

So what's new? Hmmm. Not much, in PL/SQL specifically.

Probably the most important thing to me won't mean much to most of you, because it is an improvement over an 11.1 feature – and who's upgraded to 11g already? Not many.

Still, I will tell you about it: when working with the Function Result Cache, you no longer need to include the RELIES_ON clause. Oracle will automatically determine what tables the cache is reliant on. When and if any changes are committed to that table, all dependent caches will be invalidated.

It's a nice improvement and it also means that for most of you, RELIES_ON will be completely ignored. Lucky you! This sort of "instant deprecation" reminds me of the RESTRICT_REFERENCES pragma that was required back in Oracle7 or 8 to be able to execute user-defined functions inside SQL statements. Now that was an enormous pain in the neck and in Oracle8i, it was no longer required.

Besides the enhancement to the Function Result Cache, we have the following changes in and additions to PL/SQL (I encourage you to download the 11.2 documentation library, even if you have no plans to upgrade to 11.2 anytime soon. Then you can review the PL/SQL Users Guide yourself!):

Surely, you are thinking, 11.2 consists of more than this. Certainly it does. The major new feature in 11.2 is "edition-based redefinition," which (according to the doc) " enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time."

This is REALLY BIG NEWS. You can now "hot patch" your application code, which means that Oracle is finally and truly a 24x7 database platform. This feature will have a big impact on application architects and some impact on PL/SQL developers, but there is actually very little change in the PL/SQL language itself.

And now for the biggest news of all: now that Oracle has officially released 11.2, O'Reilly can publish the 5^th edition of Oracle PL/SQL Programming. This edition has lots of good stuff in it, but most important are:

Optimize PL/SQL performance with the aid of a brand-new chapter in the fifth edition;
Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation from subtypes, and enhancements to native compilation, triggers, and dynamic SQL;
Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects.

[ By the way, I am considering making it possible to order a signed copy of OPP5 through my own website. If you are interested, please drop me a note. ]

Making Change Happen

Steven Feuerstein — Sun, 23 Aug 2009 00:18:00 GMT

[ It is very likely that if you are reading this you are a developer, but this is really geared towards software managers. So feel free to copy and paste into a Word doc and pass it along to your manager or team lead! ]

I very often give trainings and presentations on the topic of PL/SQL best practices. I offer lots of ideas for writing code that is easier to read and maintain, runs more efficiently, can be tested with less difficulty, and utilize more fully the best features of PL/SQL.

And all too often, the response I get to these ideas are: "They all sound great, but I know that when I go back to work, I'll just fall back into the same way I've been doing things. And even if I change, the rest of the group will still be stuck in the old ruts."

Well, that's depressing, isn't it?

When I was in Singapore last week, I gave one of those "Here's the right way to do everything" talks to a group of developers at a major financial services firm. Then I met with the Deputy Director of IT and he presented me that same challenge:

"How can I get my people to change the way they do things?"

So I decided to try to come up with an answer. Here's what I suggested to him:

Champions

Research Time

Team-building Reviews

Incentives

Selective Enforcement

Perhaps I should go into a bit more detail....

First of all, it is important to recognize that the biggest challenge to bringing about change is not making sure that developers know about the new features and the right way to do things. The technology is actually the easy part. The hard part is to change behavior. We are all creatures of habit, and we like our comfort zones. They're so comfortable.

Champions

For any new product (we were talking about Quest Code Tester, but it applies to anything) or major new feature, you should assign a person on your team to be the "champion" of that technology. They should become expert in the technology, be enthusiastic about what it can do for the team, and they should be ready, willing and able to mentor others.

Without a strong proponent of a new direction, you won't be able to build the momentum to ensure a solid, consistent, team-wide implementation. With a champion in place, everyone on the team knows who to go to when they get stuck. And since the role is well-defined and "public knowledge," there is no reluctance to approach the champion for help.

Research Time

I continue to be shocked at how many developers still do not seem to know about and use many important features added to PL/SQL in Oracle8i. Yes, Oracle8i. Things like: invoker rights, autonomous transactions, FORALL and BULK COLLECT. And then of course, there are the many new features of 9i and 10g, such as string-indexed collections, MULTISET operators, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, and more.

It's easy to understand why so many developers remain ignorant about such things and consequently under-utilize the language: they don't have the time (or feel like they have the freedom) to explore PL/SQL.

Managers usually ask just one or two questions of their developers: "When you will be done?" and "Why is your code so buggy?" Neither of those questions are conducive to expanding one's knowledge of a programming language.

I strongly urge managers and team leads to ask each developer to come up with a research plan: spend 4 hours every two weeks (5% of your time - more is better, but I don't want to sound crazy) exploring technologies that could be helpful to the project.

Insist that the developer not work directly on application code, but also that they put together a proposal for precisely what they want to study and why. They will feel great about the opportunity, and both the team and application will benefit in short order.

Team-building Reviews

Everyone knows that code review is a good idea. I would even go so far as to say that no critical code should go into production until at least one other human being (besides the one who wrote it) looks it over and makes sure it isn't totally crazy (that is, unreadable spaghetti code). Often the form of such a review is "one on one" - the team lead looks over the good and gives private feedback to the developer.

That's not what I'm talking about here (though it's a fine thing to do!). Rather (or in addition), I propose that once a month, the entire team gets together to critique, as group, a piece of code. The aim of the review is not so much to find flaws in code, but to share knowledge about the application and elevate the whole team's awareness of how best to use PL/SQL.

Whoever is the senior developer on the team should take the lead; you can't expect junior programmers to be willing to subject themselves to potential humiliation (at least not until they see there is nothing to be afraid of). The best developer should set an example: everyone's code can be improved; it's OK to make mistakes; we can all learn from one another.

Incentives

What's the best way to motivate a developer: carrot (incentive) or stick (punishment)? We all know the answer, and not just for developers: we aren't motivated to change for the better with a threat of punishment (though we may still change because we have no choice). I think the world would be a far better place if without coercion.

And certainly when it comes to getting developers to create higher quality software, well, it's really important to make that they want to do. So....let's use incentives!

Managers should identify ways to measure objectively progress in important areas, and then give out rewards (both recognition/status and goodies, like gift certificates to restaurants, Amazon.com, etc.) to those who are high achievers in those areas.

Here are some ideas for possible metrics:

Reusable code: we don't want to reinvent the wheel, but sometimes it's hard to find and reuse code. Perhaps prizes will help. You can run searches through the code base to find all external (not in the same package) program calls, for one example. And I suggest you give a reward for (a) the person writes the most reusable code - usually a senior developer, and (b) the person who uses the most reusable code. That way even a junior developer has a chance to win and is very much incented to learn from the use the work of others.
Naming standards: we usually have some set of naming standards, but how do you check to see if people are following them? Consider installing Oracle Database 11g on a workstation, load up your application code, and use PL/Scope to analyzer variable declarations. For an example of a query that demonstrates this powerful feature, check out the AMIS blog, and search for "PL/Scope".
Unit tests (especially automated tests): I would give out a really substantial reward for a developer who does the most automated testing of their code, building and putting in place regression tests. This will have the biggest impact on the quality and success of your applications, so get people motivated to do it! It will greatly help to provide a tool to make it easy(ier) to automate the process. Check out Quest Code Tester for Oracle, utPLSQL, dbFit, PLUTO or PL/Unit.

Selective Enforcement

No doubt about it: carrots are the way to go. But that doesn't mean that you (a manager or team lead) shouldn't be ready to bring out the stick now and then.

Well, that's probably a bad way to put it. I am not making an argument for punishing developers if they don't do things a certain way. I do believe, however, that a manager should in at least some cases, insist and enforce certain critical changes in behavior. Some things are just too important to leave up to developer preference and hopes for good behavior.

The following areas come to mind:

Testing (yes, that again): let's face it. Our applications have too many bugs in them. That's not only a big drag for our users, but it makes our live miserable, too. Rather than focus on building new applications or adding interesting functionality, we spend way too much of our time putting out fires. We are stuck in a vicious cycle: no time to write better code, because we have too many bugs to fix. The only way out is better and more automated testing. Managers should find tools to help make this a reality (see list in previous section) and they should insist that developers write repeatable, automated tests.
Version control: it seems obvious, but I still run into development shops that do not have VC systems in place. Sometimes I even encounter developers who do not maintain their source code in files, but simply edit and compile right out of the database. Totally insane! That's a sure recipe for losing work and creating terrible frustration on your team.

I could list other ideas, but I don't want to end this note with too much of heavy hand. And managers should definitely not go overboard with putting lots of new requirements in place at once. Phase them in, and make sure that you provide support (tools, champions, training time) to ensure success.

No Guarantees But It Won't Hurt to Try!

Maybe none of these ideas will make a difference in your team, but surely it's worth trying some new directions to see what if anything will stick.

I have no doubt that developers will be excited to see their manager trying new approaches, especially ones that don't simply mean more work and longer hours.

Watch Out for Data Caching!

Steven Feuerstein — Mon, 03 Aug 2009 18:31:00 GMT

I just fixed a bug in Quest Code Tester for Oracle, and I thought I would share my experience with you.

The bug was reported by our most excellent QA tester, Danny Pham. He is not only good at going through the defined test cases to verify correct behavior. He also takes devilish delight in trying all sorts of "crazy" stuff to see what happens.

So Danny encountered this error after he set up a test case with three outcomes, all built on cursor variable expression tests (something that is very unlikely for a customer to have done):

    PL/SQL: numeric or value error: NULL index table key value
  ORA-06512: at "QCTO.QU_TEMPLATE_XP", line 1523
  ORA-06512: at "QCTO.QU_TEMPLATE_XP", line 1570
  ORA-06512: at "QCTO.QU_GENERATE", line 6062
  ORA-06512: at "QCTO.QU_GENERATE", line 6138

I did some tracing, digging around, etc. and I concluded that this is very quirky behavior, likely specific to this unlikely scenario, and I was just about to write him an email suggesting that this one will not be easy to fix, is not a high priority bug to fix (we are in final QA testing - only able to fix "P0" bugs - the product is considered broken if a P0 is not addressed), and we should leave it for next version.

But then I thought: "Aw, heck, let's try to reproduce the problem with a simpler, more likely scenario." And, lo and behold, and much to my stunned disappointment, it was very easy to replicate the error with a much more likely scenario.

That left me with no choice: MUST FIX BUG. And it was very odd behavior, too. The error occurred when I first defined the test and any time I tried to run it in that session. But when I exported and imported the test definition, the bug went away, and I could run the test just fine.

After a little bit more analysis, I also found that if I closed Code Tester and reopened it, the bug would disappear. How could this be? What was different?

Well, any time a bug's behavior changes with a new connection, you should think about what was cached in the session, either by Oracle or by the application. That led me to remember that I had implemented some caching of key data in the Code Tester backend to improve performance when generating and running tests.

I cache the data when validating the test definition, and then reuse that same cached data when generating the test code and finally when running the test (at least in those situations where I need to generate some "just in time" code elements).

Caching is fine and wonderful - especially Oracle 11g's new function result cache, which is a thing of great beauty - but you have to make sure that the underlying data hasn't changed!

So maybe that was the problem...something had changed. But why would it change? So I opened up one of my program files and did some searching for DML operations. And I found a point in the validation phase when I go back and make a change to the underlying data (actually, I knew about this step and had studied it, but hadn't connected it to the caching issue).

Now, you could argue (and I did so to myself) that validation is not the place for data changing. You could also argue that there is no time to refactor this convoluted piece of code. You could, finally, argue that you should put it on your to do list to get back to this piece of code and clean it up.

I put the following on my to do list: "Get back to this piece of code and clean it up."

In the meantime, however, I put in a call to invalidate the cache and force new retrieval of data, immediately after the change I made to the qu_attributes table.

And then the bug disappeared. Ah...so nice.

Lessons learned (or of which I was reminded):

Don't be hasty in drawing conclusions about the causes of bugs, especially if your conclusion seems anything like a rationalization for not doing something you know you should do.
Be very, very, very careful when caching data. It's a powerful performance optimization technique, but it can lead to dirty data and bugs that are hard to identify and fix.

Must Know Features of PL/SQL

Steven Feuerstein — Wed, 22 Jul 2009 14:50:00 GMT

I spend way too much of my life on airplanes zipping around to different places, doing presentations and trainings on PL/SQL. In the process, I meet many PL/SQL developers – and I continue to be surprised and disappointed at how many developers do not know about or at least do not seem to take advantage of very powerful and useful features of PL/SQL. So I have to decided to list below all those elements of PL/SQL that I consider to be "must know." In other words, if you are not familiar with the following features, and not utilizing them, then your PL/SQL programs likely leave lots to be desired from the standpoints of efficiency and maintainability.

In the table below, you will find on the left the PL/SQL keywords that identify the feature and on the right a brief description and link to the Oracle documentation on this feature. Of course, you can also download my trainings at PL/SQL Obsession and learn about these features – oh, and don't forget to buy my books. J

Collections	Associative arrays, nested tables, varrays: if these terms mean little to you, then you are missing out on some of the most important functionality in PL/SQL. The collection is PL/SQL's version of an array, and they can help you in so many different ways. >More info
FORALL	Bulk processing of DML statements. This is the most important new feature in PL/SQL since Oracle8i was released. Anytime you execute an insert, update, delete or merge inside a loop of some sort (multi-row DML), you should convert this looping operation to a FORALL statement. You will see an amazing improvement in performance...and you need to use collections to leverage FORALL. Don't forget to also check out these nuances of FORALL: SAVE EXCEPTIONS, INDICES OF, VALUES OF. >More Info
BULK COLLECT	Bulk retrieval of data. Second only to FORALL in importance, use BULK COLLECT with both implicit and explicit cursors to drastically speed up the performance of data retrieval. Don't forget to use the LIMIT clause to manage the amount of PGA memory consumed by your program. As with FORALL, you need to use collections to leverage BULK COLLECT. >More Info
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE	Answers the question "On what line number was the most recent exception raised?" Available in Oracle Database 10g Release 2 and above, this function should be called whenever you trap and log an error. >More Info
DBMS_UTILITY.FORMAT_CALL_STACK	Answers the question "How did I get here?" In other words, it shows you the execution call stack (A called B called C). Very useful information when logging errors or tracing application execution. >More Info
DBMS_UTILITY.FORMAT_ERROR_STACK	Returns the current Oracle error message (sometimes as a single string, and sometimes as a stack of messages, if the exception has been re-raised one or more times). Oracle recommends you use this instead of SQLERRM, which may truncate your error message. >More Info
PRAGMA AUTONOMOUS_TRANSACTION;	This pragma defines your procedure or function as an autonomous transaction, which means that any DML operations performed within this subprogram are saved or rolled back without affecting any other uncommitted changes in your session. Very handy when logging errors or tracing, also useful in a variety of other circumstances. >More Info
AUTHID CURRENT_USER	When you include this clause in the header of a program unit, then when the program is run, the privileges of the current user or invoker of the program are used to resolve references to table and view names. This is called invoker rights and is in contrast to definer rights, the default (or AUTHID DEFINER), in which the privileges of the owner of the program determine at compile time how references to database objects are resolved. Invoker rights comes in very handy when you need the same code to work against different tables, depending on who is calling the program. >More Info
SUBTYPE	The SUBTYPE keyword allows you to define your own, application-specific datatypes. It is a trivial feature to learn and use, but a very powerful one from the standpoint of writing maintainable code. Use packaged-based subtypes to hide the actual datatype definition, such as VARCHAR2(500). That way, if you ever need to change the datatype (for example, to make the maximum size of the string larger), you can change it one place and all the variables that are declared using the subtype will use this new definition after recompilation. >More Info
RESULT_CACHE	New to Oracle Database 11g, you can specify that a function is a result cache. This means that Oracle will cache the inputs and return values of the function in a new SGA cache, and share this data across all sessions in the instance. It can provide a significant boost in query performance for any tables that are queried more frequently than they are updated. Sure, you are not using 11g yet, but you will. And you can get ready to take advantage of the result cache by hiding your queries inside functions now. >More Info
DBMS_ERRLOG	Introduced in Oracle Database 10g Release 2, this package and the associated LOG ERRORS clause, allows you to suppress the raising of exceptions in for DML statements, instead writing error information to a log table. This feature is very handy when performing large numbers of DML operations (ie, batch loads) from within PL/SQL and you need to continue past any exceptions raised. You can also use this within a FORALL statement. >More Info

Avoiding repetitive coding for repetitive column structures

Steven Feuerstein — Wed, 15 Jul 2009 13:10:00 GMT

Steven proposes a solution to a customer's question about using a 2 dimensional array in PL/SQL.

Oracle PL/SQL Programming 5th Edition - "Early Warning"

Steven Feuerstein — Wed, 10 Jun 2009 18:19:00 GMT

I was on the phone with a Quest Code Tester user the other day and he asked me if I was working on the next edition of my original "magnum opus", Oracle PL/SQL Programming, which would include Oracle11g features.

I cannot describe to you how good it felt to say: "No, I'm not working on it. I'm finished working on it."

In fact, just last week, my editor (Debby Russell) sent all the chapters to the production department, so that they could begin the long process of converting Word docs into an actual, publishable book.

This fifth edition will cover all features of Oracle11g, through Release 2 - which means it will not be published until the fall, probably at the same time as Oracle Open World (week of October 12), or whenever Oracle formally announces availability of Release 2.

We have also added a single, very extensive chapter on optimizing performance of PL/SQL code.

You can see more information about the book and even pre-order the book at:

http://oreilly.com/catalog/9780596514464/#top

But you may want to hold off a little bit on that pre-order. I am considering offering the ability to order the book directly from me, and have it signed whatever personalized message you request. So....you will hopefully see an update about that soon!

Naming Conventions and Coding Standards, the Feuerstein Way

Steven Feuerstein — Thu, 28 May 2009 17:32:00 GMT

I am often asked about the naming conventions and coding standards that I use. My answer is usually a combination of muttering and vague statements and hand-waving.

That's because I have a confession: I don't have a single naming conventions and coding standards document that I use. Why not? Because I am a software developer! That is, I feel (well, I am) very busy, overwhelmed by deadlines. I don't feel like I have the time to stop and write down the few rules that I might actually follow more of less consistently. And it seems that a part of me is somewhat resistant to being accountable to a standard. Sound familiar?

Sigh...unfortunately (no, make that very fortunately), lots of other PL/SQL developers look to me for advice about how to "do things right." Ah, the pressure, the pressure!

Ah, the hypocrisy. That's what it really comes down to: I am a big hypocrite. I routinely violate just about every best practice I push on others. I sound high and mighty, but when I write code, I feel the same pull to do the quick and dirty.

So I've decided to take a few moments out of my various plane rides (I am now - when I started this document, anyway - flying from Manchester, UK, to Sweden to do a presentation to the Stockholm OUG, then a couple of days of customer visits in Stockholm and Milan, then two days of training for Oracle in Helsinki) to jot down what have become my de facto coding standards.

You see, I do have standards, and I even follow them, more or less. I just don't like to bother writing them down.

So that's what (finally) you'll find here. It's a combination of the naming conventions I follow, certain naming conventions with which I disagree, and a variety of other suggestions. I hope you find it useful. I'm not going to make much of an effort to justify my approach or explain away the inconsistencies. I'll just show you what I do and you can decide for yourself if you'd like to use my path as a model for your own journey.

I also hereby declare this to be a work in progress. So...

If you have disagreements with my approach, let me know. Perhaps I will agree with you, and change my document (you will get the credit).
If you have a suggestion for something to add to the document, send it to me. If I like it, I will put it into the document (and give you credit).
If you would like to offer your own set of naming conventions and coding standards to the world of PL/SQL developers, I will add it to the webpage.

The URL for my standards document is: www.ToadWorld.com/SF/standards

A Classic Error in My Monthly Puzzle!

Steven Feuerstein — Mon, 27 Apr 2009 15:47:00 GMT

Every month, a Toad World newsletter goes out to thousands of Toad users and it includes a monthly puzzle that I write. Last month's puzzle went like this:

Which of the following blocks does not contain an infinite loop?
A.
DECLARE
   l_line VARCHAR2(32767);
   l_file UTL_FILE.file_type :=
      UTL_FILE.fopen ('C:\temp', 'my_file.txt', 'R');
BEGIN
   LOOP
      UTL_FILE.get_line (l_file, l_line);
   END LOOP;
END;

B.
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
   END LOOP;
END;

C.
DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

D.
BEGIN
   <>
   DBMS_OUTPUT.PUT_LINE ('abc');
   GOTO start_of_block;
END;

I told my friends at Toad World that the answer was (A), because UTL_FILE.GET_LINE raises the NO_DATA_FOUND exception when you read past the end of the file. Since files cannot be of infinite size, you will eventually read to the end of the file and then the loop will terminate.

And I wiped my hands of that puzzle. Unfortunately, it came back to haunt me in the form of a message from the Toad World editor-in-chief:

"Steven, normally I don't worry when I get just a few responses to the puzzler with answers different from what you’ve told us is the correct one, but this month I’m wondering if we missed something because we’ve had close to 50% of the respondents picking the wrong answer (C), and explaining their choice as follows: The value of l_keep_going is not initialized to TRUE and is therefore unknown. It will not enter the loop and therefore the loop is not infinite."

Oh my, oh my. They are so right. And I was so sloppy. And I was so over-confident, that I did not ask anyone with PL/SQL experience to proof my Q&A.

Let's take a closer look at choice C:

DECLARE
   l_keep_going BOOLEAN;
BEGIN
   WHILE (l_keep_going)
   LOOP
      DBMS_OUTPUT.PUT_LINE ('abc');
      l_keep_going := SYSDATE IS NOT NULL;
   END LOOP;
END;

I declare a variable that I will use to determine termination of the loop, but I neglected to provide a non-null default value. As a result, the WHILE condition fails immediately, the loop body never executes even once – and certainly not an infinite number of times!

This kind of bug often manifests in this form as well:

DECLARE
   l_counter PLS_INTEGER;
BEGIN
   LOOP
      do_something;
      l_counter := l_counter + 1;
      EXIT WHEN l_counter = 100;
   END LOOP;
END;

Again, I have failed to initialize my counter (likely it would be set to 1) and in this case the result is an infinite loop.

Hey, that must have been what I meant to use as choice C! Yeah, right.

So the lessons to be learned are:

Anyone can and everyone does make mistakes. Including the self-named PL/SQL Evangelist.
Code review is a critical component of high quality development. All critical code (and what isn't?) should be looked at by another human being before it goes "live."

An interview with Steven Feuerstein

Steven Feuerstein — Thu, 23 Apr 2009 01:06:00 GMT

In case you simply can't get enough of Steven Feuerstein (no, I don't generally talk about my self in the third person or in the royal "we"), I invite you to check out this interview. I had an awful lot of fun answering the questions, and you might be entertained reading them.

A handy string parsing utility - extended

Steven Feuerstein — Mon, 13 Apr 2009 16:57:00 GMT

It's truly one of the oddities of the PL/SQL language that it does not offer a delimited string parsing program. The closest we can get is DBMS_UTILITY.COMMA_TO_TABLE, and that is sadly deficient (it only parses comma-delimited strings and each item between the commas must be a valid PL/SQL identifier).

So I built one myself (the parse package) and put it in the demo.zip file that accompanies my training materials. You will find it in the parse.pkg file. For the past few years it has consisted of a single function that accepts a delimited list and returns a collection of the items in the list.

On a flight from London Heathrow to Istanbul in April 2009, though, I decided to make this package a bit more useful. I added two overloadings of the string_to_list function so that you can now easily parse (1) a string that contains multiple lists and (2) a string that contains multiple lists, each of which has a name.

So I have updated the demo.zip download to include this new version, and I thought I would bring it to your attention. The demo.zip contains about 2000 files and there is no index. So it's just a tad bit difficult to find the useful programs within it.

I have also created a Quest Code Tester for Oracle test definition to verify the behavior of these functions. If you would like to check that out, install Quest Code Tester 1.8.3 or higher (available as a beta here until mid-May 2009), and then import the Q#PARSE.qut file (also in demo.zip).

Here are examples of using the three different overloadings of parse.string_to_list:

DECLARE
   l_list   parse.items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d',',');
END;
/

DECLARE
   l_list   parse.nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

DECLARE
   l_list   parse.named_nested_items_tt;
BEGIN
   l_list:=parse.string_to_list('a,b,c,d|1,2,3,4','|',',');
END;
/

The third overloading, which returns a collection of type parse.named_nested_items_tt, also offers a demonstration of the elegance and usefulness of multi-level and string-indexed collections.

I hope you find the parse package useful.

And I hope that someday we can happily deprecate it, for the very good reason that PL/SQL finally offers one of its own.

Run an OS command from within PL/SQL

Steven Feuerstein — Fri, 27 Mar 2009 13:14:00 GMT

Developers are hard people to satisfy. Oracle gives us this incredibly robust, powerful and easy to use database programming language – and all we can do is complain about what it doesn't do for us.

Well, that's reality for you: PL/SQL is powerful and robust and easy to use, but also very narrowly focused. So if you want it to do something outside of its area of expertise, sometimes you have to jump through a few more hoops than you'd like.

A good example of this is figuring out how to run an OS command from a PL/SQL block. There is not, unfortunately, a native "execute host command" statement within PL/SQL . Instead, Oracle offers (at least) three ways to do this:

Use the DBMS_SCHEDULER package
Run a Java method to execute your command.
Run a C program to execute your command.

DBMS_SCHEDULER offers the simplest and most intuitive solution, especially from the standpoint of DBA and system administration tasks. I talk about this solution on the OTN Best Practice page:

http://apex.oracle.com/pls/otn/f?p=2853:4:1544539351000407::NO::P4_QA_ID:16282

I will, in this Toad World blog, offer brief guidance on the other two options. It is very unlikely that you will be able to rely solely on the content below to implement either approach. Instead, I suggest you use this as a starting point and then from there visit the detailed Oracle documentation and/or the full chapter coverage of both these techniques in my Oracle PL/SQL Programming.

Executing an OS command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.
Build a class that invokes that host command method. Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;

public class execHostCommand
{
public static void execute (String command)
    throws java.io.IOException
{
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
}
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.
Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.
Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing an OS command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, check out Oracle documentation at:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#ADFNS010

and also read Chapter 27 of Oracle PL/SQL Programming, 4^th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.
Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll
Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".
Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle’s CREATE LIBRARY privilege.
Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);
Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Automatic refactoring in PL/SQL tools – Part 3

Steven Feuerstein — Mon, 09 Mar 2009 15:18:00 GMT

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. In part 2, I checked out PL/SQL Developer's refactoring features. Now, it's time for Toad.

Differently from both PL/SQL Developer and SQL Developer, Toad does not have a separate Refactoring right click menu. Instead, the main (and most important) refactoring is explicitly visible from the right click menu in the editor: Extract Procedure.

Let's dive right in and explore this feature. When I select Extract procedure...., I see the following window:

When I choose the default (and check the "With replace option" – which I would argue should be checked by default), Toad then opens another tab in my editor and fills it with this:

It did not automatically compile the procedure for me, which I actually like. I'd rather be responsible for the act of compiling - and perhaps over-writing an existing procedure of the same name, which in fact would have occurred here, as you can see from my list of existing procedures:

The extraction was smart; I ended up with a procedure that has two formal parameters, ensuring that it is completely independent of the original context (the use_cursor procedure). That original procedure is also changed appropriately:

If I choose the "local procedure" option, then Toad correctly creates a local subprogram for me:

Conclusion: a very handy and competent refactoring. It performs correctly under all tested circumstances (PL/SQL Developer had a number of bugs, which I have been told is fixed in a later release than I used). My suggestions for improvement? Let me specify that I want the stored program to be not schema-level, but inside a package. And if I could choose an existing package to put it into, well, that would be fantastic! I could cut down on the number of independent stored program units (I try to avoid schema-level functions and procedures whenever possible), and increase the likelihood of code re-use.

That is the only truly automatic refactoring feature in Toad, at least from my perspective. But for the sake of performing a true apples-to-apples comparison, you may recall that SQL Developer offers these options on its refactoring menu:

I don't really consider case conversion to be refactoring, but in case you do, you can certainly do that in Toad, either under the Edit menu in Toad or on Editor toolbar (or you can simply use the keyboard shortcuts):

Toad doesn't offer any kind of rename feature, as does SQL Developer and PL/SQL Developer, but both of those implementations were a bit hobbled in how they worked. I doubt they are used much on a day to day basis.

Overall Conclusion

All three tools examined (Toad, SQL Developer and PL/SQL Developer) offer competent Extract Procedure functionality, though at least in the versions I used, Toad had the fewest drawbacks in its implementation. Beyond that, Toad has fewer explicit refactoring features than PL/SQL Developer. My feeling, however, is that outside of Extract Procedure, the refactorings are minor and of limited importance.

I hope that with the introduction of PL/Scope and ALL_IDENTIFIERS in Oracle11g, we will see a major step forward by all IDE vendors as regards refactoring and code analysis.

Automatic refactoring in PL/SQL tools – Part 2

Steven Feuerstein — Tue, 06 Jan 2009 16:30:00 GMT

In part 1 of this series, I reviewed the automated refactoring features in SQL Developer. Now I will check out PL/SQL Developer (version 7.1.5)'s refactoring features.

To access these features, I highlight some code in a procedure edit window and then choose Refactoring from the right click menu. I see these options:

I explore each of these below.

Rename item

If I highlight the name of identifier in its declaration section, the Rename item option is enabled:

I specified "abc" as the new name for this variable and PL/SQL Developer correctly substituted all instances of that identifier:

What if I have another variable with the same name in a different scope? PL/SQL Developer does a nice job of recognizing that it should not change it:

Sadly, it's not yet working at genius level. If I include in the subprogram a reference to the outer l_cursor variable, it does not change that name:

Conclusion: Works quite well, though it would be nice if I could highlight an identifier name anywhere in the program and have it do a rename (not only in the declaration statement for that variable). Could be a bit smarter, but for most "normal" scenarios, it should be fine.

Extract procedure

This is one of the most important refactorings: take logic out of your executable section and "hide" it inside a local procedure – or in a schema-level procedure. So I highlighted a line of code and choose this option. I was then asked to provide the new procedure name:

After providing that name and pressing OK, I see the following:

Well...that didn't go very well. Notice that it grabbed a part of the next line (DBMS_SQL.PARSE), even though I didn't highlight it. Ugh. OK, I will do it again, with just the code from that single open_cursor line. The result looks much better; notice that the tool correctly extracted all local variables and arguments referenced in the code and now passes them in as arguments:

There is one big problem, however: the code will not compile, because the subprogram was inserted before the l_cursor declaration and that is invalid:

Here's a nice touch, though: if your highlighted code references local variable that are used only in that highlighted code, PL/SQL Developer will move the declaration for that variable out of the main program and into the local module. In other words, from this:

to:

Conclusion: Disappointing overall. It's got the right idea and has been pretty-well thought through, but has at least two fairly serious flaws. Also, SQL Developer offers the option to define locally or store as a separate subprogram, which PL/SQL Dev does not.

Extract local constant

Here's what the help doc says about this one:

"If a certain expression should be converted to a local constant, you can select it and provide the constant name. A local constant will be created within the current subprogram, of a type that is determined from the expression. All occurrences of the expression in the current subprogram will be replaced by the constant name."

All righty – let's try it out. I will create a constant for the call to the open_cursor function:

And the result:

PL/SQL Dev did a good job of not including the ; from the highlighted text in the value assigned to the constant. It correctly replaced the call to the function with the named constant. It did not, however, come up with the right datatype for the constant. Instead, it seems to have simply added up the number of characters in the selected text. That's just weird!

Next I will highlight the local variable and change it to a constant:

The result is not great:

It seems to me that it should have left the original declaration of l_cursor alone. The result is code that will not compile.

Conclusion: marginally useful refactoring.

Extract global constant

PL/SQL Dev help tells us the following about this feature:

"If a certain expression should be converted to a global constant, you can select it and provide the constant name. A global constant will be created within the current package, of a type that is determined from the expression. All occurrences of the expression in the current package will be replaced by the constant name."

So what does this do? Well, if I use it inside my procedure, it acts the same as with a local constant extraction. I would have thought it should prompt me for the package name and either move the variable to that package or create a new package with that name.

Next I tried in a package:

and it did the right thing, sort of:

It still does not work properly with datatypes, though. It just defines them as VARCHAR2 all the time.

Conclusion: same as for local constant....minimally useful.

Swap assignment

A very minor "refactoring". Use it to change an assignment of this form:

a := b;

b := a;

It's fairly smart. You don't have to highlight the entire assignment statement, just a part of it, and PL/SQL Dev will move everything on the right to the left.

Conclusion: not really a refactoring, but it could be a useful accelerator on occasion.

Replace assignment with initialization

The help doc says:

"If a local variable assignment is purely for initialization, you can move it to the declaration of the variable. You can right-click on a statement or select multiple statements."

So I started with this code:

When I clicked on the variable name, the identifiers lit up:

I then chose my refactoring option

and ended up with this:

That's nice...though I have one concern and one desire:

Concern: the change is valid, but it could cause problems. When an exception raised in the declaration section, it cannot be handled within that block's exception section. The exception always propagates unhandled to the outer block. So making this change could affect program behavior.
Desire: This would be even nicer if it was combined with the option to move the assignment to an initialization procedure not simply to the declaration statement.

Conclusion: mildly useful, but sometimes not desireable due to the way exception propagation works in PL/SQL.

Overall Conclusion

Refactoring in PL/SQL Developer is minimal, but useful. It's done in a fairly smart way. Now if they would only give me a key stroke to pop up the refactoring menu, rather than having to make my way down through two levels of right click menu options.

Automatic refactoring in PL/SQL tools – Part 1

Steven Feuerstein — Tue, 09 Dec 2008 19:23:00 GMT

I have long been attracted to the idea and process of refactoring (explained below). It is now very exciting to see that automated refactoring features are working their way into tools for PL/SQL development. I will analyze these features over the next few entries in my ToadWorld blog, starting with SQL Developer and then moving on to PL/SQL Developer, and finishing up with SQL Navigator and Toad.

Oracle recently released Version 1.5.1 of SQL Developer, its free development tool for PL/SQL developers. I have been watching SQL Developer since Oracle first brought it into the world, seeing as (a) I am obsessed with PL/SQL; and (b) Oracle has had a checkered history when it comes to producing decent development tools for us PL/SQL developers; and (c) I work for Quest Software and of course we are keen to see how SQL Developer stacks up with Toad and SQL Navigator.

My overall assessment of SQL Developer has been: it's a fine, clean, light development environment – with lots of room for growth and catching up. That is, for a free product that is relatively new, it is a nice effort. Not too surprising, since it leverages the JDeveloper framework, into which Oracle has invested lots and lots of resources.

Would I use it instead of Toad or SQL Navigator or a number of the other tools that have been out there for awhile? Not unless my budget consisted of $0 for PL/SQL development tools. As with any piece of software, it will take time for SQL Developer to mature and fill out its feature set.

The SQL Developer team is certainly not standing still, however, and with Version 1.5, they have added several new features. One feature that caught my eye in particular was support for automated refactoring.

" Refactoring? What's that?" some of you may be wondering. You can see lots of information at http://www.refactoring.com/, but briefly (and from that site):

" Refactoring is a disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior. Its heart is a series of small behavior preserving transformations. Each transformation (called a 'refactoring') does little, but a sequence of transformations can produce a significant restructuring. Since each refactoring is small, it's less likely to go wrong. The system is also kept fully working after each small refactoring, reducing the chances that a system can get seriously broken during the restructuring."

The bottom line is that it is a highly structured methodology for going through clearly defined steps (each ideally verified by a pre-defined regression test) to make your code more maintainable.

Automated refactoring is a process that applies well-established and accepted refactorings automatically to your code. IDEs for object oriented languages have offered automated refactorings, albeit very simple ones, for a while. Now, SQL Developer offers this capability.

Exciting! So I decided to take a look. After installing SQL Developer 1.5, I opened up a program definition in an editor window, highlighted some code and checked out the right click menu. There it is!

Interesting....but what are they? I dipped into Help just to see what I could find about these and found....very little. Just a window on Extract Procedure. So let's start with that first. SQL Developer tells me:

"This dialog box is displayed if you are editing a procedure, select one or more PL/SQL statements, right-click, and select Refactoring, then Extract Procedure. The selected statements are encapsulated into the procedure to be created.

"Defined Locally: For a standalone procedure, defines the newly refactored code in the definition section of the original procedure.

"Stored: For a standalone procedure, defines the newly refactored code in a new standalone procedure.

"Name: Name of the procedure to encapsulate the selected statements. For a packaged procedure, the newly extracted procedure text is placed immediately after the current procedure."

Now, I really like this. I do. I believe that encapsulation or information hiding is one of the most important factors in writing maintainable code. And I believe in particular that we should make extensive use of locally-declared modules to do this.

So I highlight the line of code I want to encapsulate (the open cursor statement). I then, for starters, ask to create it as a schema-level procedure:

Type in the name, press OK, and I see this:

So far...OK, but just OK. Notice that it does not recognize that my line of code references a parameter from the main program. It would seem to me that the refactoring process should automatically create an argument for this procedure. This code isn't even valid. But I press Yes, and the box goes away, fine, but then I see that the original code is still present in my procedure:

That's no good – I wanted to replace it with the new stored procedure. I check the browser and find that this procedure has not been created in my schema:

Now maybe that's because there was an error in the code (un-declared referenced to security_level_in), but I didn't get any notification. OK, I will try it again with code that will compile, but still I see nothing. Well, I have no idea what is going on here, but really what I want to do is create a local module, so let's try that.

I highlight the code, choose my refactoring option, choose Local:

I type in the name of my new local module and press OK. I then see this:

Ugh. That's no good. It's supposed to be a local module. SQL Developer has placed this code above and outside of the use_cursor procedure. I then have to move it inside to make it work.

Bottom line, SQL Developer developers: nice idea, but your implementation leaves much to be desired. I don't see how I can use this to help me develop more modular, maintainable code any more easily than if I cut and paste myself.

Let's check out the other refactoring options. "Rename Local Variable..."

Hey, I like the sound of that! All too often we come up initially (in the heat of development) with bad variable names like "d" or "l_integer." Yuch! So the ability to be able to provide a new name and have the product sweep through my code and replace all instances...well, that would be pretty darned cool. Let's give it a try; in the program below I reference l_cursor four times:

There's no help for this feature, so I figure I will highlight the variable name and then specify a new one that indicates that this is a dynamic cursor:

Press OK, and it worked very nicely:

Excellent! Now I will add a reference to a package variable with the same name:

and try to do another renaming. This last addition should not be changed, as it is not the local variable. OK, I highlight the variable inside the code and try to change it, but I get this message:

Hmmm. I guess I need to highlight the declaration itself, though that shouldn't be necessary. OK, I do that, ask to rename variables to abc and sadly I see this:

So the rename feature is nice enough, but if it is not scope-aware (that is, if it cannot distinguish between locals, globals and even program names – yes, in another test, it even replaced the name of a packaged program that happened to have the same name as the local variable), then it really isn't doing anything better than a search and replace.

It also lets me change the variable name to something that is not a valid identifier, such as 123. If this feature is really directed at changing variable names, it should be aware of the rules and at a minimum put invalid names inside double quotes.

Finally, I tried to change a local variable's name from abc to something with about 100 characters in it and got this message:

Not sure what this means, but it certainly isn't helpful.

So I have to rate this feature a marketing-inflated misnomer. Sorry, SQL Developer developers!

And the third refactoring:

This lets you toggle between different case formats. It seems to work well enough. Honestly, however, I believe that it is not appropriate to include something like this under the Refactoring banner. It is a simple key driven formatting feature.

In conclusion, I am very pleased that the SQL Developer has introduced automated refactoring to the PL/SQL community. This is a concept and capability which is badly needed. The implementation in Version 1.5, however, leaves much to be desired.

Next/soon, I will take a look at PL/SQL Developer's refactoring capabilities.

Congratulations to the newest Oracle Magazine PL/SQL Developer of the Year!

Steven Feuerstein — Mon, 03 Nov 2008 15:03:00 GMT

As I travel certain parts of the globe doing presentations on PL/SQL, I meet many extremely talented PL/SQL programmers – people who meet the most complex challenges you can imagine with a deft combination of intense creativity, hard work, and of course Oracle PL/SQL.

Oracle Magazine names only one as the PL/SQL Developer of the Year at each Oracle Open World, and this year the award was given to Alex De Vergori of Betfair. Oracle Magazine writes:

Developer works with 250,000 lines of PL/SQL code that deliver success.

Successful PL/SQL developers require more than just good technical knowledge, according to Alex De Vergori, Oracle Magazine’s PL/SQL Developer of the Year.

“They also need fine attention to detail, a passion about what they’re doing, and a high degree of pride in the software that they’re producing,” says De Vergori, a database architect who channels those skills at Betfair, an online gaming company that pioneered the concept of betting exchanges in 2000.

To manage customers’ bets, De Vergori and the Betfair team have created a core betting engine and business logic that encompass more than 250,000 lines of PL/SQL code. The Betfair solution runs on Oracle Database using distributed databases around the world and an Oracle data warehouse running Oracle Real Application Clusters. Betfair also uses Oracle Coherence for its in-memory distributed data caching requirements, Java for its middle tier, and Ajax for its front-end applications.

“We take in excess of five million transactions a day, and they all go straight through to the database, where those bets match with other transactions in real time,” says De Vergori. “At peak, we can easily see something like 1,000 bet transactions per second. With Oracle, our growth has been exponential, so that has enabled us to succeed in our market.”

Click here if you would like to read more details about how Betfair is using PL/SQL.

Alex, welcome to the "select circle" of PL/SQL Developer of the Year awardees. Now, finally, you understand why you were spending all those long days, nights and weekends writing PL/SQL code!

Play games to become a better developer!

Steven Feuerstein — Tue, 28 Oct 2008 16:23:00 GMT

Part 2: The Game of Mastermind

Software development is one heck of a serious job. It turns out, however, that there are several games you can play to improve the quality of code you write. This is the second of two blog entries that introduce you to two of my favorite brain development and training games: Set and Mastermind.

Play either (preferably both) of these games, and you will write better software.

[ Note that I do not include Solitaire in this list. Playing this game will definitely not help you become a better developer, but it will pass the time. ]

I even encourage you to play these games on company time, with management approval. It will be a good investment by your employer. Sound crazy? I have been told by a number of my students that their manager did, in fact, agree to do this, and everyone is happy with the results. I will come back to this point at the end of this blog.

So the game featured in this blog is Mastermind. Mastermind has been around for decades, usually played on a small plastic box filled with holes into which pegs of different colors are placed. You can find lots of electronic versions of Mastermind on the Internet. Here's the one I used in my blog:

http://www.irt.org/games/js/mind/index.htm?b=8&t=8&c=4

So how does it work? You have to guess the colors of the pegs in each of the positions, with the smallest possible number of guesses. Each time you make a guess, Mastermind (the electronic version) tells you how close you are by providing clues (when playing with another human being, they provide the clues – and hopefully they don't make any mistakes).

There are two kinds of clues:

You got the color and the position right. Usually this clue is indicated with a black peg.
You got the color right, but the position is wrong. Usually this clue is indicated with a white peg.

When you get four black pegs, you have won that round.

The game is more or less difficult depending on:

the number of positions
the number of choices of colors (including or excluding blanks)
whether or not you can repeat colors in the solution

You could, of course, simply guess each time, in which case you will be very lucky (and only lucky) to actually ever win. But the point of the game (at least, the way that I understood the point of the game) is to use logic to narrow down the choices that are valid based on the clues provided.

Let's walk through a game of Mastermind. In this version, we will not allow blanks nor repeated colors. Here's the starting point:

First try: it's luck and random selection. In other words, one guess is as good as any other (this first turn is the only time at which this statement is valid)....so I will simply choose the first four colors in the order shown in the game.

Then I press OK and Mastermind shows me how close I got. One white clue.

Not very close! So only one of these colors is in the solution. This is a good start, precisely because there is so little information. Once I figure out which of these colors is in the solution, I know that the other three are not.

What shall I do next? Pick the color that I will assume is in the solution, and move it to another location so that, theoretically, it could then result in a black clue.

I will make the smallest possible effort here, and move black over one. I will then use the next three colors on the second row:

Time to verify my second guess against my first clue. I have only color repeated from the first guess, and it is in a different position. OK, this could be right. Wish me luck....press OK..and:

Not bad at all! Now Mastermind is telling me that two of these four colors are in the solution and they are in the right place. One other color is also in the solution, but is in the wrong place.

Time to make another guess. Let's assume that red and black are the black clues and white is in the wrong place. We already know that green, brown and purples are not in the solution (if my assumption about black is correct), so what can I use for the fourth color? There is just one left: yellow.

Where should I put the yellow and white? I have already assumed that red and black are in the right positions. So that means that white would have to be in the fourth position (I don't have three black clues), and yellow in the third.

This is getting suspenseful! Press OK and....

Hmmm. I seem to have taken a step backwards. Instead of two black clues, I have just one. OK, it is time now to stop and take stock.

What can I now conclude about the solution, given these three sets of clues? Quite a lot, namely:

A fact	How do I know?
The white is in the third position or the blue is in the fourth.	I repeated red and black and lost a black clue. Therefore, one of the two on the right half must have been the source of the black clue.
Both black and red cannot be in the solution.	If black is in the solution, then green, brown and purple are not. If red is also in the solution, then only one of white and blue can be in it. If white is in it, then the fourth clue tells me that yellow cannot be in it. I have now exhausted all the colors and we do not have a fourth color. That's not possible. So it's either red or black, but not both.
White, blue and yellow must be in the solution.	The second guess has three clues, so three of the four must be in the solution, but only one of red and black can be, so the other two must be. The third guess also has three clues and again, this means that the two that are not red and black must be in the solution, so yellow must be in it.

Now I have enough information to make the smallest numbers of assumptions and produce a solution. If this works, we win. If it doesn't, I will get lots of additional data from which to work (that is, it will show me that an assumption was invalid, forcing me to re-evaluate and take a new path).

Let's keep going with black in the second position; I haven't yet encountered any reason to think it is not there. That accounts for the one black clue from the second turn. Let's now go with white in the third position. Blue must then be in the first position, and yellow in the fourth.

I then go back and make sure this last guess is consistent with all previous guesses and clues:

Guess/clue 1: Only black is repeated from the first turn and it is in a different position, so guess 4 could be correct based on this clue.
Guess/clue 2: The black and white colors are in the same position and the blue is in a different position, so guess 4 could be correct based on this clue.
Guess/clue 3: The black is in the same position, but yellow and white are now in two different positions, and I added in a different color. Guess 4 could be correct based on this clue.

OK, I have verified that this guess could be correct. So how did I do? Press OK and....

I did it!

Now, that's very satisfying, but I must also stay humble. I was lucky enough to make the correct assumption about black in the very first turn. I could easily have decided to assume that green was the correct color, and then spent several turns making guesses before I discovered I was wrong.

Regardless, at each step of the way, I used deductive logic to extract all possible information from the clues, and use them to construct another guess that is consistent will previous clues.

So what does this have to do with software?

Isn't it enough fun all by itself?

Sure, but I did claim that playing this game would help you become a better developer. So I really should explain. Here goes....

I claim that playing Mastermind a lot and getting really good at it will help you debug your code more quickly and accurately.

Conversely, if you have trouble "mining" conclusions from clues in Mastermind, I believe that you would find it very difficult, perhaps even torturous to debug your (or anyone else's) code.

Why do I say this? Well, let's walk through how we build our code.

I am handed requirements. I write a first pass at the code to implement those requirements. I get my code to compile (wow!). Then (theoretically), it is time to test my code.

So I run my test, and I get "clues" – my test (hopefully a regression test that exercises my code for a variety of input scenarios) tells me the circumstances of failure (When I pass in "ABC", I get 1 – the right answer. When I pass in "123", I get NULL – the wrong answer).

I might also trace execution of my code, giving me lots of additional clues.

I then take all this information (forget about up to four conveniently colored clues – I will usually get many more pieces of feedback then that!), use it to guide my debugging session, and identify changes to the code that I believe will result in a correct program.

That's my next "guess." I then rerun my tests, get my next set of clues, and on I go.

Now, doesn't that sound just like the process of finding a solution to Mastermind, only way more complicated?

So if you have trouble playing Mastermind, I've got to conclude that you will more easily get lost in the tangle of code and clues you get when testing. And if you can play Mastermind very well (which means, by the way, that you can also rapidly get to a solution even when duplicate colors and blanks are allowed, greatly increasing the number of possible solutions and requiring more sophisticated strategies for solving the puzzle), I am certain you will find it that much easier to juggle larger amounts of test feedback and apply all of that productively to your code.

Play games to become a better developer!

Steven Feuerstein — Mon, 20 Oct 2008 14:01:00 GMT

Part 1: The Game of Set

Software development is one heck of a serious job. It turns out, however, that there are several games you can play to improve the quality of code you write. This is the first of two blog entries that introduce you to two of my favorite brain development and training games: Set and Mastermind.

Play either (preferably both) of these games, and you will write better software.

[ Note that I do not include Solitaire in this list. Playing this game will definitely not help you become a better developer, but it will pass the time. ]

So the game featured in this blog is Set. Set is a "family game of visual perception," according to the creator (lots more details at www.setgame.com). I think it is better described as a subtle and sophisticated pattern analysis/matching game.

Set is a physical card game, though you can play the daily puzzle here. They used to sell a software version of the game, which I will be using in this blog, but it is no longer available.

So how does it work? You lay out twelve cards from the deck on the table of your choice. Here we go:

So, the object of the game is to find sets of three cards. Whoever finds the most sets wins the game.

You are now wondering: how do I know which three cards go together in a set? Here are the rules:

Each card has four characteristics: shape, coloring, number, and filling.
Each characteristic has three possible values. Oval, squiggle and diamond for shape; green, red or purple for color; one, two or three objects for number; lined, solid and empty for filling.
Three cards make up a set if each characteristic, independent of the others, is all the same or all different.

An example of a set: all reds, all ones, oval-squiggle-diamond and lined-empty-solid.

Three cards that do not make up a set: red-red-red, two-one-three, solid-solid-solid, and oval-diamond-oval. Two ovals and one diamond are neither all different or all the same.

In other words, two out of three is a no-go in Set.

Note that you can never have all four characteristics the same (they would then be the same card, and the Set deck contains no repeats). You can, however, have a set in which all characteristics are different. I find this to be the challenging and satisfying set to find.

OK, let's use deductive logic to find ourselves a set. Notice the two cards in the middle of the top row?

They could be in a set (any two could, in fact!). Let's see if we can figure out what the third card would have to be. Then if we are lucky enough to have that card on the table, I will have my first set.

The first two cards:

Each have two items. So the third card has to have two items, too.
Are green and red. So the third card would have to have a different color: purple.
Are solid and lined. So the third card would have to have a different filling: empty.
Are oval and squiggle. So the third card would have to have a different shape: empty.

So the third card would have to be: two-purple-diamond-empty.

Can you see it?

There it is on the bottom row, third column!

Now, if we were sitting around a table together, I would shout: "Set!" and if did so before anyone else, I would scoop up my set. If someone else said "Set" first, they could then take that or a different set, if they had found one (I don't think there is one).

So those three cards go into my pile and we put down another three cards:

And the game proceeds this way until all sets have been found.

Can you identify the (at least) three sets in the above twelve cards?

There are two different sets using and two different sets using . Can you find them? See answer at end of this entry.

So that is Set. When you first start to play this game, you will likely follow the path to a solution I showed above; namely, use deductive logic to find the third card given the first two.

You probably, however, will not play this way very long, at least not if you are playing (competing) with others.

In Set, stepping your way through a logical proof ("If the first two are red, the third must be red.") is too slow. You will lose again and again. Not to wrry! After you have been playing for a while, your brain will of its own accord (whatever that means) abandon explicit deductive logic. Instead, your eyes will roam over the entire set of twelve cards and the three cards that make up a set will "appear." Your brain will have by this time trained itself to assimilate the various kinds of input and resolve the problem very rapidly.

How does Set help software developers?

So what's the connection to software? There is a movement in the world of software called "Design Patterns." Here's the basic idea: there's nothing new under the sun; any code you write today is likely very similar to what you or someone else wrote yesterday. Most of our code, in other words, follow common patterns, because we are mostly solving the same kinds of problems, albeit described differently in different domains, over and over again.

So if you can abstract out the pattern, and then provide a similarly generic solution to the pattern, the next time you encounter a problem that fits the pattern, you can immediately apply the general solution, and work simply on fitting it precisely to the specifics of your current challenge.

In other words, lots of software development is all about pattern analysis and recognition, followed by taking logic-based action in response to that pattern.

The better you are at identifying patterns, the better you will be at writing elegant software that avoids the repetitions of logic that invites bugs and mitigates against maintainability.

And that is why I encourage playing this game while you are on company time. In fact, I suggest that team leads or managers organize a weekly Friday pizza lunch during which you play Set as a group. Great team builder and brain builder!

Is Set only good for work?

Ha! No way. In fact, if you have children under the age of twelve or thirteen – anyway, at an age when they are still willing to play a game with you, order Set now (you can find locations in the U.S. that sell Set right here) and start playing it with your kids.

I bet a child as young as four could get started (perhaps with only two colors, or some other way of simplifying the rules). The sooner the better – Set will help make your child smarter!

The other nice thing about playing Set with your kids is that it is very likely that once they get the hang of it, they will regularly and decisively defeat you. Their minds are so nimble, probably devoting their full concentration to the game, while yours is jumbled full of bills to pay, what to cook for dinner, and so on, that you will spare only a part of your total (and totally awesome) brain power.

And that's OK. Kids generally don't get to control too much of their environment. To know that you are trying hard as you can and still lose to them, well, that is very empowering. Great for kids when they're young, perhaps a point of regret when they become teenagers.

In conclusion, I urge you to get your copy of Set today. Play it at home with your family and friends. Play it at work with your co-workers. Make your brain strong and help make the world a far better place than it is today.

The Answers

Two with

Green-purple-red

One-one-one

Squiggle- squiggle- squiggle

Lined-lined-lined

One-two-three

Purple-purple-purple

Lined-lined-lined

Squiggle- squiggle- squiggle

Two with

Same as before, namely:

Green-purple-red

One-one-one

Squiggle- squiggle- squiggle

Lined-lined-lined

And

Green-purple-red

One-one-one

Squiggle- oval-diamond

Lined-solid-empty

Doing SQL in PL/SQL: key resource from Bryn Llewellyn

Steven Feuerstein — Wed, 08 Oct 2008 17:09:00 GMT

One of the highlights of Oracle Open World 2008 for me was the presentation by Bryn Llewellyn (PL/SQL Product Manager) on “Doing SQL in PL/SQL.”

Bryn surely has the most thorough and clear understanding of the PL/SQL language of anyone I have met (definitely including me).

His talk was detailed and precise (and maybe just a little bit overwhelming. He needed twice the time allotted) on this most important topic.

The bad news about this talk is that most PL/SQL developers will never be able to hear Bryn present it.

The good news is that everything he talks about (and more) may be found in a newly published whitepaper of the same name.

Simply visit the Oracle Technology Network PL/SQL Homepage and click on the hyperlink for that paper. Notice also that Bryn has published an important whitepaper on SQL injection.

Your understanding of PL/SQL will increase dramatically, as will your ability to write high quality PL/SQL programs, by reading both of these papers.

Thanks, Bryn!

For loops or While loops to scan collections?

Steven Feuerstein — Mon, 22 Sep 2008 16:21:00 GMT

I have generally recommended in the past that whenever you are writing code to iterate through the elements of a collection, you should use a while loop, combined with the FIRST-NEXT or LAST-PRIOR collection methods.

The key advantage of this approach is that the code will not raise a NO_DATA_FOUND exception if your collection is sparse (there is an index value between FIRST and LAST that is not defined). And if your collection is empty, the loop will not execute at all, whereas with a for loop, an empty collection could cause a VALUE_ERROR exception if you are not careful.

Here is an example of the kind of code that requires the use of the while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEX BY pls_integer;

   employee_cache   employee_tt;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache (rec.employee_id) := rec;
   END LOOP;

   FOR indx IN 1 .. employee_cache.COUNT
   LOOP
      DBMS_OUTPUT.put_line (employee_cache (indx).last_name);
   END LOOP;
END;
/

When I execute this block, I get the following error:

Instead, I should use a while loop:

DECLARE
   TYPE employee_tt
   IS
      TABLE OF employees%ROWTYPE
         INDEX BY pls_integer;

   employee_cache   employee_tt;
   l_index           pls_integer;
BEGIN
   /* Fill the collection using the employee ID
      as the index value - most like these values are
      NOT sequentially defined; primary keys can
      certainly have "gaps". */
   FOR rec IN (SELECT   *
                 FROM   employees)
   LOOP
      employee_cache (rec.employee_id) := rec;
   END LOOP;

   l_index := employee_cache.FIRST;

   WHILE (l_index IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (employee_cache (l_index).last_name);
      l_index := employee_cache.NEXT (l_index);
   END LOOP;
END;
/

and then I will not see any errors.

So, no doubt about it, this is good advice – but should you always use the while loop?

I suggest that in situations when you know, when you are absolutely sure, that your collection is densely-filled, you should use the FOR loop, for two reasons:

It is a simpler solution, requiring less code (reducing the chance of a bug creeping into your code and making it easier to maintain).
It is more self-documenting. By using the for loop construct, you are stating that an assumption of this code is that the collection is densely-filled.
It is more efficient than the while loop.

So when you can be certain that your collection is densely-filled? When....

The collection is populated by a BULK COLLECT query. In this situation, the first index used is always 1, and the collection is filled sequentially (2, 3, 4...) from that index.
The collection is a nested table assigned its contents from a MULTISET operator (UNION, INTERSECT and EXCEPT). These set level operators always fill sequentially a nested table from index value 1.

And what kind of difference in performance can you expect to see? Not a very big difference, but the FOR loop is definitely faster. I put together the script below (relying on the sf_timer package, included in the demo.zip.

DECLARE
   l_index     pls_integer;
   l_source   DBMS_SQL.varchar2a;
BEGIN
   sf_timer.start_timer;

       SELECT    text
   BULK COLLECT INTO   l_source
         FROM    all_source;

   sf_timer.show_elapsed_time (
      'Retrieved ' || TO_CHAR(l_source.COUNT) || ' elements'
   );
   --
   sf_timer.start_timer;

   FOR indx IN 1 .. l_source.COUNT
   LOOP
      NULL;
   END LOOP;

   sf_timer.show_elapsed_time ('FOR loop through collection');
   --
   sf_timer.start_timer;
   l_index := l_source.FIRST;

   WHILE (l_index IS NOT NULL)
   LOOP
      NULL;
      l_index := l_source.NEXT (l_index);
   END LOOP;

   sf_timer.show_elapsed_time ('Full collection scan with NEXT');
END;

I saw the following results:

Retrieved 3079394 elements - Elapsed CPU : 11.14 seconds.
FOR loop through collection - Elapsed CPU : .05 seconds.
Full collection scan with NEXT - Elapsed CPU : .48 seconds.

In other words, the while loop is an order of magnitude slower than the for loop. That sounds like a big deal, but it's only a difference of .4 seconds with a scan of over 3,000,000 elements. With smaller collections, you'll probably never notice the difference.

Analyzing code coverage with the PL/SQL profiler

Steven Feuerstein — Wed, 27 Aug 2008 22:55:00 GMT

I have, for the past several years, focused heavily on designing and building an automated code testing tool for PL/SQL: Quest Code Tester for Oracle. One feature that is often requested as a part of code testing is analysis of code coverage, answering questions like:

When I run my program do I use 50% of the code? 75% of the code?
Are there chunks of logic that are never run

The only way to get this kind of information is to turn on the PL/SQL profiler (and/or the new hierarchical profiler delivered with Oracle11g). The profiler keeps track of each line run by the program during the profile session. You then query the contents of the plsql_profiler_data|units|runs tables and draw your own conclusions.

And therein lies the rub.

Code Tester users don't want to see lengthy reports showing which lines where executed. Instead, they want us to provide them a single number that tells them all they need to know: "75% of my program was executed." Period.

And I would like to provide that feature, I really would. But I am feeling a little bit stumped and thought I would share with you what I have done so far, and my areas of befuddlement, in hopes that you, my dear reader, may be able to help provide additional clarity.

So what's the problem? Well....to figure out the % of code coverage, I need to....

decide which lines of code I should include in the total number of possible lines that could be executed. For example: do I include comments? What about the IS, BEGIN, END keywords?
understand what lines of the code the profiler actually pays attention to, and records as having been run. I have never found the profiler data to be entirely straightforward.

And so I have decided to do some research and exploration. I created a package that contains lines of code with all sorts of line breaks to see if I could isolate how Oracle treats such code in the context of profiling.

You will find below the code for this package, followed by a utility I wrote to show profiling data in a way that I thought would be useful, and then the output from a profiling session.

Which leads to my questions for you:

Do you have any ideas on how I should go about computing this single, golden number regarding code coverage?
What lines do you think should be included or not?
Should I take a different approach in analyzing code and profiler behavior?
What do you think of the output I got from my "test" package?

Looking forward to your comments,

Steven

Package to exercise profiler

CREATE OR REPLACE PACKAGE what_is_profiled
IS
   TYPE aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2);

   FUNCTION func1
      RETURN VARCHAR2;
      
      procedure driver ;
END what_is_profiled;
/

CREATE OR REPLACE PACKAGE BODY what_is_profiled
IS
   TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
      val
      INTEGER;
      condition1 boolean := true;
      condition2 boolean 
      := 
      true;
      
   BEGIN
      FOR indx IN 1 .. 100
      LOOP
         NULL;
      END LOOP;
            
      FOR 
      indx 
      IN 
      1 
      .. 
      100
      LOOP
         val := 1;
      END 
      LOOP; 
      
      FOR indx IN 1 .. 100 LOOP NULL; END LOOP;

      FOR rec IN (SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101)
      LOOP
         val := 1;
      END LOOP;

      FOR 
      rec 
      IN 
      (
      SELECT *
                    FROM all_source
                   WHERE ROWNUM < 101
      )
      LOOP
         val := 1;
      END 
      LOOP;
      
      WHILE (condition1 AND condition2)
      LOOP
         condition1 := FALSE;
      END LOOP;

      WHILE 
      (
      condition1 
      AND 
      condition2
      )
      LOOP
         condition1 
         := 
         FALSE
         ;
      END LOOP;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT WHEN indx > 100;
            indx := indx + 1;
         END LOOP;
      END;
      
      DECLARE
         indx   INTEGER := 1;
      BEGIN
         LOOP
            EXIT 
            WHEN 
            indx 
            > 
            100;
            indx := indx + 
            1
            ;
         END LOOP;
      END;      
   END;

   PROCEDURE conditionals 
   IS
   a 
   boolean;
   b boolean;
   c boolean
   ;
   BEGIN
      IF (a AND b OR c)
      THEN
         NULL;
         elsif
         a
         then
         null;
         else
         dbms_output.put_line ('a');
      END IF;
      
      a := case
      true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;
      a := case true
      when true
      then
      false
      when 
      false then
      true
      else
      false
      end
      ;  
      
      case when 
      sysdate > sysdate + 1
      then
      a := false;
      when 1 > 2 then
      b := false;
      when 1
      > 2   
      then
      c := false;
      else null; end case; 
   END;

   FUNCTION p_func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN NULL;
   END;

   PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
   IS
   BEGIN
      NULL;
   END;

   FUNCTION func1
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN p_func1;
   END;
   
   procedure driver is
   l varchar2(100);
   begin
   loops(1, l);
   conditionals;
   proc1
   (
   1
   ,
   l);
   GOTO checkloop;
   <>
   dbms_output.put_line ('a');
   end;
END what_is_profiled;
/

Utility to show profiler data

CREATE OR REPLACE PROCEDURE show_lines_profiled
/*
Assumptions: the ONLY data inside the profiler tables are for a single
run of what_is_profiled.driver
*/
IS
   l_type varchar2( 10000 );
BEGIN
   DBMS_OUTPUT.put_line( 'Profiling Report' );
   DBMS_OUTPUT.put_line( '  NOT PROFILED = No profile data for this line' );
   DBMS_OUTPUT.put_line( '  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0' );
   DBMS_OUTPUT.put_line( '  LINE RUN     = This line was executed at least once' );

   DBMS_OUTPUT.put_line( 'Profile Info   Line Source');
   DBMS_OUTPUT.put_line( 
'============== ==== ==============================================================');

   FOR rec
   IN (  SELECT line, text
           FROM all_source als
          WHERE     als.owner = USER
                AND als.name = 'WHAT_IS_PROFILED'
                AND als.TYPE = 'PACKAGE BODY'
       ORDER BY line )
   LOOP
      BEGIN
         SELECT CASE
                   WHEN total_occur = 0 THEN 'ZERO RUNS'
                   ELSE 'LINE RUN'
                END
                   profile_type
           INTO l_type
           FROM plsql_profiler_data ppd
          WHERE ppd.line# = rec.line AND ppd.unit_number = 2;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_type      := 'NOT PROFILED';
      END;

      DBMS_OUTPUT.put_line(   RPAD( l_type, 15 )
                           || LPAD( rec.line, 4 )
                           || ' '
                           || rtrim (rec.text, chr(10)));
   END LOOP;
END show_lines_profiled;

Results of profiling the package

BEGIN
   DELETE FROM plsql_profiler_data;

   DELETE FROM plsql_profiler_units;

   DELETE FROM plsql_profiler_runs;

   DBMS_OUTPUT.put_line(
                         DBMS_PROFILER.start_profiler( 'What is profiled?' )
   );
   what_is_profiled.driver( );
   DBMS_PROFILER.stop_profiler;
   --
   show_lines_profiled();
END;
/
Profiling Report
  NOT PROFILED = No profile data for this line
  ZERO RUNS    = Profiled, but TOTAL_OCCURS = 0
  LINE RUN     = This line was executed at least once
Profile Info   Line Source
============== ==== ==============================================================
NOT PROFILED      1 PACKAGE BODY what_is_profiled
NOT PROFILED      2 IS
NOT PROFILED      3    TYPE p_aa1 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      4       INDEX BY PLS_INTEGER;
NOT PROFILED      5 
NOT PROFILED      6    TYPE p_aa2 IS TABLE OF VARCHAR2 (100)
NOT PROFILED      7       INDEX BY PLS_INTEGER;
NOT PROFILED      8 
ZERO RUNS         9    PROCEDURE loops (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED     10    IS
NOT PROFILED     11       val
NOT PROFILED     12       INTEGER;
LINE RUN         13       condition1 boolean := true;
LINE RUN         14       condition2 boolean 
NOT PROFILED     15       := 
NOT PROFILED     16       true;
NOT PROFILED     17       
NOT PROFILED     18    BEGIN
LINE RUN         19       FOR indx IN 1 .. 100
NOT PROFILED     20       LOOP
LINE RUN         21          NULL;
NOT PROFILED     22       END LOOP;
NOT PROFILED     23             
LINE RUN         24       FOR 
NOT PROFILED     25       indx 
NOT PROFILED     26       IN 
NOT PROFILED     27       1 
NOT PROFILED     28       .. 
NOT PROFILED     29       100
NOT PROFILED     30       LOOP
LINE RUN         31          val := 1;
NOT PROFILED     32       END 
NOT PROFILED     33       LOOP; 
NOT PROFILED     34       
LINE RUN         35       FOR indx IN 1 .. 100 LOOP NULL; END LOOP;      
NOT PROFILED     36 
LINE RUN         37       FOR rec IN (SELECT *
NOT PROFILED     38                     FROM all_source
NOT PROFILED     39                    WHERE ROWNUM < 101)
NOT PROFILED     40       LOOP
LINE RUN         41          val := 1;
NOT PROFILED     42       END LOOP;
NOT PROFILED     43 
LINE RUN         44       FOR 
NOT PROFILED     45       rec 
NOT PROFILED     46       IN 
NOT PROFILED     47       (
ZERO RUNS        48       SELECT *
NOT PROFILED     49                     FROM all_source
NOT PROFILED     50                    WHERE ROWNUM < 101
NOT PROFILED     51       )
NOT PROFILED     52       LOOP
LINE RUN         53          val := 1;
NOT PROFILED     54       END 
NOT PROFILED     55       LOOP;
NOT PROFILED     56       
LINE RUN         57       WHILE (condition1 AND condition2)
NOT PROFILED     58       LOOP
NOT PROFILED     59          condition1 := FALSE;
NOT PROFILED     60       END LOOP;
NOT PROFILED     61 
NOT PROFILED     62       WHILE 
NOT PROFILED     63       (
NOT PROFILED     64       condition1 
NOT PROFILED     65       AND 
NOT PROFILED     66       condition2
NOT PROFILED     67       )
NOT PROFILED     68       LOOP
NOT PROFILED     69          condition1 
NOT PROFILED     70          := 
NOT PROFILED     71          FALSE
NOT PROFILED     72          ;
NOT PROFILED     73       END LOOP;
NOT PROFILED     74       
NOT PROFILED     75       DECLARE
LINE RUN         76          indx   INTEGER := 1;
NOT PROFILED     77       BEGIN
LINE RUN         78          LOOP
LINE RUN         79             EXIT WHEN indx > 100;
LINE RUN         80             indx := indx + 1;
NOT PROFILED     81          END LOOP;
NOT PROFILED     82       END;
NOT PROFILED     83       
NOT PROFILED     84       DECLARE
LINE RUN         85          indx   INTEGER := 1;
NOT PROFILED     86       BEGIN
LINE RUN         87          LOOP
LINE RUN         88             EXIT 
NOT PROFILED     89             WHEN 
NOT PROFILED     90             indx 
NOT PROFILED     91             > 
NOT PROFILED     92             100;
LINE RUN         93             indx := indx + 
NOT PROFILED     94             1
NOT PROFILED     95             ;
NOT PROFILED     96          END LOOP;
NOT PROFILED     97       END;      
LINE RUN         98    END;
NOT PROFILED     99 
ZERO RUNS       100    PROCEDURE conditionals 
NOT PROFILED    101    IS
NOT PROFILED    102    a 
NOT PROFILED    103    boolean;
NOT PROFILED    104    b boolean;
NOT PROFILED    105    c boolean
NOT PROFILED    106    ;
NOT PROFILED    107    BEGIN
LINE RUN        108       IF (a AND b OR c)
NOT PROFILED    109       THEN
NOT PROFILED    110          NULL;
NOT PROFILED    111          elsif
LINE RUN        112          a
NOT PROFILED    113          then
NOT PROFILED    114          null;
NOT PROFILED    115          else
LINE RUN        116          dbms_output.put_line ('a');
NOT PROFILED    117       END IF;
NOT PROFILED    118       
LINE RUN        119       a := case
NOT PROFILED    120       true
NOT PROFILED    121       when true
NOT PROFILED    122       then
NOT PROFILED    123       false
NOT PROFILED    124       when 
NOT PROFILED    125       false then
NOT PROFILED    126       true
NOT PROFILED    127       else
NOT PROFILED    128       false
NOT PROFILED    129       end
NOT PROFILED    130       ;
LINE RUN        131       a := case true
NOT PROFILED    132       when true
NOT PROFILED    133       then
NOT PROFILED    134       false
NOT PROFILED    135       when 
NOT PROFILED    136       false then
NOT PROFILED    137       true
NOT PROFILED    138       else
NOT PROFILED    139       false
NOT PROFILED    140       end
NOT PROFILED    141       ;  
NOT PROFILED    142       
LINE RUN        143       case when 
NOT PROFILED    144       sysdate > sysdate + 1
NOT PROFILED    145       then
LINE RUN        146       a := false;
NOT PROFILED    147       when 1 > 2 then
NOT PROFILED    148       b := false;
NOT PROFILED    149       when 1
NOT PROFILED    150       > 2   
NOT PROFILED    151       then
NOT PROFILED    152       c := false;
NOT PROFILED    153       else null; end case; 
NOT PROFILED    154    END;
NOT PROFILED    155 
ZERO RUNS       156    FUNCTION p_func1
NOT PROFILED    157       RETURN VARCHAR2
NOT PROFILED    158    IS
NOT PROFILED    159    BEGIN
ZERO RUNS       160       RETURN NULL;
ZERO RUNS       161    END;
NOT PROFILED    162 
ZERO RUNS       163    PROCEDURE proc1 (arg IN NUMBER, arg2 OUT VARCHAR2)
NOT PROFILED    164    IS
NOT PROFILED    165    BEGIN
LINE RUN        166       NULL;
NOT PROFILED    167    END;
NOT PROFILED    168 
ZERO RUNS       169    FUNCTION func1
NOT PROFILED    170       RETURN VARCHAR2
NOT PROFILED    171    IS
NOT PROFILED    172    BEGIN
ZERO RUNS       173       RETURN p_func1;
ZERO RUNS       174    END;
NOT PROFILED    175    
ZERO RUNS       176    procedure driver is
NOT PROFILED    177    l varchar2(100);
NOT PROFILED    178    begin
LINE RUN        179    loops(1, l);
LINE RUN        180    conditionals;
LINE RUN        181    proc1
NOT PROFILED    182    (
NOT PROFILED    183    1
NOT PROFILED    184    ,
NOT PROFILED    185    l);
LINE RUN        186    GOTO checkloop;
NOT PROFILED    187    <>
LINE RUN        188    dbms_output.put_line ('a');
LINE RUN        189    end;
NOT PROFILED    190 END what_is_profiled;

Oracle Open World Presentations

Steven Feuerstein — Tue, 22 Jul 2008 15:41:00 GMT

Hey folks,

For anyone attending Oracle Open World this year, here is my schedule of presentations:

Session ID: S300184
Session Title: Weird PL/SQL
Track: Oracle Develop: Database
Room: Golden Gate C3
Date: 2008-09-21
Start Time: 15:45

Session ID: S300183
Session Title: Break Your Addiction to SQL!
Track: Oracle Develop: Database
Room: Salon 02
Date: 2008-09-22
Start Time: 13:00

Session ID: S300185
Session Title: Why You Should Care About Oracle 11g PL/SQL Now
Track: Oracle Develop: Database
Room: Salon 02
Date: 2008-09-23
Start Time: 11:30

Hope to see you there!

Always Bulk Collect

Steven Feuerstein — Mon, 23 Jun 2008 14:34:00 GMT

You learn something new every day, right? Well, I certainly do (more or less). Even about PL/SQL, about which I am sure many people think I already know everything. Far from it.

In fact, I learned just last week from the PL/SQL Product Manager, Bryn Llewellyn, that his recommendation regarding cursor FOR loops and bulk collect is different from mine – and for a very good reason.

Several years ago, he informed me (and provided a script to verify the statement: 10g_optimize_cfl.sql in the demo.zip) that in Oracle10g, the compiler now optimizes every cursor FOR loop to execute at a level of performance similar to BULK COLLECT.

This is a wonderful and non-trivial optimization. Rather than have to go through a manual rewrite of all cursor FOR loops, you can leave them in place and reap the benefits of the BULK COLLECT (sort of).

Well, that's not quite true – and it's even less true than I thought.

First of all, if your cursor FOR loop contains any DML statements, you will still want to explicitly convert to BULK COLLECT. The reason is that while the optimizer will improve the performance of the query step, it will not optimize the DML statements to execute like FORALLs (the bulk processing analogue for updates, inserts and deletes).

So if you have code that looks like (cfl_to_bulk_0.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   CURSOR emp_cur
   IS
      SELECT employee_id, salary, hire_date
        FROM employees
       WHERE department_id = dept_in;
BEGIN
   FOR rec IN emp_cur
   LOOP
      BEGIN
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (rec.employee_id, rec.salary, rec.hire_date
                     );

         adjust_compensation (rec.employee_id, rec.salary);

         UPDATE employees
            SET salary = newsal_in
          WHERE employee_id = rec.employee_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            log_error;
      END;
   END LOOP;
END upd_for_dept;

You will need to change the cursor FOR loop to BULK COLLECT so that you can fill up collections with data from the query, which can then be used in the FORALL statement. You will, sadly (due to increased complexity and program length) end up with something like this (cfl_to_bulk_5.sql in demo.zip):

PROCEDURE upd_for_dept (
   dept_in     IN   employees.department_id%TYPE
, newsal_in   IN   employees.salary%TYPE
)
IS
   bulk_errors    EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE employee_tt IS TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;

   employee_ids   employee_tt;

   TYPE salary_tt IS TABLE OF employees.salary%TYPE
     INDEX BY BINARY_INTEGER;

   salaries       salary_tt;

   TYPE hire_date_tt IS TABLE OF employees.hire_date%TYPE
      INDEX BY BINARY_INTEGER;

   hire_dates     hire_date_tt;

   CURSOR employees_cur
   IS
      SELECT     employee_id, salary, hire_date
            FROM employees
           WHERE department_id = dept_in
      FOR UPDATE;

   PROCEDURE fetch_data_quickly (
      limit_in           IN       PLS_INTEGER
    , employee_ids_out   OUT      employee_tt
    , salaries_out       OUT      salary_tt
    , hire_dates_out     OUT      hire_date_tt
   )
   IS
   BEGIN
      FETCH employees_cur
      BULK COLLECT INTO employee_ids_out, salaries_out, hire_dates_out LIMIT limit_in;
   END fetch_data_quickly;

   PROCEDURE adj_comp_for_arrays (
      employee_ids_io   IN OUT   employee_tt
    , salaries_io       IN OUT   salary_tt
   )
   IS
   BEGIN
      FOR indx IN 1 .. employee_ids_io.COUNT
      LOOP
         adjust_compensation (employee_ids_io (indx), salaries_io (indx));
      END LOOP;
   END adj_comp_for_arrays;

   PROCEDURE insert_history
   IS
   BEGIN
      FORALL indx IN employee_ids.FIRST .. employee_ids.LAST SAVE EXCEPTIONS
         INSERT INTO employee_history
                     (employee_id, salary, hire_date
                     )
              VALUES (employee_ids (indx), salaries (indx), hire_dates (indx)
                     );
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            -- Log the error
            log_error
                     (   'Unable to insert history row for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
            /*
            Communicate this failure to the update phase:
            Delete this row so that the update will not take place.
            */
            employee_ids.DELETE (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX);
         END LOOP;
   END insert_history;

   PROCEDURE update_employee
   IS
   BEGIN
      /*
        Use Oracle10g INDICES OF to avoid errors
        from a sparsely-populated employee_ids collection.
      */
      FORALL indx IN INDICES OF employee_ids SAVE EXCEPTIONS
         UPDATE employees
            SET salary = newsal_in
              , hire_date = hire_dates (indx)
          WHERE employee_id = employee_ids (indx);
   EXCEPTION
      WHEN bulk_errors
      THEN
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            log_error
                     (   'Unable to update salary for employee '
                      || employee_ids
                                     (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
                    , SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                     );
         END LOOP;
   END update_employee;
BEGIN
   OPEN employees_cur;

   LOOP
      fetch_data_quickly (100, employee_ids, salaries, hire_dates);
      EXIT WHEN employee_ids.COUNT = 0;
      insert_history;
      adj_comp_for_arrays (employee_ids, salaries);
      update_employee;
   END LOOP;
END upd_for_dept;

Sorry, I know that I should probably explain this code but to be brutally honest my dad had heart bypass surgery and I am currently at the hospital and so you get the abbreviated version ....

Anyway, that is one scenario where you definitely need to convert from the cursor FOR loop.

But Bryn, to my surprise, recommends that you always convert explicitly to BULK COLLECT. Why is that? For performance reasons. Apparently, the cursor FOR loop optimization makes the code run faster, but not as quickly as BULK COLLECT.

In my tests (which you can reproduce in cfl_vs_bulkcollect.sql), I found that BULK COLLECT ran 33% faster than the cursor FOR loop:

Cursor For Loop Elapsed: 8.12 seconds.
Bulk Collect Elapsed: 5.46 seconds.

So if you really want the best performance, take the time and make the effort to switch over to BULK COLLECT.

How to Run an OS Command from PL/SQL

Steven Feuerstein — Wed, 04 Jun 2008 14:11:00 GMT

Oracle doesn't make it terribly easy to run operating system commands from within a PL/SQL block. I suppose that's understandable, given that PL/SQL is an embedded database-oriented language. Still, developers do ask me on a regular basis about how they can do this.

As I understand it, there are basically three ways to do this:

Invoke a Java method from within a PL/SQL wrapper
Call a C program as an external procedure from within PL/SQL.
Use the new DBMS_SCHEDULER package.

I will soon publish a DBMS_SCHEDULER solution (written by Bryn Llewellyn, PL/SQL Product Manager) on my OTN Best Practices column. In the meantime, you will find below a quick review of the steps needed to do this in Java and C.

Executing Host Command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

1. Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.

2. Build a class that invokes that host command method.

Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;
public class execHostCommand
{ 
  public static void execute (String command) 
    throws java.io.IOException
  {
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
  }
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.

3. Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
  PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
           'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.

4. Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges is to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing Host Command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, read Chapter 27 of Oracle PL/SQL Programming, 4th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help from your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

1. Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
   return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.

2. Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll

3. Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".

4. Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib 
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix
     
CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle's CREATE LIBRARY privilege.

5. Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);

Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Testing and refreshing data from production

Steven Feuerstein — Thu, 17 Apr 2008 16:21:00 GMT

As many of my readers likely know by now, I have been working for the past several years on the Quest Code Tester development effort.

Code Tester is the most powerful PL/SQL test automation tool available. You describe the expected behavior of your programs and Code Tester generates your test code, which can then be run from the UI or via a script. With Code Tester, you can build comprehensive regression tests and even implement the Test Driven Development methodology.

In the process of talking about Code Tester with many developers, I have come across a belief regarding code testing and the refreshing of data from production tables that I think actually reflects a misunderstanding about both how to use Code Tester specifically and, more generally, how to test our code.

Here's the way this belief was expressed by a customer recently:

"We have a development environment - let's call it TEST. Inside this environment is the supporting schema / repository for Code Tester, as well as the test definitions and generated test code."

"Once a week, we refresh the TEST environment from the production environment, PROD. PROD doesn't contain a Code Tester repository, so after I refresh, I lose my test repository and code."

Clearly, this developer can do an export of the Code Tester schema and then import it after refresh, but he was concerned about having to add any overhead for the DBA to this refresh process.

Actually, I think the problem goes much deeper than that: if you refresh your test tables with production data on a regular basis, you will find it very difficult indeed to create stable regression tests that can be run against your code.

Why do I say that? Because when you refresh data from production, you change the "inputs" to your programs (contents of the tables) and therefore you will almost certainly have to change the expected results for your tests.

In fact, I think that when it comes to functional testing of your programs (does it meet user requirements?), you should not be refreshing your test tables from production. To understand why I would say this, let's talk about....

How we make sure our programs work

There are as many kinds of tests as there are definitions of what it means for a program to "work." We need to make sure, for example, that our programs meet functional requirements (they are correct) and also that they run quickly enough to avoid user frustration. The programs need to scale up for many users and lots of data, etc.

Quest Code Tester is designed specifically to help you implement tests on functional requirements; in essence, to verify that your program is correct.

To do this, I will almost always want to compare the actual results of running my program with the expected results or control data.

For example, if table XYZ contains a certain set of rows, then after running the program, table ABC should be changed in a specific way. Or the program is a function such that when I pass "ABC" for an IN argument, the function returns 100, and so on.

In general, there is no room for ambiguity here. Either the program works as expected or it does not.

Now, if I want to build regression tests and automate the process of testing my program, I need to able to tell Code Tester that for a given set of inputs, I expect the associated outcomes. And – this is the key thing to realize – those inputs can't keep changing on me. Every time the inputs change, I would need to change the expected outcomes.

Constantly updating one's test code might make sense if you are perform manual tests from hand-coded scripts. But if you want to build comprehensive, serious regression tests, then you need a stable, consistent environment from which to run those tests.

Every time you refresh data from production, you change the values in your table and thus you cannot reliably execute your regression test.

But don't we need real production data to really test?

Is this a big problem? Does this mean that we can't or shouldn't build static, repeatable regression tests for our code? After all (so the thinking goes), we need to test our code against production data to make sure that code handles "real world" situations.

Clearly, our code does need to work properly with production data.

This does not mean, however, that you need to constantly change the data for your functional tests as production data changes. It does mean that the data used in functional tests should represent the variety of data found in production.

In other words, the data in your test tables must be sufficiently varied to allow you to exercise the program to verify all requirements. It doesn't really matter so much that the data is precisely the same as that found in production.

And, again, if you keep changing the test data, you must also change your test definition.

OK, but don’t we need to test against production volume?

There's another problem with basing functionality testing around production data: the data volumes are generally too large, increasing the time it takes to complete the tests.

You certainly do need to make sure your code executes with production volumes of data. But that sort of stress testing should be done independently of your functionality testing. With functionality testing, you want your tests to run as quickly as possible, for these reasons:

You will have lots of (dozens, perhaps hundreds) of separate test cases to run; if each test takes five minutes due to data volume, the test cycle will take an enormous amount of time.
Ideally, you run your tests after each change you make to your program. That way, you can immediately determine if you have introduced any bugs. But if running those tests takes an hour, you will test less frequently and you will get less "return" on your investment of creating your tests.

Conclusion: Segregate your functional test environment

The most important thing to verify about your program is that it is correct: it meets user requirements.

The best way to do this is to build a regression test that you can run after any change to the program, to ensure that it has no bugs.

A regression test should not have to be changed as long as the program itself has not changed. It should work from a consistent set of "inputs" (values for IN arguments, contents of any tables queried by programs, etc.) that do not change. [Of course, you may need to make some changes along the way as program requirements change, as you add more test cases, etc. That is different, however, from daily or weekly refreshes.]

In addition, you want regression tests to run as efficiently as possible. These tests focus on program functionality, not performance. So you want the minimum volume of data in test tables that allow you to cover your requirements. Use different tests to verify adequate performance.

Consequently, when it comes to functionality testing, you should avoid refreshing your test tables from production. Instead, invest the time upfront to come up with setup scripts to populate tables with data that fully exercises your code. Include those setup scripts in your Code Tester test definitions and then you have an independent, consistent test environment.

Auto-formatting of templates for Toad

Steven Feuerstein — Thu, 10 Apr 2008 16:14:00 GMT

A few months ago, I posted on this blog an explanation of how to use Toad's Code Templates to standardize development and improve productivity. I included an XML document that contains over 20 templates that I thought you might find useful. I also asked my readers to produce XML transformations so that the XML document could be "output" in the format that Toad recognizes (and SQL Navigator as well).

I am happy to say that Sean Gilbert has done this for Toad 8 and is working on a transformation for Toad 9.

For more information, check out https://getonthetrail.blogsite.org/Blog/post/2008/04/Transform-Custom-TOAD-XML-Template-with-XSLT.aspx.

Thanks, Sean!

Weird PL/SQL

Steven Feuerstein — Mon, 17 Mar 2008 19:57:00 GMT

Collaborate08, annual conference of the International Oracle User Group, and several other national and international user groups, will be held in Denver this year, from April 13th to the 16^th. I am going to present three papers, including (for the first time) Weird PL/SQL. I thought you might enjoy reading about some of the weirdnesses of PL/SQL in my ToadWorld blog, so here's an excerpt from the beginning of my whitepaper:

Introduction

You probably think that PL/SQL is rather ordinary programming language. Well, it truly is a very powerful and straightforward language, but there are some features "less traveled" which can seem nothing less than weird. This presentation explores some of stranger nooks and crannies of the PL/SQL language, perhaps in the process making them a little bit less weird to the everyday programmer.

All the code I reference in this paper is available at my PL/SQL Obsession site: www.ToadWorld.com/SF. Just click on the "Trainings" link and then click on the "demo.zip" link.

Disclosure: I will poke fun at Oracle, the PL/SQL language, and implicitly the folks who are responsible for building and enhancing PL/SQL. Please know that it is all done in a spirit of deep appreciation for what PL/SQL (and its developers) has done for me and so many others around the world. But, hey, there's always room for improvement and you've got to keep a sense of humor about all this stuff!

Error Codes: Negative or Positive? Make up your mind, Oracle!

I really would have thought that Oracle would have sorted this out by now. Here's the issue:

When you see this string:

ORA-01855

do you interpret the "-" character as a hyphen or a negative sign?

I always considered it to be a negative sign, and that just about every single error code in the world of Oracle is negative. The only exceptions are 1 (user-defined exception) and 100 ("No data found" – which is another little weirdness in PL/SQL. The "No data found" exception has two error codes: 100 and -1403).

Yet sometimes Oracle treats the error codes as positive numbers, most notably when using the SAVE EXCEPTIONS clause of FORALL. When you include SAVE EXCEPTIONS, Oracle "saves up" any exceptions it encounters as it executes all the DML statements specified by the bind array. Then if at least one error occurred, it raises the ORA-24381 exception (Argh! Sometimes I worry about all the little bits of information that are stuck in my head!). It also populates the SQL%BULK_EXCEPTIONS pseudo-collection with all the exceptions that were raised.

Here's an example of a program that uses this feature:

/* bulkexc.sql */
DECLARE
   bulk_errors          EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

   TYPE namelist_t IS TABLE OF VARCHAR2 (1000);

   enames_with_errors   namelist_t
      := namelist_t ('ABC'
                   , 'DEF'
                   , NULL
                   , 'LITTLE'
                   , RPAD ('BIGBIGGERBIGGEST', 250, 'ABC')
                   , 'SMITHIE'
                    );
BEGIN
   FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST SAVE EXCEPTIONS
      UPDATE employees
         SET last_name = enames_with_errors (indx);
   ROLLBACK;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (   'Error '
                               || indx
                               || ' occurred on index '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                               || ' with error code '
                               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                              );
      END LOOP;

      ROLLBACK;
END;
/

And this is what I see when I run the program:

Error 1 occurred on index 3 with error code 1407
Error 2 occurred on index 5 with error code 12899

Hmmm. Oracle returns the error code as a positive number! So if I want to use SQLERRM to look up the error message for that code, I have to multiply it by -1:

FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
   DBMS_OUTPUT.put_line
                      (   'Error '
                       || indx
                       || ' occurred on index '
                       || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                       || ' with error '
                       || SQLERRM
                                ( -1
                                 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
                                )
                      );
END LOOP;

And then I see this output:

Error 1 occurred on index 3 with error ORA-01407: cannot update () to NULL
Error 2 occurred on index 5 with error ORA-12899: value too large for column

It would be nice if Larry would decide once and for all if error codes are positive or negative, and then lay down the law.

SQLERRM and DBMS_OUTPUT.PUT_LINE

Speaking of SQLERRM....let's talk about everybody's favorite program: DBMS_OUTPUT.PUT_LINE. For many, many years – all the way up to the release of Oracle Database 10g Release 2, if you tried to display a string of more than 255 characters with DBMS_OUTPUT.PUT_LINE, that built-in would raise an exception. This caused no end of teeth-gnashing and anguished moans from developers over the year. Fortunately, that restriction was lifted in Oracle Database 10g Release 2 – you can now display up to 32K characters.

But there is still a problem with SQLERRM. We all know about SQLERRM. You call it to return the error message for the current error (obtained by calling SQLCODE). Did you know, however, that Oracle recommends that you not use this function,. and instead call DBMS_UTILITY.FORMAT_ERROR_STACK?

Why would that be? The problem is that SQLERRM may truncate your error message. In earlier versions of Oracle, truncated occurred at 255 characters. Now, it is 512. DBMS_UTILITY.FORMAT_ERROR_STACK, on the other hand, returns strings of up to 2000 bytes.

I can just picture some developer years and years ago, confronted with a nasty problem:

He has found that SQLERRM can return error messages that get quite long, depending on application-specific information, like the names of identifiers. And when you try to display that string with DBMS_OUTPUT.PUT_LINE, an exception is raised.

What's a developer to do?

1. Fix DBMS_OUTPUT.PUT_LINE so it displays longer strings.

or

2. Truncate your error message.

Well, obviously, the solution is to truncate the error message!

Isn't that weird?

I am so glad the PL/SQL team finally got around to fixing DBMS_OUTPUT.PUT_LINE! And according to the PL/SQL development manager at the time, all they had to was change one number!

So...there's a little bit of weirdness in PL/SQL...more to come at Collaborate08 and this blog.

Just how popular is PL/SQL?

Steven Feuerstein — Mon, 04 Feb 2008 18:56:00 GMT

Just how popular is PL/SQL?

And how many PL/SQL developers are there "out there"?

These are surprisingly hard questions to answer. Officially and even unofficially, Oracle Corporation's point people on PL/SQL do not have any idea (or refuse to say) how many PL/SQL developers there are.

I take the approach of doing some rough extrapolations from numbers of books I and other PL/SQL developers have sold, and I conclude that there are perhaps a couple million PL/SQL developers, all told.

That is, however, very unscientific.

There are some websites, however, that offer an analysis of relative popularity of languages, usually based on data they have retrieved from various search sites.

TIOBE

TIOBE compiles a list of relative popularity of programming languages at http://www.tiobe.com/index.htm?tiobe_index.

Here is the list as of January 2008:

Ok, fine, so PL/SQL is not the most popular language. We all know that. What I was very pleased to see is that PL/SQL had increased in popularity over the last year. Based on my experience in India, I can see why; most of the increase probably came from there alone!

Here is some information from the TIOBE website about how they calculate their ratings:

The ratings are calculated by counting hits of the most popular search engines. The search query that is used is: +" programming"
The search query is executed for the regular Google, Google Blogs, MSN, Yahoo!, and YouTube web search for the last 12 months. The web site Alexa.com has been used to determine the most popular search engines.
The number of hits determine the ratings of a language. The counted hits are normalized for each search engine for the first 50 languages. In other words, the first 50 languages together have a score of 100%.

Let's define "hits50(SE)" as the sum of the number of hits for the first 50 languages for search engine SE and "hits(PL,SE)" as the number of hits for programming language PL for search engine SE, then the formal definition of the ratings becomes

((hits(PL,SE1)/hits50(SE1) + ... + hits(PL,SEn)/hits50(SEn))/n
 where n is the number of search engines used.

LangPop

The nice thing about the TIOBE site is that they rank PL/SQL explicitly. All the other language popularity pages I could find simply list "SQL," which I took to mean "SQL and all procedural language extensions to SQL."

The http://www.langpop.com/ site shows the following graph:

O'Reilly Media Popularity by Book Sales

O'Reilly Media, publisher of all my books on PL/SQL, has a research arm ("O'Reilly Radar") that projects programming language trends based on book sales. Again, there is just a single entry for SQL:

http://radar.oreilly.com/archives/2005/04/book_sales_as_a.html

Functions should return data only through the RETURN clause

Steven Feuerstein — Mon, 07 Jan 2008 21:46:00 GMT

You will find below an excerpt from my latest publication: the 2^nd edition of Oracle PL/SQL Best Practices. This edition is a complete rewrite of the 1^st edition. I decided that since software is still, for the most part, written by humans, I would create a cast of characters who write the software I reference and critique in this book.

They work for a company named My Flimsy Excuse, Inc (MFE) and their web site is myflimsyexcuse.com. The mission statement of My Flimsy Excuse is simple and powerful:

Provide a wide array of excuses (flimsy and otherwise) to people in need of a way to explain away questionable behavior.

The MFE founders love technology and don't want to have any flimsy excuses for failure, so they have chosen Oracle as their database technology. They have several different development teams working on various aspects of the web-deployed business plan. The team, which has graciously volunteered to share their experiences with my readers, is made up of the following individuals:

Sunita

The team leader, a very smart woman with a fast, razor-sharp mind. She is always busy, always constructive, and somewhat intimidating. She was a programmer in years past, mostly trained in Fortran and then some C+. She isn't sure if that makes her a better manager or a bigger danger to the team, but she still likes to get her hands "dirty," now and then, writing code.

Delaware

A classic anarchistic, anti-standards, big ego kind of programmer. He has a hard time learning from anyone else, and writes code that is hard to understand....but he is very productive and very bright. If you need a job done overnight (literally), Delaware is the guy to do it. He claims to read up on all the latest features of PL/SQL, but he generally programs in a rut – relying on techniques learned years past in Oracle7. He keeps his thinning hair neatly trimmed, with just a hint of a comb-over, and favors three-piece suits from the Men's Wearhouse.

Lizbeth

The anchor of the team. She used to write in Cobol and is continually shocked at the lack of strong process in the PL/SQL world. She can't understand why programmers today make fun of the Cobol programmers of the past. Didn't they write the software that made the first phase of the Information Revolution a broad success? Lizbeth is methodical and careful, but not, on the whole, the best problem solver—she too easily falls into the trap of seeing things only from her own perspective.

Jasper

The junior member of the team. He is new to PL/SQL and new to MFE. Jasper is eager to learn from anyone and everyone and has nice thick skin, but he is not very creative (not ready to take risks in his code). He always wears jeans, preferably of the distressed variety, complemented by polo shirts with the logos of animals on them. Lizbeth thinks of him as the son she never had, and Delaware treats him like the mascot of the team.

And here is my recommended best practice and explanation for this first blog entry of the new year:

Functions should return data only through the RETURN clause.

Problem: Jasper returns data in a very confusing manner.

Jasper needs a program to retrieve several pieces of information about an excuse: the title, the author and the word count. In just a few moments he builds the following function:

FUNCTION excuse_title (
   excuse_id_in IN mfe_excuse.isbn%TYPE
 , author_out OUT mfe_excuse.author%TYPE
 , word_count_out OUT mfe_excuse.word_count%TYPE)
RETURN mfe_excuse.title%TYPE
IS BEGIN 
   ... implementation unimportant! ... 
END excuse_title;

And then he puts this function to use as follows:

PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
 l_title mfe_excuse.title%TYPE;
 l_author mfe_excuse.author%TYPE;
 l_word_count mfe_excuse.word_count%TYPE;
BEGIN
   l_title := excuse_title (l_id, l_author, l_word_count);
   ...

Jasper is proud that he took the time to put this retrieval logic into its own program. Now it can be used in many places in the application. He shows everyone excuse_title at the weekly code review session. Imagine his dismay when Delaware snorts:

Humph. Well, Jasper, it's absolutely peachy keen that you wrote a reusable function. But that is not a program I would ever want to use. The name says you are giving me a title, and you are, but then you are also passing back all that other stuff. It's self-contradictory and I have to declare a bunch of individual variables to use the darned thing!

Jasper pouts; he was so looking forward to a pat or two on the back. Sunita gives Delaware a dirty look and shakes her head. "Jasper," she says, drawing his attention away from Delaware's gloomy outlook, "hiding the lookup was an excellent move. You just need to take a step or two further, and make sure there are no mixed messages. Let's redesign excuse_title together."

Solution: Return multiple values through a single, composite structure or with a procedure.

Here are the steps that Sunita takes to revamp the lookup function and make it more useful:

Make sure the name of the program reflects what it does: In this case, the program doesn't return just the title; it returns several pieces of information about an excuse. Let's call it the "excuse_info" function.
Pass everything in the RETURN clause: Rather than returning one value through the RETURN clause and another through the parameter list, let's pass everything back in the RETURN clause, using a composite structure—in this case, a record.

Here, then, is the new header of the program:

FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
   RETURN mfe_excuse%ROWTYPE
And here is the revised usage of this function:
PROCEDURE process_excuse (excuse_id_in IN mfe_excuse.isbn%TYPE)
IS
   l_excuse mfe_excuse%ROWTYPE;
BEGIN
   l_excuse := excuse_info (l_id);

Now the code is leaner and cleaner. Everything that is returned by the function is deposited into a single record. And if you are concerned about returning all the data in a table's row, when you need only a small subset of its columns, you can always create your own user-defined record, as shown here:

PACKAGE excuse_pkg
IS
   TYPE key_info IS RECORD (
      title mfe_excuse.title%TYPE
    , author mfe_excuse.author%TYPE
    , word_count mfe_excuse.word_count%TYPE
   );
 
   FUNCTION excuse_info (id_in IN mfe_excuse.id%TYPE)
      RETURN key_info;
END excuse_pkg;

To sum up: the whole point of a function is to return a value (whether it’s a single, scalar value or a composite, such as a record or a collection). If you also return data back through the parameter list with OUT or IN OUT arguments, the purpose and usage of the function will be obscured.

If you need to return multiple pieces of information, take one of the following approaches:

Return a record or collection of values

Make sure to publish the structure of your record or collection (the TYPE statement) in a package specification so that developers can understand and use the function more easily.

Break up a single function into multiple functions, all returning scalar values

With this approach, you can call the functions from within SQL statements.

Change a function into a procedure

Unless you need to call a function to return this information, just change it to a procedure returning multiple pieces of information through the OUT arguments in the parameter list

If you follow these guidelines, your subprograms will be more likely to be used and reused, because they will be defined in ways that make them easy to understand and apply in your own code.

Your function may also then be callable from within a SQL statement, which encourages even wider use of this program. Note, though, that there are restrictions on function calls from SQL. You may not call a function with an OUT argument from within SQL, You also may not call a function that returns a record (the datatypes of all parameters must be SQL-compatible).

Single quotes and CLOBs in 10.2.0.3

Steven Feuerstein — Thu, 20 Dec 2007 00:41:00 GMT

I usually write about best practice principles and general issues.

Today I thought I would share a bit of my pain with you on a very specific topic: single quotes and CLOBs, and specifically on Oracle 10.2.0.3.

I figure that I lost an hour or two of my life to this one, and would like to pass on the warning to you all.

In Quest Code Tester, we store in put and expected values that you provide in your test definition as CLOBs. We maintain "internal" and "external" values – the external value is a string that is ready to be concatenated directly into the generated test code.

Thus, if the input value is one single quote, we save it in the external_value column as

''''

That is, four single quotes – which evaluate to a single quote. Got it so far, right?

When we generate test code, those four single quotes need to be treated as NULL, so we wrote this function to tell us if a CLOB is really to be considered as NULL:

And it worked just fine in all sorts of situations, but then we got a support issue logged by a user. He was getting the wrong test results: Quest Code Tester was showing that he had provided a NULL value when he had instead entered the single quote character!

What the heck was going on?

Well, it took quite a while to narrow things down. The error did not occur on Oracle9i, Oracle11g, on Oracle 10g 10.2.0.1. It only seems to happen on 10.2.0.3.

And after a while of testing and experimenting, I discovered that on this version of Oracle, you get some very strange behavior when performing a test like:

value_in = ''''''

It's easy to see; you can try it yourself. Copy and paste the following block of code into Toad and run it:

You should see this output:

FOUR QUOTES
FOUR QUOTES

And that is wrong, so wrong. It looks like it doesn't matter how many single quote characters you put inside two single quotes; that equality check will always return TRUE.

Ugh.

So I built the following workaround:

And that seems to work reasonably well – though I would much rather NOT have to do this.

Now you know and hopefully you will never run into this issue – but if you do, you have been warned!

Error Management functions and features for PL/SQL Developers

Steven Feuerstein — Wed, 05 Dec 2007 03:41:00 GMT

Winter is descending on Chicago; we had our first snow (flurries, really) on Thanksgiving Day, and the sun is weak. For many, this is a depressing time – and I mean that the lack of that and cold actually does make people feel depressed. Perhaps that is why I found myself thinking negatively – that is, about exceptions in PL/SQL.

So I thought I would share with you some of the features and functions available in PL/SQL, especially those introduced in recent versions of Oracle, which will help you trap and log error information.

For each of the topics below, I describe them briefly and then point to files in my demo.zip archive that illustrate the technique, and also the hyperlink to Oracle documentation on the topic.

You can download my demo.zip archive by clicking here.

Enjoy! And remember: never be sad about Oracle exceptions. Just make sure you have a powerful, general utility for raising, handling and logging those errors. If you don't already have such a thing, then please do download and try the freeware Quest Error Manager. I wrote it myself – just for you!

Save DML errors to a log table instead of raising an exception (Oracle10g)
As soon as the SQL engine raises an exception from processing a DML statement, your executable section terminates. You can, of course, trap the exception, log it, and then keep going, but once an exception is raised, performance degrades terribly.

Use the DBMS_ERRLOG package to define an error log table for your own table. Then when you write your DML statement, include the LOG ERRORS clause. With this approach, Oracle will save DML errors to a log table instead of raising an exception; your program will finish more quickly and then afterwards, you can query the information from the log table and either log the errors in your own system, try to recover or display error information.

My demo.zip files of relevance
dbms_errlog*.*
forall_with_dbms_errlog.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#sthref2860

Save exceptions till the end of your FORALL execution
FORALL is an absolutely, deliriously wonderful enhancement to DML execution introduced in Oracle8i. With FORALL, you can execute multiple DML statements in "bulk," which means they run in a fraction of the time it would take if they ran them on a row by row basis.

As with LOG ERRORS above, you can tell FORALL to continue past any exceptions encountered as it runs each DML operation by adding the SAVE EXCEPTIONS clause. Then if one or more exceptions were encountered, when FORALL is done, Oracle will raise the ORA-24381 error and also populates a "pseudo-collection" called SQL%BULK_EXCEPTIONS with the error code and the index in the collection that raised the error.

My demo.zip files of relevance
cfl_to_bulk*.sql
bulkexc.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2201

Find the line number on which the error was raised (Oracle10g)
Call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to retrieve a string that shows the stack of calls that trace back to the line number and program name from which the most recent error was raised.

My demo.zip files of relevance
backtrace*.sql

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9387

Get the error message
What? You thought you should call the SQLERRM function? No way! Oracle recommends that you not use this function, because it might truncate your error message. Instead, call the DBMS_UTILITY.FORMAT_ERROR_STACK function and it will return the full error message (and sometimes even a stack!).

My demo.zip files of relevance
Sorry, none! It's such an easy, little thing to use...

Oracle documentation reference http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#sthref9392

My Favorite Templates – in one convenient file

Steven Feuerstein — Wed, 28 Nov 2007 20:24:00 GMT

Would you like to increase productivity, improve code quality, and ensure that standards are being followed? Of course you would! And one of the best ways to do that is to rely heavily on Toad's Code Templates (similar functionality exists in SQL Navigator as well).

Code Templates are chunks of code that you can assign a name and description. You then use the name to quickly copy the associated code into your editor. I showed in a previous blog (Don't start with a blank slate!) how you can create a template of your own inside Toad.

In this blog entry I take a different approach: I offer (in the last section of the entry) an XML document that has within it a set of templates that I often use in my own development. The names of all these templates are prefixed with the letters "sf." You can then move these templates into your own template environment, whether in Toad or any other tool. Please see end of this blog entry for a challenge to my readers – you could win a signed copy of my brand new Oracle PL/SQL Best Practices, 2^nd Edition!

My templates range from simple shortcuts (I hate having to type "DBMS_OUTPUT.PUT_LINE" over and over again) to more elaborate, best-practice oriented templates, such as a standardized structure for new packages, procedures and functions. Here is the list of the current contents of this file:

Call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Call DBMS_UTILITY.FORMAT_ERROR_STACK
Call DBMS_UTILITY.FORMAT_CALL_STACK
Call DBMS_OUTPUT.PUT_LINE
Call DBMS_OUTPUT.PUT_LINE to display a Boolean
Sparse Collection Scan: scan the contents of a collection that may be sparse, using a WHILE loop.
A BULK COLLECT query with a LIMIT clause
A standard package format that you can use as a starting point for development of new packages. Contains a standard header, initialization section, procedure and function templates.
A standard procedure format that you can use as a starting point for development of procedures.
A standard function format that you can use as a starting point for development of procedures.
WHEN OTHERS clause that reminds you of all the information you should be gathering when you handle an exception. WHEN OTHERS clause that relies on the Quest Error Manager to gather all necessary data. You decide how many name-value pairs you need.
A FORALL statement that includes SAVE EXCEPTIONS and the code needed to iterate through that pseudo-collection
Standardized approach to documenting workarounds, including what needs to be done to back out the workaround and implement the native, bug-fixed approach.

I encourage you to look through these see which of them you like the best or feel you can use. Then modify them to meet your own standards, and put them to work inside your editor.

Loading these templates into your editor

I chose am generalized XML document format so that these templates could be easily transformed into whatever format is supported by your IDE.

Here is the format I use in my XML document:

Here is an example of one such template, a shortcut to avoid writing the 20 characters needed to ask PL/SQL to "show me" data:

Here is the format used by Toad (from the LexLib.lxl file in the User Files directory):

Obviously, it is different and I am not yet able to offer a utility to convert from my XML format to Toad's format.

But I do offer this challenge and reward: the first person who sends me a utility to convert an XML document with the structure shown above into both Toad's and SQL Navigator's template file formats will receive a signed copy of my brand new Oracle PL/SQL Best Practices, 2^nd Edition. And just to make this fair for those of you that might be reading this from a different time-zone, if you submit your answer and are not the first submission you will be entered in to a drawing to win a copy of my book. Your program must handle templates that have zero or more parameters and multiple lines of code. Please make sure you have tested it fully before passing it along.

Please send your implementation of this utility to me at steven.feuerstein@quest.com.

Thanks in advance!

The template text

Catching up with me at Oracle OpenWorld

Steven Feuerstein — Sun, 11 Nov 2007 18:13:00 GMT

Sorry about the late posting, but if you are interested in attending my presentations, here's the info:

Session ID: S290723
Session Title: Break Your Addiction to SQL!
Track: Database
Room: Grand Ballroom A
Date: 2007-11-12
Start Time: 12:30

Session ID: S290725
Session Title: Collect Yourself: Optimize PL/SQL Code with Collections
Track: Database
Room: Yosemite Room B
Date: 2007-11-12
Start Time: 15:15

Session ID: S290727
Session Title: Test-Driven Development in the World of Oracle PL/SQL
Track: Database
Room: Continental Ballroom 6
Date: 2007-11-13
Start Time: 12:15

In addition, I will be at the OTN Lounge on Tuesday the 13^th (Moscone West) at 4:15 PM. I would love to see you there, and I think we will be raffling off a few of my books – you have very good odds on winning one of these!

Finally, on Tuesday night, I will be at the Quest Toad event (come round the booth #1414 for details) at which I will be signing complementary copies of my newest books (or latest editions, anyway).

Other than that, I will be hanging out at the Quest booth, eager to give demonstrations of Quest Code Tester....