Re: making default column values work in %ROWTYPE inserts

Catherine wrote:
> 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

You can not do what you are trying to do. Type in those column names.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)