Bullet proofing entire record inserts/updates

There's a handy feature in PL/SQL that lets you insert an entire record into a table without specifying each individual field in the record. Here's an example.


create table wide_test
  (id number, 
   col_a varchar2(10), col_b varchar2(10), col_c varchar2(10));

declare
  cursor c_wide is 
   select *
   from wide_test;
  v_rec c_wide%rowtype;
begin
  v_rec.id := 1;
  v_rec.col_a := 'A';
  v_rec.col_b := 'B';
  v_rec.col_c := 'C';
  --
  insert into wide_test
  values v_rec;
end;
/


select * from wide_test;

But if you give that to a code reviewer, they may tell you that using SELECT * is bad practice. What happens when a new column is added to the table ? So he makes you recode it.

declare

  cursor c_wide is 

   select id, col_a, col_b, col_c

   from wide_test;

  v_rec c_wide%rowtype;

begin

  v_rec.id := 2;

  v_rec.col_a := 'A';

  v_rec.col_b := 'B';

  v_rec.col_c := 'C';

  --

  insert into wide_test

  values v_rec;

end;

/

select * from wide_test;

Everyone is happy....until a new column is added to the table.

ALTER TABLE wide_test ADD col_d VARCHAR2(10) DEFAULT 'D' NOT NULL;

declare

  cursor c_wide is 

   select id, col_a, col_b, col_c

   from wide_test;

  v_rec c_wide%rowtype;

begin

  v_rec.id := 3;

  v_rec.col_a := 'A';

  v_rec.col_b := 'B';

  v_rec.col_c := 'C';

  --

  insert into wide_test

  values v_rec;

end;

/

insert into wide_test

            *

ERROR at line 12:

ORA-06550: line 12, column 15:

PL/SQL: ORA-00947: not enough values

That didn't quire go as planned. You get the same error if you try

  insert into wide_test (id, col_a, col_b, col_c)

  values v_rec;

Perhaps the SELECT * was the right way to go ?

declare

  cursor c_wide is 

   select *

   from wide_test;

  v_rec c_wide%rowtype;

begin

  v_rec.id := 3;

  v_rec.col_a := 'A';

  v_rec.col_b := 'B';

  v_rec.col_c := 'C';

  --

  insert into wide_test

  values v_rec;

end;

/

declare

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("PERFORM"."WIDE_TEST"."COL_D")

Nope ! The "col_b" in v_rec isn't set so remains NULL, but the database column is NOT NULL. Because a value is specified in the INSERT the default doesn't kick in.

Happily there is a solution:

declare

  cursor c_wide is 

   select id, col_a, col_b, col_c

   from wide_test;

  v_rec c_wide%rowtype;

begin

  v_rec.id := 3;

  v_rec.col_a := 'A';

  v_rec.col_b := 'B';

  v_rec.col_c := 'C';

  --

  insert into

    (select id, col_a, col_b, col_c

    from wide_test)

  values v_rec;

end;

/

select * from wide_test;

We use an inline view as the target of the insert. Since the order of the columns is specified in the inline view, this will continue to work even if someone re-arranges the columns in the underlying table. A regular, permanent, view in the database works just as well.

This is called the Insert Statement Extension and there's a similar facility for UPDATE

DECLARE

  cursor c_wide is 

   select col_a, col_b, col_c

   from wide_test;

  v_rec c_wide%rowtype;

BEGIN

  v_rec.col_a := 'A1';

  v_rec.col_b := 'B1';

  v_rec.col_c := 'C1';

  UPDATE 

   (SELECT col_a, col_b, col_c 

    FROM wide_test)

  SET ROW = v_rec;

END;

/