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

Hi Catherine,

I'm not sure what your trying to achieve here... using the "values t" clause means you're telling Oracle "I have an entire row". The fact that some of the column values are null doesn't invoke the use of DEFAULT clauses, just like normal SQL, ie, if you did

insert into T ( col_with_default_clause) values (null);

you would get a null

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------