On Collaboration, Testing, When, and Why

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

November/December 2011

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

Here is my test data:

create table t ( col varchar2(75) );
insert into t values ('1');
insert into t values ('1.1');
insert into t values ('1.1.1');
insert into t values ('1.1.2');
insert into t values ('1.2');
insert into t values ('1.2.4');
insert into t values ('1.2.5');
insert into t values ('1.2.10');
insert into t values ('1.10.1');
insert into t values ('1.10.2');
insert into t values ('2');
insert into t values ('2.1');
insert into t values ('22.333.1' )

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.

Code Listing 1: First regular expression solution for sorting numbers and dots

SQL> select col,
  2     regexp_replace
  3        (col,'(^|\.)([[:digit:]]{3})','\1 \2') p1,
  4     regexp_replace(
  5     regexp_replace
  6        (col,'(^|\.)([[:digit:]]{3})','\1 \2'),
  7             '(^|\.)([[:digit:]]{2})','\1 0\2') p2,
  8    regexp_replace(
  9     regexp_replace(
 10     regexp_replace
 11        (col,'(^|\.)([[:digit:]]{3})','\1 \2'),
 12             '(^|\.)([[:digit:]]{2})','\1 0\2'),
 13             '(^|\.)([[:digit:]])','\1 00\2') p3
 14    from t
 15   order by
 16    regexp_replace(
 17     regexp_replace(
 18     regexp_replace
 19        (col,'(^|\.)([[:digit:]]{3})','\1 \2'),
 20             '(^|\.)([[:digit:]]{2})','\1 0\2'),
 21             '(^|\.)([[:digit:]])','\1 00\2');
COL                   P1               P2               P3
--------              --------         ---------        -------------
1                     1                1                001
1.1                   1.1              1.1              001. 001
1.1.1                 1.1.1            1.1.1            001. 001. 001
1.1.2                 1.1.2            1.1.2            001. 001. 002
1.2                   1.2              1.2              001. 002
1.2.4                 1.2.4            1.2.4            001. 002. 004
1.2.5                 1.2.5            1.2.5            001. 002. 005
1.2.10                1.2.10           1.2. 010         001. 002. 010
1.10.1                1.10.1           1. 010.1         001. 010. 001
1.10.2                1.10.2           1. 010.2         001. 010. 002
2                     2                2                002
2.1                   2.1              2.1              002. 001
22.333.1              22. 333.1        022. 333.1       022. 333. 001
13 rows selected.

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.

Code Listing 2: Generalized regular expression solution for sorting numbers and dots

SQL> SELECT col,
  2         RegExp_Replace
  3         (col || '.', '(\d+\.)', '00000000\1') p1,
  4         RegExp_Replace(
  5         RegExp_Replace
  6         (col || '.', '(\d+\.)', '00000000\1'),
  7                      '0+(........)\.', '\1') p2
  8    FROM t
  9   ORDER BY
 10    RegExp_Replace(
 11    RegExp_Replace
 12    (col || '.', '(\d+\.)', '0000000\1'),
 13                 '0+(........)\.', '\1');
COL                P1                                 P2
-------            ------------------------------     -----------------------
1                  000000001.                         00000001
1.1                000000001.000000001.               0000000100000001
1.1.1              000000001.000000001.000000001.     000000010000000100000001
1.1.2              000000001.000000001.000000002.     000000010000000100000002
1.2                000000001.000000002.               0000000100000002
1.2.4              000000001.000000002.000000004.     000000010000000200000004
1.2.5              000000001.000000002.000000005.     000000010000000200000005
1.2.10             000000001.000000002.0000000010.    000000010000000200000010
1.10.1             000000001.0000000010.000000001.    000000010000001000000001
1.10.2             000000001.0000000010.000000002.    000000010000001000000002
2                  000000002.                         00000002
2.1                000000002.000000001.               0000000200000001
22.333.1           0000000022.00000000333.000000001.  000000220000033300000001
13 rows selected.

As you can see, that solution works in a similar manner: converting the elements into fixed-width fields and then trimming them all to the same length, resulting in a string that is again sortable.

Another frequent Ask Tom reader who goes by the name Sokrates then posted a nice use case for this query—against one of the Oracle Database V$ tables—as shown in Listing 3.

Code Listing 3: Use case for regular-expression sorting solution

SQL> SELECT optimizer_feature_enable, bugno,
  2         description
  3    FROM v$system_fix_control
  4   ORDER BY
  5   RegExp_Replace (
  6   RegExp_Replace
  7   (optimizer_feature_enable || '.', '(\d+\.)', '0000000\1'),
  8                                     '0+(........)\.', '\1'),
  9   bugno
 10  /
OPTIMIZER      BUGNO  DESCRIPTION
---------  ---------  -------------------------------------------------
             2194204  disable push predicate driven by func. index into
                      partition view
...
8.0.0        2663857  Use extended index caching discount
...
8.0.0        9785632  disallow slave group reuse in parallel query
8.1.6        1403283  CBO do not count 0 rows partitions
8.1.7        2324795  add(remove) cluster index for push view
8.1.7        2660592  do not trigger bitmap plans if no potential domain index
                      driver
9.2.0        2320291  push into table with RLS
9.2.0        2492766  use OR'ed predicates in index filter
...
9.2.0.8      3118776  Check for obj# for named view estimated card
9.2.0.8      4904838  allow index skip scan with no index keys
9.2.0.8      5005866  remove null first element from multicolumn inlist if
                      possible
...
10.1.0       3056297  No selectivity for source transitive equality join
                      predicates
10.1.0       3151991  use cost cutoff for first_rows
10.1.0       4550003  do not consider no sel predicates in join selectivity
                      sanity
10.1.0.3     3120429  account for join key sparsity in computing NL index
                      access cost
10.1.0.5     4308414  outer query must have more than one table unless lateral
                      view
10.1.0.5     4569940  Use index heuristic for join pred being pushed
10.2.0.1     3335182  use fkr_1 for (NOT) EXISTS subquery
...
11.1.0.6     4168080  Eliminate unneeded bitmap conversion
...
11.1.0.7     8557992  Enhance functional index checks when considering
                      OR-expansion
11.2.0.1      399198  ORDER BY sort elimination with OR expansion
..
11.2.0.2     9912503  Remove having clause subquery at all levels
551 rows selected.

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.

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.

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

SQL> column filename new_val filename
SQL> select 'my_script_name_' ||
to_char(sysdate, 'yyyymmdd' ) filename
from dual;
FILENAME
-------------------------------
my_script_name_20110624

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.

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.

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.

Developers often code something like this:

procedure p (  ..., return_code
in out number )
begin
   return_code := 0;
   ...
exception
when others then
   log_the_error;
   return_code := -1;
end;

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.

Consider this table and stored procedure:

SQL> create table t ( x int check
( x>0 ) );
Table created.
SQL> create or replace procedure p
  2  as
  3  begin
  4       insert into t values ( 1 );
  5       insert into t values (-1 );
  6  end;
  7  /
Procedure created.

So, you have a procedure you know will fail, and the second INSERT will always fail in this case. Let’s see what happens if I run that stored procedure:

SQL> begin
  2      p;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.SYS_C0018095) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 2
SQL> select * from t;
no rows selected

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:

SQL> begin
  2     p;
  3  exception
  4     when others then
  5        dbms_output.put_line(
'Error!!!! ' || sqlerrm );
  6  end;
  7  /
Error!!!! ORA-02290: check constraint
(OPS$TKYTE.SYS_C0018095) violated
PL/SQL procedure successfully completed.
SQL> select * from t;
         X
-------------
         1

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.

Code Listing 4: Missing RAISE or RAISE APPLICATION ERROR returning compilation warnings

SQL> alter session set
  2  PLSQL_Warnings = 'enable:all'
  3  /
Session altered.
SQL> create or replace procedure some_proc( p_str in varchar2 )
  2  as
  3  begin
  4          dbms_output.put_line( p_str );
  5  exception
  6    when others
  7    then
  8      -- call some log_error() routine
  9      null;
 10  end;
 11  /
SP2-0804: Procedure created with compilation warnings
SQL> show errors procedure some_proc
Errors for PROCEDURE P:
LINE/COL  ERROR
--------  -----------------------------------------------------------------
1/1       PLW-05018: unit SOME_PROC omitted optional AUTHID clause; default
          value DEFINER used
6/8       PLW-06009: procedure "SOME_PROC" OTHERS handler does not end in
          RAISE or RAISE_APPLICATION_ERROR

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:

SQL> begin
  2     savepoint sp;
  3     p;
  4  exception
  5     when others then
  6        rollback to sp;
  7        dbms_output.put_line(
 'Error!!!! ' || sqlerrm );
  8  end;
  9  /
Error!!!! ORA-02290: check constraint
(OPS$TKYTE.SYS_C0018095) violated
PL/SQL procedure successfully completed.
SQL> select * from t;
no rows selected

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!