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.

O-verkill-racle

Oracle's had a philosophy for a while now that, "We're the database company; so anytime our software has to interact with any data, for any reason, we'll have it create an Oracle database for the purpose."

The trouble is, an Oracle database represents a LOT of overhead. For instance, an empty Oracle database takes ten times the disk space of an empty postgresql database. Worse, every new database is a new administration burden.

We wanted to do a little bit - a very little bit - with web-based Oracle Discoverer. That meant installing Oracle Application Server, and OAS created its very own infrastructure database. Now I've got this "infrastructure" database sitting around, doing next to nothing, soaking up almost as much disk space and memory as a small production database would. It's 9.0.1 - way behind my own databases - and I have to attend to its obsolete 9.0.1 security needs, or the network security scan gripes at me.

And the shame of it is - a use like this doesn't need the things an RDBMS provides. A store of configuration data for an application server doesn't need multi-user access, transactional consistency, etc. etc. - totally unnecessary. A few flat files would do fine.

It's like a trucking company that won't use a hand dolly because, well, they're a trucking company. So, of course, they need diesel pumps and regular oil changes and overhauls for the big rigs they use to get the copy paper down to the copy room, and maneuvering the 18-wheelers between the cubicles may be a little troublesome sometimes.

passwords in LSNRCTL

Oracle's listener control (LSNRCTL) can take a password. The way you actually supply the password is very confusing, though.

c: > lsnrctl
LSNRCTL> stop
TNS-01169: The listener has not recognized the password

What do you mean, "Did not recognize the password?" You never asked for a password!

LSNRCTL> set password badguess
LSNRCTL > stop
TNS-01169: The listener has not recognized the password
LSNRCTL> set password goodguess
LSNRCTL > stop
The command completed successfully

In other words... 'set password' doesn't mean 'set password' the way normal human beings mean it - 'establish this as the password'. It means, 'this is what I think the password is, so transmit this whenever I do something that demands a password'.

Humph. There should be a category for "not exactly a bug, but nomenclature confusing enough that it may as well be".

For the first time in my life, I find myself worried about mutability.

I want to take advantage of Python's new sets (who wouldn't?). It turns out that sets can only be made of immutable, hashable objects, and a user-created object type is (by definition) mutable.

I can see where it makes sense. If I say
box1 = Box(contents='eggs')
box2 = Box(contents='eggs')
basket = set([box1, box2])
should basket contain one or two items?
Now suppose I
box2.contents = 'yogurt'
Now does my basket have a box of eggs and a box of yogurt, or just one box of yogurt? It's not clear.

The problem is, there are objects I will create that truly never will change, and it truly would be useful to use this wonderful set functionality with them. So now I'd like to be able to create immutable objects, but Python has no particular way to create them.

The only way I've found so far feels a little bit dangerous - to define __hash__. As far as I can tell, it means "You can know me by this". If I do forget and later change data that is the basis of my hash, my object no longer hashes to what it once did - it has moved and left no forwarding address. There are some clumsy ways to try to block later changes to my object's values, but they are... clumsy. (Redefining the object's __setattr__ and raising exceptions therein. Ick.)

Thus, it feels like I'm delving into eldritch secrets, like my soul is toast if I scuff the pentagram. __str__ and __cmp__ are the only double-underscore methods I'd defined previously. They feel downright warm and fuzzy compared to this.

undead Apache processes

Beware of surviving Apache processes that you thought you had stopped.

I just had a miserable time making some changes to httpd.conf on an Apache (1.3)/Windows installation. After making each change, I'd stop/start Apache and test the page with a browser to see if I'd mucked httpd.conf up fatally. The trouble is that I got a lot of false readings with this test, which confused me horribly. (Make change X; page works. Make change Y; page doesn't work. Undo change Y; page doesn't work. Undo change X; page doesn't work. Scream.)

It turns out that, although I'd executed 'apache stop' properly (I think), there were quite a few apache.exe processes lurking alive in the Task Manager's Processes tab. Apparently they were the source of the trouble, because killing them manually during my stop/starts eliminated my false test results.

Oracle 10g upgrade woes

I really ought to initiate a blog with a non-grumpy post! Oh, well. You should have been here a month ago when everything was sweetness and light.

I'm the early-adopter type. I upgraded to 9i pretty early on, and it went well. A couple weeks ago, I upgraded to Oracle 10g. This time, it hasn't gone very well.

At first, performance was absolutely miserable - the CPU was 100% busy and stayed there. But that was my fault. Shame on me, I had accepted the init.ora parameters written by DBCA. Fixing some of them (cursor_space_for_time, sga_max_size, sga_target, fast_start_mttr_target, cursor_sharing, session_cached_cursors) corrected that. I know that's my responsibility, but I'm really surprised that neglecting it caused such an utter CPU jam.

Then, last night, the database started dumping lots of "ORA-04021: timeout occurred while waiting to lock object" errors into the alert log and refusing to do anything whatsoever. The associated trace files mean nothing: "No current SQL statement being executed." I shouldn't have to be looking at binary stack dumps, dangit. At the time, I was trying to do a full export with EXP; I don't know whether that caused the problem. Supposedly, you can still use EXP with 10g, but I wonder if Oracle has stopped bug-checking it vigorously. Tonight, I'll switch to EXPDP.

There were also many unhandled PL/SQL exceptions in packages like WKSYS.WK_JOB - packages that Oracle supplied and that I don't even know what they're doing. Oracle's ever-increasing sophistication means that it's always doing more and more stuff behind my back; with 10g, that stuff sometimes clogs the performance, and sometimes it contains errors. I am not impressed. Postgresql looks more appealing all the time.

The problem might be that I'm one patch behind - I'm at 10.1.0.2, not 10.1.0.3. The patch procedure is pretty involved, though - a lot more involved than it ought to be, for a top-dollar product, IMHO. It will mean a couple hours of downtime, which means it should be done in the middle of the night or on a weekend. I wouldn't mind that, but as a contractor, I'm not allowed to be in the building at those times without dragging my supervisor in to babysit me.