Thursday, May 12, 2011

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;
/


5 comments:

Tim... said...

Hi.

This is used quite a bit in the SQL Expert certification exam. :)

Cheers

Tim...

SydOracle said...

One day, when I've got some spare money, I'll have to try one of those certifiaction exams

Wit said...

Hello. It doesn't work in my OracleXE environment. :(


create table tmp_ins(a number not null, b number not null);

declare
cursor c1 is select a from tmp_ins;
v1 c1%rowtype;
begin
v1.a:=1;
insert into (select a from tmp_ins)values v1;
end;
/
select * from tmp_ins;

results

table TMP_INS created.

Error starting at line 1 in command:
declare
cursor c1 is select a from tmp_ins;
v1 c1%rowtype;
begin
v1.a:=1;
insert into (select a from tmp_ins)values v1;
end;
Error report:
ORA-01400: cannot insert NULL into ("UANIC_DATA"."TMP_INS"."B")
ORA-06512: at line 6
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
A B
---------------------- ----------------------

SydOracle said...

The column 'B' needs a default value defined.

Wit said...

oops. I'd not see that in the example... Thank you.