TECHNOLOGY: Ask Tom

On Collaboration, Testing, When, and Why

By Tom Kyte

Our technologist learns from you, is tested on testing, and propagates errors.

I am often asked how I got to know so much about Oracle Database. The answer is quite simple: I learned everything I know because of you—the readers of Ask Tom. It is through the questions—and the research necessary to answer those questions—that my knowledge of the database has grown and continues to grow. I’ve said many times that I learn something new about Oracle Database almost every day, but another thing I’ve learned is that sometimes the best answers come from the readers of Ask Tom, not from me. Below is one of those cases, and it shows how the open forum approach—with the give and take from readers—leads to the best answer possible.

Recently I was asked this question:

I have a varchar2 column in a table, and its values contain only numbers and dots (.). There are never two or more consecutive dots, and the values represent versions. The column data looks like this:

1 1.1 1.2 1.10.2 1.1.1 ...

I want to sort this column by number separated by dots. This is the result I want:

1 1.1 1.1.1 ... 1.2 ... 1.10.2

Is there any easy way to do this? I don’t want to create a custom function to process the column and then sort the values in the SELECT statement, such as SELECT * FROM TableA ORDER BY functionA(ColumnA).

I came up with an initial solution that would work for a fixed number of elements —initially three—in the version field. That number could be extended to four or five or more elements by modification of the query. So I came up with a much more complex query (you can see my original answers at bit.ly/qbBQ4C) that would work for any number of elements (well, up to 100,000 of them, anyway!). It was very generic and solved the problem, but I don’t think anyone would call it a simple or elegant solution.

Enter the readers of Ask Tom. Oracle ACE Laurent Schneider (laurentschneider.com) was the first to comment with an alternative approach. He developed an approach using regular expressions. His solution is in Listing 1.

In real life, you would need only the REGEXP calls in the ORDER BY statement; I’ve included the three calls to REGEXP in the SELECT list only to demonstrate how this works. The first call to REGEXP would take any three-digit number and convert it into a fixed-width, four-character field, as demonstrated in the last line of the output. The second call to REGEXP would take any two-digit number and likewise convert it into a fixed-width, four-character field—adding leading zeros to the number. The third call to REGEXP would convert a single-digit number, placing it in a fixed-width four-character field with leading zeros. That would construct a string of the digits that would sort correctly.

This approach suffered from one drawback: it was limited to three digits per element. If you wanted a fourth digit, you had to add another REGEXP call. Enter Brendan from London. He generalized Laurent’s approach and came up with the solution in Listing 2.

As you can see, sorting by software version (or IP address!) is now pretty easy. And that was just one more new thing I’ve learned about Oracle Database.

Testing

My team runs a large number of Java test cases every night, one after another. All the tests execute against the same schema, and in order to make sure the initial data is known, each test drops and re-creates all the objects in the schema and repopulates the default data. We’re also using an ORM [object relational mapping] to generate much of the DDL [data definition language] code for us. Our schema contains about 150 tables, with no more than a few hundred rows in each table. It takes about 30 minutes to get the schema and test data set up. Most of our test cases execute within a few seconds, aside from the schema preparation, so I’m looking for a way to speed this up, because the preparation limits how many tests we’re able to run each night.

I am wondering if there is a way of quickly reverting a schema to a known state. Many of the test cases use multiple transactions, so we can’t simply roll back. I’ve tried using exp/imp, and my understanding of flashback is that it affects the entire database (not just a single schema), in which case it’s not an option for us.

There are a couple of ways to achieve this. Depending on the number of changes, you might be able to use the flashback table statement: flashback table t1, t2, t3, t4, .... to <point in time>;. If you have a reasonable number of tables, this single statement would use the flashback query capability to place all tables back at that same point in time. Beware, however, that it will be accomplished as one large transaction, so it might generate much UNDO and REDO. Also, it would put the tables back the way they looked logically but not physically. The ordering of the rows on disk would change, the size of the tables might change, and so on. It would enable you to perform functional testing (does the code still work?), but it might cause some queries to run with different performance characteristics after a flashback. See bit.ly/ps056J for details.

Another very viable approach would be to set up the test tablespace(s) and “transport” it/them. By transporting, you would have a copy of the necessary datafiles. Then whenever you wanted to restore the schema data, you would simply drop that tablespace—including its contents and datafiles—and reattach the old datafiles (transport them in). See bit.ly/qSqaYz for details on that approach, which would bypass the UNDO/REDO issue and would restore the data in a manner ensuring that the bits and bytes on disk are identical from test run to test run. This would enable you to perform functional testing and compare query performance from run to run, because you would know that the data on disk is laid out exactly the same each time.

Dynamic Spool Filenames

I am running a SQL script and want to produce a spool file with a name such as script_output||datetime—that is, the filename with the date and time appended to it.

This is pretty easy in SQL*Plus, as long as you know about the NEW_VAL option. With NEW_VAL, you can have SQL*Plus store the last returned value of some column from a query in a substitution variable and then you can use that substitution variable in the SPOOL command. For example

That select statement built the script name, and the COLUMN command had SQL*Plus store the value in a substitution variable named FILENAME. Now you can simply use

SQL> spool &filename

And you are done.

When to Partition

What is a good size—in number of records—for a table partition? Is 200,000 records too small?

It might be 100; it might be 1,000,000. There is no reason to base this decision on the number of rows or even the size of the table.

It all comes down to what are you trying to do and whether partitioning can help.

Let’s say you have a table with 10,000 records in it. Further, assume that there is a status code field in there, you have only two values for that status field, and the values are fairly evenly distributed. Do you frequently run aggregation queries that return one row, and does the predicate always contain the text WHERE status = ? If so, that will require a full scan of the table, but it could require a full scan of only 50 percent of the table data if you partitioned the table by status.

Partitioning is a tool you might use at 100 records and might not use at 1,000,000 records. And vice versa.

So, depending on what you do with those 200,000 records, it may be a really good idea, a really bad idea, or neither good nor bad. It depends. Before you apply the tool that is doing the partitioning, you need to understand the goal for using partitioning in the first place to see if it makes sense.

Why You Really Want to Let Exceptions Propagate

I’ve seen a programming pattern (antipattern is probably more descriptive) that frequently causes a large number of bugs in developers’ code. That programming pattern involves the use of exception handling and many developers’ irrational fear of allowing an exception to propagate out of their code. The fact is that most exceptions should never be caught in PL/SQL, or if they are, they should be immediately reraised. However, in real life, I see the opposite happening in many cases.

That is, they wrap all their code in a WHEN OTHERS exception handler to catch any error, log it using some generic routine, and then output a return code. This is a very wrong way to deal with exception handling—for two main reasons.

The first reason is that it is far too easy for someone who invokes this procedure to ignore the return code. There is nothing forcing the user to check the code, and it is just too easy to forget—especially if you are calling a procedure that “cannot fail” (and anything that cannot fail will almost certainly fail). Return codes are error-prone.

The second reason is far more important. It has to do with the A in the ACID properties of relational databases. The A stands for atomicity. Transactions are atomic in Oracle Database, meaning that either all the statements that constitute the transaction are committed (made permanent) or all of them are rolled back. This atomic protection is extended to individual statements as well. Either a statement entirely succeeds, or it is entirely rolled back. Note that I said that the statement is rolled back. The failure of one statement does not cause previously executed statements to be rolled back. (Their work is preserved and must either be committed or rolled back.) This atomicity extends to anonymous blocks as well.

As you can see, Oracle Database treated the stored procedure call as an atomic statement. The client submitted a block of code—BEGIN P; END;—and Oracle Database wrapped a SAVEPOINT around it. Because P failed, Oracle Database restored the database back to the point right before it was called.

Note: the preceding behavior—statement-level atomicity—relies on the assumption that the PL/SQL routine itself will not perform any commits or rollbacks. In my opinion, COMMIT and ROLLBACK should generally not be used in PL/SQL; the invoker of the PL/SQL stored procedure is the only one who knows when a transaction is complete. It is a bad programming practice to issue a COMMIT or a ROLLBACK in PL/SQL routines you develop.

Now, if I submit a slightly different block, I will get entirely different results:

Here, I ran a block of code that ignored any and all errors, and the difference in outcome is huge. Whereas the first call to P effected no changes, this time the first INSERT succeeds and remains in the database. Oracle Database considered the statement to be the block the client submitted, but this statement succeeded by catching and ignoring the error! Hence, the partial work performed by P was preserved. The reason this partial work was preserved in the first place is that there is statement-level atomicity within P—each statement in P is atomic. P becomes the client of Oracle Database when it submits its two INSERT statements. Each INSERT either succeeds or fails entirely.

I consider virtually all code that contains a WHEN OTHERS exception handler that does not also include a RAISE or RAISE_APPLICATION_ERROR to reraise the exception to be a bug. It silently ignores the error, and it changes the transaction semantics. Catching WHEN OTHERS and translating the exception into an old-fashioned return code changes the way the database is supposed to behave.

In fact, I believe this so strongly that when Oracle Database 11g Release 1 was still on the drawing board and I was permitted to submit three requests for new features in PL/SQL, I jumped at the chance. My first suggestion was simply, “Remove the WHEN OTHERS clause from the language.” My reasoning was simple: the most common cause of developer-introduced bugs I see is a WHEN OTHERS that is not followed by a RAISE or a RAISE_APPLICATION_ERROR. I felt that the world would be a safer place without this language feature. The PL/SQL implementation team could not honor my request, of course, but it did the next-best thing. It made it so that PL/SQL will generate a compiler warning if you have a WHEN OTHERS that is not followed by a RAISE or RAISE_APPLICATION_ERROR call. Listing 4 demonstrates the compiler warning.

So, if you include WHEN OTHERS in your code and it is not followed by a RAISE or a RAISE_APPLICATION_ERROR, be aware that you are almost certainly looking at a bug in the code you developed, a bug placed there by you.

The difference between code with and without a WHEN OTHERS exception block is subtle—and something you must consider in your applications. Adding an exception handler to a block of PL/SQL code can radically change its behavior. The following is a different way to run the P stored procedure, one that restores the statement-level atomicity to the entire PL/SQL block:

Caution: The preceding code represents an exceedingly bad practice! In general, you should neither catch a WHEN OTHERS nor explicitly code what Oracle Database already provides in terms of transaction semantics.

By mimicking the work Oracle Database normally does with the SAVEPOINT in this example, I can restore the original behavior of P—the procedure fails, and SELECT * FROM t returns no rows—while still catching and “ignoring” the error. Note, however, that the correct, “bad-practice free” block of code submitted to the database should simply be

SQL> begin
2 p;
3 end;
4 /

During your code reviews, you should regard all WHEN OTHERS exception blocks with suspicion and really investigate the code if the WHEN OTHERS is not followed by a RAISE or a RAISE_APPLICATION_ERROR!

Tom Kyte is a database evangelist in Oracle’s Server Technologies division and has worked for Oracle since 1993. He is the author of Expert Oracle Database Architecture (Apress, 2005, 2010) and Effective Oracle by Design (Oracle Press, 2003), among other books.