I like PL/SQL's capability to insert a row all at once, using %ROWTYPE.
Example:
CREATE TABLE TEST
(a NUMBER,
b NUMBER DEFAULT 0 NOT NULL);
declare
t test%ROWTYPE;
begin
t.a := 22;
t.b := 0;
insert into test
values t;
end;
/
PL/SQL procedure successfully completed.
However, I'm trying to use it to do inserts on a table with many NOT NULL, DEFAULT 'N' columns, and I get ORA-01400 CANNOT INSERT NULL errors.
declare
t test%ROWTYPE;
begin
t.a := 22;
insert into test
values t;
end;
/
ERROR at line 1:
ORA-01400: cannot insert NULL into ("EQDBW"."TEST"."B")
ORA-06512: at line 5
I don't want to clutter my PL/SQL 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).
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
The consensus of the Oracle community is that there really is no consise and clean way to do this. There were suggestions, but they were clunky ideas I'd already considered - like building a SQL string for EXECUTE IMMEDIATE. Oh, well.
ReplyDeleteMy solution:
ReplyDelete-- create additional 'template' table
CREATE TABLE TEST_TEMPLATE
(
a NUMBER,
b NUMBER DEFAULT 0 NOT NULL
);
insert into TEST_TEMPLATE
(a) values (null);
declare
R TEST_TEMPLATE%rowtype;
begin
-- fill default values
select * into R from
TEST_TEMPLATE where rownum = 1;
R.a := 1;
insert into TEST values R;
end;
It can be fairly simple! I made a procedure for that and called it init. It looks up all the default values if a particular table in the user_tab_columns systemview:
ReplyDeleteprocedure pi_init
is
lv_query varchar2(1000);
type cur_typ is ref cursor;
rcur_typ cur_typ;
rc_sometable_empty sometable%rowtype;
begin
for rec in (select *
from user_tab_columns
where table_name = 'sometable'
order by column_id
)
loop
if rec.data_default is null
then
lv_query := lv_query||', null';
elsif rec.data_type = 'NUMBER'
then
lv_query := lv_query||', '||rec.data_default;
else
lv_query := lv_query||', '''||rec.data_default||'''';
end if;
end loop;
lv_query := 'SELECT'||substr(lv_query,2)||' FROM DUAL';
open rcur_typ for lv_query;
fetch rcur_typ into rc_sometable_empty;
close rcur_typ;
end pi_init;
Works like a charm!
-- Created on 10.6.2009 by KAREL
ReplyDeleteDeclare
-- Local variables here
i Integer;
Type TR_Cur Is Ref Cursor;
lCur2 TR_Cur;
ret ivan.customer%Rowtype;
Function Dej_defaultValue(intable Varchar2) Return TR_Cur As
s Varchar2(10000) := '';
lCur TR_Cur;
Begin
For a_cur In (Select *
From sys.all_tab_cols c
Where c.table_name = intable
And c.column_id Is Not Null
Order By c.column_id)
Loop
s := s || ',' || nvl(a_cur.data_default, 'null');
End Loop;
s := 'select ' || substr(s, 2) || ' from dual';
Open lcur For s;
Return lCur;
End Dej_defaultValue;
Begin
-- Test statements here
--ivan.customer
lCur2 := Dej_defaultValue('CUSTOMER');
Loop
Fetch lcur2
Into ret;
Exit When lcur2%Notfound;
-- Pipe Row(ret);
End Loop;
Close lcur2;
dbms_output.put_line( ret.bbs);
End;