making default column values work in PL/SQL's %ROWTYPE inserts

I'm going to look/post around the web for the answer to this puzzle...

I like PL/SQL's capability to insert a row all at once, using %ROWTYPE.

Example:

CREATE TABLE TEST
 (a NUMBER,
  b NUMBER DEFAULT 0 NOT NULL);

 declare
   t test%ROWTYPE;
 begin
   t.a := 22;
   t.b := 0;
   insert into test
   values t;
 end;
 /
 
PL/SQL procedure successfully completed.

However, I'm trying to use it to do inserts on a table with many NOT NULL, DEFAULT 'N' columns, and I get ORA-01400 CANNOT INSERT NULL errors.

declare
  t test%ROWTYPE;
begin
  t.a := 22;
  insert into test
  values t;
end;
/

ERROR at line 1:
ORA-01400: cannot insert NULL into ("EQDBW"."TEST"."B")
ORA-06512: at line 5

I don't want to clutter my PL/SQL by manually setting all these columns to their default values; after all, reducing clutter was the whole idea behind using %ROWTYPE in the first place. Besides, if I hard-code the default assignment into my PL/SQL, then it will become out-of-date if I change the column defaults in the table specification later.

If life were perfect, the automatic behavior would be to use the default values for these columns when nothing is specified in the %ROWTYPE variable. I'd settle for having some manual way to specify that as the behavior I want, but I can't find it.

Ironically, there is a way to do this for a record type explicitly defined in PL/SQL - "To set all the fields in a record to default values, assign to it an uninitialized record of the same type". But that doesn't work for %ROWTYPE records (I tried).

reference:
PL/SQL User's Guide and Reference
5 Using PL/SQL Collections and Records
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/05_colls.htm#sthref655

Win32::GuiTest

I have given laborious and painful birth to an automated GUI tester. I'm feeling a bit of postpartem depression, because it's nowhere near as clean as I'd like. I chose some complex ways of coding simply because the obvious ways produced errors or strange results that I didn't understand. It was also hard to get consistent results... things would stop and start working without apparent cause.

Bleh. I don't like programming voodoo.

This article says, "the first rule of GUI testing is: Don’t!", and it's really true. It would have been far better to get my developer to design his GUI app for testability in the first place, or at least get him to embed handy hooks in the code itself. Working totally from the outside, trying to write code that looks at and uses a GUI on the screen exactly as you do, is

But still, there are times when you do have to work as a complete outsider to the code. It is nice that there's a way, however painful. And here are some things I learned. Who knows if they will have any applicability to anybody else's Win32::GuiTest project? I'm sure it depends on a million quirks of the system you're testing.

1. Most things I saw on my screen could not be probed by Win32::GuiTest, as far as I could tell. About all I could see were the titles of windows and window elements, and not even all of those.
2. If you SendKeys, don't forget to bring your window to the foreground with SetForegroundWindow first. For me, the same GUI automatically seized the foreground on one machine, but on another machine, it stayed back, and the wrong window intercepted my keystrokes.
3. I wanted to know how long an hourglass wait lasted while a new window came up. I found no good solution. FindWindowLike succeeded too soon - it found the window immediately, long before the user could see it. What I settled for was to count the number of dependent items that GetChildWindows would find for the new window - and keep counting until the number was up to its maximum value. Then I knew the window was ready for business. Kludgey? Oh, yes!
4. Insert lots of sleep()s. They help reduce errors enormously.
5. I had trouble stringing several tests together into a single session. The first test in a single session would work fine, but inconsistent problems cropped up with subsequent tests. Eventually, I remembered that it's a machine and it doesn't get bored, so I made it close the application, get all the way out, and go all the way back in for each test.

timeout with threading

I'd gotten the impression that python's threading module was a very frightening thing, and had put off getting to know it. That's too bad, because when I finally looked at it, it provided a very simple way to add timeouts to some of my code. And - unlike os.fork(), etc. - it works on Windows!

    t = threading.Thread(target=self.code)    
    t.start()
    t.join(self.timeout)
    if t.isAlive():
        completed = False
        self.msg = 'Timed out after %s seconds' % (self.timeout)
        if self.killOnTimeout:
            t._Thread__stop()

interactive perl

I'm writing a GUI tester, which involves me deeply in Win32::GuiTest. It's been a mix of joys and frustrations. Two things have saved my mind.

1. I'm hooked on Python's interactivity, especially when exploring a poorly-documented module. I was missing that badly in Perl; Komodo's interactive mode is only a partial substitute. This tip saved me: http://www.devx.com/tips/Tip/17304?trk=DXRSS_WEBDEV
   
2. Getting a module to export its functions was giving me fits. Just adding the function names to EXPORT_OK in the .pm file only gave me "Can't locate
   auto/... (function).al" errors. This is black magic to me, but it fixed it: http://groups.yahoo.com/group/libwww-perl/message/1470
   

IOUG Live! sent me into rapturous joy. More on that later. I can't believe I was seriously considering not going.

Here's one of the problems that the conference set my mind to.

Some of my homemade procedures fill up some tables with huge amounts of somewhat ephemeral logging data. It's only needed for a couple weeks, after which I need to delete the rows. Doing it the conventional way generates cruel amounts of redo. I've wanted a good script to duplicate the effects of the imaginary command
DELETE FROM logtable WHERE blah blah blah NOLOGGING

The basic algorithm I've seen is
CREATE TABLE newlogtable AS
SELECT * FROM logtable
WHERE (criteria for keeper rows)
UNRECOVERABLE;
DROP TABLE logtable;
RENAME newlogtable TO logtable;

The trouble is that it's hard to get all the dependent DDL to move along. Especially since I don't want any names to change - I name all my constraints, down to the NOT NULLS. The script gets more complicated then.

I found out about DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_DEPENDENT_DDL at Joe Trezzo's "New 10g & 9i features", and it looks like that might be a good basis. It's still tricky, though, because I can't create new dependent DDL until the originals have been dropped. It feels like juggling: generate the new DDL, drop the original dependent objects but not the original table, create the new table with its data based on the stored DDL, then finally drop the original table... hmm.

I'm thinking of relying on the recycle bin (more new IOUG knowledge) for some help here. Deleting objects in 10g really only renames them and designates them as being in a "recycle bin"; you can still access them, you just need to consult the right views to learn their new obscure system-assigned names. (They make an awful clutter in your schema viewed through TOAD, btw.)

Could I drop the table (with its objects) first, then build a new table based directly on the one in the recycle bin? That means all the old dependent object names would already be out of the way, and I can do it all in fewer steps.

Something seems scary about relying on the recycle bin that way, though. Like mailing checks against the deposit you expect to make while your envelopes are still in the mail. I guess Oracle makes no guarantees about the continued availability of objects in the recycle bin; they stay until they're manually purged or the tablespace fills up. But, since it is a very bulky table I'm working with, it's not too farfetched that the recycle-bin version could become unavailable while I'm halfway through this process. Hmm. Darn.