SQL Developer: Install Unit Testing Repository Get the latest SQL Developer release here.

I'm not a big tools guy, I prefer SQL*Plus to anything else. I especially don't like paying for tools (yes, the database is a tool and costs a lot of money...I do realize the hypocrisy).

After Syme Kutz's presentation at SOUG, I've been looking more closely at SQL Developer. I've been using it (and JDeveloper) since they were both made freely available a few years ago. Mostly for the schema browsing, looking around, importing and exporting data. I do use it (SQL Developer) to write reports that I can share with the Business folks as well.

Syme's presentation was primarily on Unit Testing (which I begged for). First step to using Unit Testing is to install the repository, a set of tables the application uses to build and store tests and their results.

You need to have version 2.1 or greater.

First up, go to Tools --> Unit Test --> Select Current Repository



You'll be prompted to select a connection (i.e. database) to use



Would you like to create one now? Select Yes.



You're then told the the required roles do not exist, select OK.



Confirm running SQL



Running...will take just a few seconds



Success!



That's it. Easy right? Future posts will detail managing users and creating tests.

Labels: howto, sql developer, testing

  ¶ 3/08/2010 08:41:00 AM   0 Comments Links to this post

  SOUG: SQL Developer with Syme Kutz Tonight was the Suncoast Oracle User Group (SOUG) meeting with Syme (pronounced Sim-e, I thought it was Si-me) Kutz of Oracle presenting on SQL Developer, mainly the new Unit Testing functionality.

Unfortunately, I missed the first half of the meeting due to a flight delay, but from what I did see, it's very cool. If you read the announcement last week, you'll remember that Kris Rice had offered up (aka - threw under the bus) Syme. I made first contact and then passed the baton to our meeting coordinator who finalized the arrangement.

If you want to check out the Unit Testing features, you need the latest release (2.1), which can be found here. To access it, go to Tools --> Unit Testing



I won't go into gory details simply because I need to use the dang thing first. I'm sure I'll have some posts in the near future.

Anyway, what I did see was pretty slick.

Syme then gave us some history of the product (developed originally by himself and Mr. Rice) and explained a bit more about some of the functionality. Many of you already know about the integration with APEX (I don't know much, other than it exists). That's about to be expanded and will give even more control over many aspects of APEX, including some pretty tight integration with the Unit Testing module.

One really cool thing that he mentioned, if you open up a trace file in SQL Developer, you get a pretty report for it. Apparently reverse engineered from tkprof.

First, find your trace file:



Double click it to open it and you'll see something like this (you'll have to click through on this one):



I will break it down if you're too lazy though.

The first column of the report is the SQL:



Next up are the statistics:



Waits:



and finally Row Sources:



Pretty slick stuff.

Thanks Syme for coming down, hopefully we can get you down here again to show us the rest.

Labels: soug, sql developer, testing

  ¶ 2/26/2010 12:47:00 AM   2 Comments Links to this post

  SOUG: SQL Developer Unit Testing This week the Suncoast Oracle Users Group will be hosting Syme Kutz, Senior Architect for Database Tools at Oracle.

Update
I received Syme's bio (from Syme of course). It's pretty impressive...

I started working at Oracle in October of 1995 in the Systems Performance Group of Consulting under Cary Millsap.

I then spent 8 years tuning the Database and Oracle Applications. My Experience tuning application lead me to work with Max Schierson fixing and Tuning iStore. I left consulting and moved into Applications It working for Max at Headquarters were our focus was building custom applications to better facilitate oracle policies and programs. I built custom applications until a position opened up on APEX the development team. After rebuilding the Database management side of APEX, Kris Rice and I began the Sql Developer project. When the group split and Sql Developer became a product I followed. I have been a developer on Sql Developer since then building various functionality, such as reports and Unti Testing.

We "found" Syme through Kris Rice who unceremoniously offered his services up on Twitter.



After much wrangling and negotiating, we finally managed to talk Syme into coming down from Orlando.

According to Kris, Syme was heavily involved with the new Unit Testing functionality of SQL Developer. We all know how much testing us database folks do, so it makes perfect sense right?

Anyway, if you're in town and Thursday, please come by and check out Syme's presentation, it should be very interesting.

Labels: soug, sql developer, testing

  ¶ 2/21/2010 11:12:00 PM   3 Comments Links to this post

  "Shrink" UNDO Tablespace Having completely screwed up my sandbox database, I decided to do a little house cleaning. Yes, I know, I would probably break something. But I have backups now.

Specifically, I wanted to reclaim some space. More specifically, I didn't like the UNDO data file being 6GB. Completely unnecessary.



So I found this thread on how to "shrink" it.

1. Create a new temporary UNDO tablespace.
2. Point your database to the new UNDO tablespace.
3. Drop the original UNDO tablespace.
Optional
4. Create new UNDO tablespace matching the original.
5. Point your database to the old new UNDO tablespace.
6. Drop the new old UNDO tablespace.
7. Remove the old files from the filesystem

--STEP 1
CREATE UNDO TABLESPACE undotbs2 DATAFILE 'UNDOTBS_02.DBF'
  SIZE 1G
  AUTOEXTEND ON;

--STEP 2
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

--STEP 3
DROP TABLESPACE undotbs INCLUDING CONTENTS;

--STEP 4
CREATE TABLESPACE undotbs DATAFILE 'UNDOTBS_01.DBF'
  SIZE 1G
  AUTOEXTEND ON;

--STEP 5
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs;

--STEP 6
DROP TABLESPACE undotbs2 INCLUDING CONTENTS;

And then you can remove the old datafiles.

Just a reminder, this is a sandbox used for testing, not a production database.

Labels: dba, testing

  ¶ 9/25/2009 03:54:00 PM   3 Comments Links to this post

  Testing: FLASHBACK, Data Pump and RMAN I've been having loads of fun the last few days, Learning By Breaking, Learning By...Breaking?, IMPDP and REMAP_TABLESPACE and finally How To: Clean Your Schema (which needs a little work).

All of this in an effort to test my migration script with changes in the hundreds. A quick recap:

1. Import metadata from production environment from 4 schemas. This includes mapping all the tablespaces from production to USER in my sandbox.
2. Create restore point.
3. Run build script.
4. Check for errors, fix errors
5. Flashback database to pre-deployment state.
6. Rinse and repeat as necessary.

If you have read any of those other posts, you'll know that:

a. I'm an idiot.
b. I like to guess.
3. I performed my first "recovery."
d. I learned the basics of Data Pump.
e. I like to break things.

For #1 above, import metadata, I've learned some hard lessons. I've had to repeat this step a number of times because I've either corrupted my database or dropped the restore point before flashing back.

And just now, a colleague of mine helped me out with another problem. See, the import process was extremely slow. Part of the reason (I think) I corrupted the silly thing was because I was mucking around at a level I don't quite understand...storage.

I just sent this pic to him:



I was like, "WTF? Why do I need a 33GB datafile for metadata?"

Him: "Ummm...idiot...it preallocates the space which is based on production."

Me: <crickets>

Me: "How can I change the storage characteristics?" (I sent him the link to the Data Pump docs.)

Me: "How about this?" (Link to the TRANSFORM clause)

Aha...TRANSFORM has 4 options:
1. SEGMENT_ATTRIBUTES (Y, Default, to keep them, N to toss 'em)
2. STORAGE (Y, Default, to keep them, N to toss them)
3. OID
4. PCTSPACE

Where does RMAN fit into all of this? I'm not really sure. Last night I issued

RECOVER DATABASE;

and it worked perfectly. Now, once I get an import completed, I take a backup of the tablespace. (I've since created a separate tablespace for the 2 largest schemas being imported.) That way, if I drop the restore point before flashing back, I should be able to restore it back to it's original state.

One of these days I'll get around to virtualizing all of this. I imagine that has to be easier, import the data, take a snapshot, run the script, fix, revert to previous snapshot. If I did that though, I wouldn't get to play (learn) with all of these cool tools.

Labels: datapump, flashback, rman, testing

  ¶ 9/23/2009 05:42:00 PM   0 Comments Links to this post

  How To: Clean Your Schema I'm going to be testing my migration script in my personal sandbox. I just want to make sure I have the exact order of execution for all the objects before sending it on to the DBA.

In that regard, I had to clean up my existing schemas. By "clean up" I mean remove all of the objects. Laurent Schneider posted something very similar last year, but it didn't handle scheduling objects (there is a reference to scheduled jobs in the comments though).

So here's my version which handles TYPES, JOBS (Scheduler), PROGRAMs, CHAINs and TABLEs. There are probably more cases that I did not catch, but this is the most I have come across so far.

DECLARE
  l_string VARCHAR2(4000);
  l_execute BOOLEAN := TRUE;
BEGIN
  FOR i IN ( SELECT DISTINCT
               owner, 
               ( CASE
                   WHEN object_type = 'PACKAGE BODY' THEN 'PACKAGE'
                   ELSE object_type
                 END ) object_type, 
               object_name
             FROM dba_objects
             WHERE owner IN ( 'MY_SCHEMA' )
              AND object_type IN ( 'SEQUENCE', 'VIEW', 'SYNONYM', 
                                   'PROCEDURE', 'FUNCTION', 'PACKAGE', 
                                   'JAVA SOURCE', 'JAVA CLASS', 'TYPE',
                                   'JOB', 'SCHEDULE', 'PROGRAM',
                                   'JAVA RESOURCE', 'CHAIN', 'TABLE' )
             ORDER BY owner, object_name )
  LOOP
    l_string := 'DROP ';
    l_string := i.object_type || ' ';
    l_string := i.owner || '."';
    l_string := i.object_name || '"';
    CASE
      WHEN i.object_type = 'TABLE' THEN
        l_string := l_string || ' CASCADE CONSTRAINTS';
      WHEN i.object_type = 'TYPE' THEN
        l_string := l_string || ' FORCE';
      WHEN i.object_type = 'JOB' THEN
        l_execute := FALSE;

        dbms_scheduler.drop_job
          ( job_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'SCHEDULE' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_schedule
          ( schedule_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'PROGRAM' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_program
          ( program_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      WHEN i.object_type = 'CHAIN' THEN
        l_execute := FALSE;
        
        dbms_scheduler.drop_chain
          ( chain_name => i.owner || '."' || i.object_name || '"',
            force => TRUE );
      ELSE
        NULL;
    END CASE;
    
    IF l_execute THEN
      EXECUTE IMMEDIATE l_string;
    ELSE
      l_execute := TRUE;
    END IF;
  END LOOP;
END;
/

Enjoy.

Labels: development, howto, testing

  ¶ 9/17/2009 01:47:00 PM   0 Comments Links to this post

  PL/SQL: Exceptions I'm not really sure what I learned today, yet. Here's what I went through though.

I'm rebuilding/redesigning/refactoring a payment processing platform. It's complete with WHEN others...there is logging after the WHEN others, but no RAISE.

I was taught to use exceptions, which to me, meant using

raise_application_error( -20001, 'something went wrong' )

which meant that my calling PL/SQL had to use the PRAGMA EXCEPTION_INIT declaration. Not a big deal when it's 1 or 2 layers deep, but that's part of today's lesson (for me).

Exceptions were used in the code, they were slightly different though, just the

DECLARE
  some_exception EXCEPTION;
BEGIN
...

variety. It's way better than nothing and I believe they were headed in the right direction.

As I peel away the layers though, far too many errors are being caught with OTHERS. Bad. Bad. Bad.

Payment processing, being at the center of most everything, should, ney, must, blow up loudly if something unknown goes wrong. Before that ever goes live you should know about the vast majority of exceptions. Vast Majority to me means 99.9%.

By blowing up loudly, you don't have to rely on looking through error logs and you are far less likely to encounter strange behavior. If one pops up that you didn't account for, it's a quick code change to add that handling.

Of course much of this is predicated on having unit tests or other testing means available. Once of the first things I did was build about 80 test cases with SQLUnit. So I am fairly confident when I make changes that I haven't affected (much) the underlying code.

Finally, on to the exceptions.

There were 4 or 5 generic exceptions (other than OTHERS) defined. I wanted more though. So I began adding them in. Currently the code travels through about 5 levels of the candy cane forest, I mean, PL/SQL. In the lower most level, I used

raise_application_error( -20001, 'invalid card number (gateway)' );

Reran the tests and nothing showed up. I added an internal function to capture the error stack.

  FUNCTION error_stack RETURN VARCHAR2
  IS
    l_error_stack VARCHAR2(32767);
  BEGIN
    l_error_stack := dbms_utility.format_call_stack;
    l_error_stack := l_error_stack || dbms_utility.format_error_backtrace;
    l_error_stack := l_error_stack || dbms_utility.format_error_stack;
    
    RETURN l_error_stack;
  END error_stack;

so I wouldn't have to rewrite those 3 (long) lines over and over. I realize that you get an extra line in there, but I'll know to ignore it.

Rerun the tests and I can see the call stack with a reference to ORA-20001. I'm getting somewhere. That's when I realized that even if you throw an exception in that manner, if you have an exception block in the same block of code and a WHEN others, WHEN others will catch it. For some reason, I always thought it bypassed that current block of code, but then again, I've rarely used WHEN others.

One by one I began to remove the WHEN others from the calling layers. I created global exceptions:

invalid_card EXCEPTION;
PRAGMA EXCEPTION_INIT( invalid_card, -20001 );

, removed WHEN others and created a new exception block in the top-most procedure. Perfect!

I rerun the tests and the error propogates all the way to the top (as it should, I just wasn't used to it). Tests begin to work again and I'm all set to go. Win!

For more on exception handling in PL/SQL, go here for 10gR2, here for 11gR1, and here for 11gR2.

Labels: plsql, sqlunit, testing

  ¶ 9/15/2009 05:02:00 PM   0 Comments Links to this post

  REGEXP_REPLACE - Credit Card (CC) Numbers I'm starting to rewrite some of our payment processing stuff right now. One thing I want to get a handle on is what is being sent to the payment gateway (we're capturing the response, but the request isn't easily accessible).

So I created a table and I'm writing the REQUEST URL to it. Ran my first test, hey look, there is the credit card number. That won't work.

I then started to think about how to mask just the credit card number...I could look for the key(word) in each URL string, but not all gateways are the same. Regular Expressions!

A nice 5 year old example from Alice Rischert can be found here. Following that it was fairly easy to figure out, specifically the example (REGEXP_INSTR) on finding the Zip code.

[[:digit:]]{13,16}

That's it. That's all there is too it and it's applicable to each and every gateway. You could have some false positives if you're passing through 13 to 16 digit numbers, but this is essentially a logging table, so who cares. If I need to see the exact credit card number, I can look it up through the usual process (access, decrypting, etc).

I ran a bunch of tests to see how it would work:

VAR C VARCHAR2(4000);

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r 
FROM dual;

I'm starting with the easy cases, numbers, 13 to 16 digits long:

CJUSTICE@TESTING>EXEC :C := 1234123412341234;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--15
CJUSTICE@TESTING>EXEC :C := 123412341234123;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--14
CJUSTICE@TESTING>EXEC :C := 12341234123412;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--13
CJUSTICE@TESTING>EXEC :C := 1234123412341;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

Now some of the negative cases, still just numbers:

CJUSTICE@TESTING>--17
CJUSTICE@TESTING>EXEC :C := 12341234123412341;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1

CJUSTICE@TESTING>--18
CJUSTICE@TESTING>EXEC :C := 123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X12

CJUSTICE@TESTING>--19
CJUSTICE@TESTING>EXEC :C := 1234123412341234123;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X123

CJUSTICE@TESTING>--20
CJUSTICE@TESTING>EXEC :C := 12341234123412341234;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1234

CJUSTICE@TESTING>--30
CJUSTICE@TESTING>EXEC :C := 123412341234123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
XX

So any number over 16 is not masked. For my purposes, I don't much care.

Numbers are easy right? Let's start adding some strings in with the Credit Card number embedded in there.

CJUSTICE@TESTING>SET DEFINE OFF
CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X

CJUSTICE@TESTING>EXEC :C := 'test=bollocks&card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
test=bollocks&card_number=X

CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234&test=bollocks';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X&test=bollocks
CJUSTICE@TESTING>SET DEFINE ON

This is more for me than it is for you (another reason you should blog). I always forget how to use Regular Expressions since I encounter the opportunity to use them so rarely.

Labels: regexp_replace, sql, testing

  ¶ 8/07/2009 05:25:00 PM   0 Comments Links to this post

  SQLUnit: Database Unit Testing What feels like a thousand years ago I used SQLUnit to do create and perform unit tests on database stored procedures. Lots of them. To the point where that's almost all I was doing. I both hated it and loved it. Hated it because it was boring, tedious, mind-numbing work. Loved it because it gave me confidence in the code I was writing and, once all the tedium was done, could be done quickly. A small change could be immediately tested to see it's effect.

Something else happened too, I began to write code thinking about the tests. Every single line of code was a potential test. If I do this, how will I test it? If I do that, how will I test it? Where do the tests end? You could theoretically test forever...and then I would inevitably fall down that rabbit hole.

Like I was saying, it changed the way I wrote code. Test Driven Development without writing the tests first (which I can hardly stand). That was a good thing because I began to anticipate certain errors. On an INSERT statement with a sequence generated Primary Key, should I trap the exception in code and give it my own error number? Or just let the ORA-00001 propagate up? I just let the Oracle error come up, no need to create my own for that. There's something seriously wrong if you get an error in that case and the whole world should know about it.

Speaking of exceptions, use them, throw them, don't trap them (mostly) and do something else. Log it and then RAISE it. I think it's good for every single developer to know when something is messed up. I've spent far too much time trying to debug something because all the errors are being trapped and processing just continues. I've been lucky on some occasions to have error logging, at least...

What is SQLUnit?

SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Unfortunately it has not been worked on in almost 3 years, but I'd say it's in pretty good shape. There's support for Procedures, Functions, Ref Cursors and User Defined Types (though I haven't tried this out yet). The current version is 5.0. The last version I worked on consistently was 1.3 I believe. A colleague (who now works for Oracle) from my early days of SQLUnit testing even contributed some Oracle specific items.

I used it to build the database for CABEZE, my first, unsuccessful, try at my own business. It was nice because I was building it from scratch, so I could build all the test data along with it (no testing with production data...though there wasn't really much of that anyway), set up the database (create test data), run the tests and then tear it down back to it's original state (empty). Unfortunately the systems I've worked on since weren't empty and testing with production, or semi-production (cleansed) data was the only viable alternative.

Which brings me to now. I'm trying to reacquaint myself with the tool and writing test cases for various procedures. I ran into an issue create test cases because all of our credit card numbers were scrambled. Everything failed with an Invalid Card Number. Barnacles.

Why not create a routine that would generate "real" credit card numbers, more specifically, numbers of the appropriate length that had a check digit? So credit cards use the Luhn formula which is supposed to prevent certain transposition errors.

The Luhn algorithm will detect any single-digit error, as well as almost all transpositions of adjacent digits. It will not, however, detect transposition of the two-digit sequence 09 to 90 (or vice versa). It will detect 7 of the 10 possible twin errors (it will not detect 22 ↔ 55, 33 ↔ 66 or 44 ↔ 77).

With CABEZE, I had written my own PL/SQL card number generator (and validator), but I didn't blog at the time and seem to have lost that code. So I tried writing it again.

Since I'm using SQLUnit, what a great way to demonstrate the power of the tool. It's a somewhat complex (to me anyway) formula, so writing up tests for it should help me to figure it out. Here's the code to create a check digit based on the Luhn formula.

CREATE OR REPLACE
FUNCTION create_check_digit( p_card_number IN NUMBER ) RETURN NUMBER
IS
  TYPE t_digits IS TABLE OF INTEGER;
  l_table T_DIGITS := T_DIGITS();
  l_count INTEGER := 0;
  l_num INTEGER;
  l_digit INTEGER;
  l_odd INTEGER := 0;
  l_even INTEGER := 0;
  l_sum INTEGER := 0;
  l_check_digit INTEGER;
BEGIN
  IF p_card_number IS NULL THEN
    raise_application_error( -20001, 'you must provide a card number' );
  END IF;

  FOR i IN REVERSE 1..LENGTH( p_card_number ) LOOP
    l_count := l_count + 1;
    l_table.EXTEND(1);
    l_table( l_count ) := SUBSTR( p_card_number, i, 1 );
  END LOOP;

  FOR i IN 1..l_table.COUNT LOOP
    l_digit := l_table(i);
    IF MOD( i, 2 ) > 0 THEN
      l_num := l_digit * 2;
      IF l_num > 9 THEN
        FOR i IN 1..LENGTH( l_num ) LOOP
          l_odd := l_odd + SUBSTR( l_num, i, 1 );
        END LOOP;
      ELSE
        l_odd := l_num;
      END IF;
      p( 'odd:  ' || l_odd );
    ELSE
      l_even := l_digit;
    END IF;
    l_sum := l_sum + l_odd + l_even;
    p( 'l_sum:  ' || l_sum );
    l_odd := 0;
    l_even := 0;
  END LOOP;
  l_check_digit := ABS( ( CEIL( MOD( l_sum / 10, 10 ) ) * 10 ) - l_sum );
  p( 'check digit:  ' || l_check_digit );
  p( 'l_sum:  ' || l_sum );
  p( p_card_number || l_check_digit );
  RETURN l_check_digit;
END create_check_digit;
/

I have no doubt it could be done easier, specifically using regular expressions. This is just my first go at it...so don't give me a hard time...any other solutions are welcome in the comments. ;)

Here's the output of my final test run:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (125ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (15ms)
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (16ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (15ms)
[sqlunit] Running test[6]: RANDOM (1) NUMBER (5) (0ms)
[sqlunit] Running test[7]: RANDOM (2) NUMBER (55) (0ms)
[sqlunit] Running test[8]: RANDOM (3) NUMBER (557) (16ms)
[sqlunit] Running test[9]: RANDOM (4) NUMBER (5579) (0ms)
[sqlunit] Running test[10]: RANDOM (5) NUMBER (65579) (0ms)
[sqlunit] Running test[11]: RANDOM (14) NUMBER (12345678965579) (16ms)
[sqlunit] Running test[12]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...

I was able to run a variety of different tests in seconds. A previous test run looked like this:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (109ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (109ms)
[sqlunit] Assertion "outparams-equal" failed (6(NUMERIC) != 1(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   6
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   1
[sqlunit] 
[sqlunit]
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (0ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (0ms)
[sqlunit] Assertion "outparams-equal" failed (2(NUMERIC) != 0(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   2
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   0
[sqlunit] 
[sqlunit]
[sqlunit] Running test[6]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...
[sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0
[sqlunit] SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0

I could then identify the problem, fix it, and run the tests within seconds. How cool is that?

That particular run helped me realize that if I passed in a string of numbers that was even, it would give me the wrong result. If you look at Visa for instance, it's typically 16 digits long with the final digit being the check digit. You start at the rightmost digit before the check digit and go backwards. That was the impetus behind putting the digits into the PL/SQL TABLE OF INTEGERS up above.

I'll post more on creating validly formatted credit card numbers later, I just wanted to use it as an example for SQLUnit.

Try it. Use it. Let me know how it goes. Maybe we can get some Java guy to get it started up again.

Labels: sqlunit, testing

  ¶ 6/11/2009 10:24:00 PM   8 Comments Links to this post

  Index those Foreign Keys I've been reading about this phenomenon for years over on asktom, but I had never actually encountered the problem, until today that is.

I'm helping out doing a small piece on another project. Trying to get back into the habit of "good" unit-testing, I have created some test data. One build script that runs the whole thing, a few user scripts and finally a teardown script that deletes all the data I have created.

Naturally, I run it via SQL*Plus; turning feedback and echo off and using a liberal number of PROMPT directives (very similar to how the ApEx install goes coincidentally). This is what my teardown script reports:

...deleting from child table 1
...deleting from child table 2
...deleting from child table 3
...etc, etc

Nothing fancy. Then I realized on the final DELETE it was hanging up. Any locked objects? Nope. Maybe it's the trigger? I checked, only saw ON INSERT OR UPDATE, confirmed that with another developer. He suggested unindexed foreign keys. Huh? Really? That's impossible you say.

It wasn't impossible. It was true. So I ran my copy of the Tom's "find unindexed foreign keys."

Quite a few "****" which is not good.

So I went searching for them myself and came up with this little query (for my specifc table):

SELECT 
  b.table_owner,
  b.table_name,
  b.constraint_name,
  b.column_name,
  a.index_name
FROM
  dba_ind_columns a,
  (
    SELECT 
      a.owner table_owner,
      a.table_name, 
      a.constraint_name, 
      b.column_name
    FROM 
      dba_constraints a,
      dba_cons_columns b
    WHERE a.constraint_name = b.constraint_name
      AND a.owner = b.owner
      AND a.table_name = b.table_name
      AND a.r_constraint_name = ( SELECT constraint_name
                                FROM DBA_CONSTRAINTS
                                WHERE TABLE_NAME = 'MY_TABLE'
                                  AND owner = 'SCHEMA_NAME'
                                  AND constraint_type = 'P' )
  ) b
WHERE b.table_owner = a.table_owner (+)
  AND b.column_name = a.column_name (+)
  AND b.table_name = a.table_name (+)
ORDER BY 
  b.table_name, 
  b.constraint_name

That gave me a list of all the columns referencing the primary key (some 37 different tables). If index_name was NULL, then I knew I had to add one. Since I have this obsession lately with generating code, I just used this query and added the following:

  ( CASE
      WHEN a.index_name IS NULL THEN
        'CREATE INDEX schema_name.' || b.table_name || '_' || b.column_name || '_idx
        ON ' || b.table_owner || '.' || b.table_name || '( ' || b.column_name || ' )
        TABLESPACE my_tablespace;' 
    END ) sql_text

Now I have my CREATE INDEX statements and all is well.

I run the teardown script again and it finishes like it should.

Labels: constraints, indexes, sql, testing

  ¶ 12/02/2008 07:15:00 PM   1 Comments Links to this post

  FLASHBACK DATABASE I've used Oracle's flashback technology to restore data, but I never realized that it was possible to flashback the objects as well.

Typically, I've taken an export of the database objects at the point where I want to restore them, do my testing, drop all the users, recreate them and import the dump file back in to "restore."

A colleague of mine sent me a link about GUARANTEE FLASHBACK DATABASE. Pretty cool.

Fired up the local instance and began.

SELECT log_mode, flashback_on 
FROM v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

OK, I cheated and set this all up. My sandbox is typically set in NOARCHIVELOG mode and I had to figure out how to turn flashback on.

Now to create a restore point.

CREATE RESTORE POINT my_restore_point;

There are 2 types of restore points, Normal (the default) and Guarantee. Normal will age out of the control file after a set amount of time. Guarantee must be explicitly dropped. Oracle has the capability to store thousands of restore points.

So I mocked up a small example:

SYS@ELEVEN>SYS@ELEVEN>SYS@ELEVEN>DESC CJUSTICE.DEPT
 Name                       Null?    Type
 -------------------------- -------- --------------------- 
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)

SYS@ELEVEN>ALTER TABLE CJUSTICE.DEPT ADD ( SOME_COLUMN NUMBER );

Table altered.

Elapsed: 00:00:00.87

SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                       Null?    Type
 -------------------------- -------- ---------------
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)
 SOME_COLUMN                         NUMBER

SYS@ELEVEN>ALTER DATABASE CLOSE;

Database altered.

Elapsed: 00:00:05.57

SYS@ELEVEN>FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Flashback complete.

Elapsed: 00:00:05.84

SYS@ELEVEN>SHUTDOWN;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@ELEVEN>STARTUP;
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             327156628 bytes
Database Buffers          201326592 bytes
Redo Buffers                5844992 bytes
Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ELEVEN>ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:25.18
SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                     Null?    Type
 ------------------------ -------- -------------------------------
 DEPTNO                   NOT NULL NUMBER(2)
 DNAME                             VARCHAR2(14)
 LOC                               VARCHAR2(13)

Voila!

Pretty amazing me thinks.

I still don't know if you can do this without shutting down the database. But it suits my purposes for the time being.

I'm constantly amazed at how much I don't know about Oracle.

Labels: database, flashback, oracle, testing

  ¶ 11/26/2008 09:25:00 AM   3 Comments Links to this post

  Datawarehouse: Testing Until recently, I have been doing support/enhancements and new development for one particular project.

In our development environment, I have essentially been doing full load testing, using all of the records that are in production.

Needless to say, our development iterations were slow. Make a change to the code and then reload some 100 million records which are bounced against one another. The good thing is that I've picked up some really good tuning skills. The bad thing is my project was recently 2 months late.

Now that I've had a chance to breathe, I've been able to rethink some of the processes. First and foremost being how we test (both in development and QA).

If I had taken the time one year ago to build a "build" and "teardown" script, I probably could have shaved months of the project life cycle.

I used SQLUnit a few years ago and found that to be a pretty good tool. At the time, it consisted of writing out XML files and was quite time consuming, but gave me more confidence in the code I wrote. It also taught me to think like a tester forcing me to write better code as I could predict much easier where the problems might be.

We've used no such tool at WellCare.

I believe database testing to be extremely difficult. Our unit tests have consisted of running the procedures and then pasting that to a Word document...not really my idea of good testing, but it's what we have.

So are there any tools that you use to test database specific items? If you don't use tools, how do you do it? Why does database testing seem so difficult?

Labels: database, datawarehouse, testing

  ¶ 4/18/2008 07:52:00 PM   0 Comments Links to this post