Steven Feuerstein's Blog

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.