making default column values work in %ROWTYPE inserts

Hi! I like PL/SQL's capability to insert a row all at once, using
%ROWTYPE.
CREATE TABLE TEST
 (a NUMBER DEFAULT 0 NOT NULL);

DECLARE
  t test%ROWTYPE;
BEGIN
  t.a := 0;
  INSERT INTO test VALUES t;
END;
/
PL/SQL procedure successfully completed.

But I'm trying to use it to do inserts on a table with many DEFAULT specified, NOT NULL columns, and I don't want to manually specify the values of the default columns. I get ORA-01400 CANNOT INSERT NULL errors.

DECLARE
  t test%ROWTYPE;
BEGIN
  INSERT INTO test VALUES t;
END;
/
ORA-01400: cannot insert NULL into ("MYSCHEMA"."TEST"."A")

I don't want to clutter my code 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).

Has anyone figured this out before?

Thanks!
- Catherine
http://profiles.yahoo.com/arcticturtle

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 Received on Tue May 31 2005 - 11:53:54 CDT