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