|
4550
2006-NOV-26 21:31:58
|
|
Moderator
|
|
|
Registered On: Mar 2006
Total Posts: 267
|
|
Can you remove savepoint from a master detail insert API?
drop table order_line;
drop table order_header;
drop table log_access;
drop sequence order_header_s;
drop sequence order_line_s;
create table order_header
( oh_id number not null,
ship_to_id number not null,
bill_to_id number not null );
alter table order_header add (
constraint oh_pk primary key (oh_id) );
create sequence order_header_s;
create table order_line
( ol_id number not null,
oh_id number not null,
ol_prod_id number not null,
ol_prod_qty number not null,
ol_desc varchar2(30) );
alter table order_line add (
constraint ol_fk foreign key (oh_id)
references order_header(oh_id) );
alter table order_line add (
constraint ol_uk unique (oh_id,ol_prod_id) );
create sequence order_line_s;
create table log_access
( access_date date not null,
username varchar2(30) not null,
http_call varchar2(4000) not null );
create or replace package order_api as
bad_data exception;
procedure save_order(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number);
end order_api;
/
create or replace package body order_api as
procedure save_order(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number)
is
l_oh_id order_header.oh_id%type;
begin
select order_header_s.nextval
into l_oh_id
from dual;
insert into order_header
( oh_id, ship_to_id, bill_to_id )
values
( l_oh_id, p_ship_to, p_bill_to );
insert into order_line
( ol_id, oh_id, ol_prod_id, ol_prod_qty, ol_desc )
values
( order_line_s.nextval, l_oh_id, p_line_one_id, p_line_one_qty,
'order_api line 1' );
insert into order_line
( ol_id, oh_id, ol_prod_id, ol_prod_qty, ol_desc )
values
( order_line_s.nextval, l_oh_id, p_line_two_id, p_line_two_qty,
'order_api line 2' );
exception
when dup_val_on_index then
raise bad_data;
end save_order;
end order_api;
/
create or replace package order_api_savepoint as
bad_data exception;
procedure save_order(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number);
end order_api_savepoint;
/
create or replace package body order_api_savepoint as
procedure save_order(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number)
is
l_oh_id order_header.oh_id%type;
begin
savepoint as_before;
select order_header_s.nextval
into l_oh_id
from dual;
insert into order_header
( oh_id, ship_to_id, bill_to_id )
values
( l_oh_id, p_ship_to, p_bill_to );
insert into order_line
( ol_id, oh_id, ol_prod_id, ol_prod_qty, ol_desc )
values
( order_line_s.nextval, l_oh_id, p_line_one_id, p_line_one_qty,
'order_api_savepoint line 1' );
insert into order_line
( ol_id, oh_id, ol_prod_id, ol_prod_qty, ol_desc )
values
( order_line_s.nextval, l_oh_id, p_line_two_id, p_line_two_qty,
'order_api_savepoint line 2' );
exception
when dup_val_on_index then
rollback to as_before;
raise bad_data;
end save_order;
end order_api_savepoint;
/
create or replace package order_ui as
procedure order_form(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number,
p_what in varchar2 default 'form',
p_message in varchar2 default null);
end order_ui;
/
create or replace package body order_ui as
procedure order_form(p_cust_id in number,
p_ship_to in varchar2,
p_bill_to in varchar2,
p_line_one_id in number,
p_line_one_qty in number,
p_line_two_id in number,
p_line_two_qty in number,
p_what in varchar2 default 'form',
p_message in varchar2 default null)
is
begin
insert into log_access values (sysdate,user,'order_form?p_cust_id=.....');
if ( p_what = 'form' )
then
-- print user form
null;
elsif ( p_what = 'save' )
then
begin
order_api.save_order(p_cust_id => p_cust_id,
p_ship_to => p_ship_to,
p_bill_to => p_bill_to,
p_line_one_id => p_line_one_id,
p_line_one_qty => p_line_one_qty,
p_line_two_id => p_line_two_id,
p_line_two_qty => p_line_two_qty);
exception
when order_api.bad_data then
order_form(p_cust_id => p_cust_id,
p_ship_to => p_ship_to,
p_bill_to => p_bill_to,
p_line_one_id => p_line_one_id,
p_line_one_qty => p_line_one_qty,
p_line_two_id => p_line_two_id,
p_line_two_qty => p_line_two_qty,
p_message => 'Please correct data');
end;
elsif ( p_what = 'save_savepoint' )
then
begin
order_api_savepoint.save_order(p_cust_id => p_cust_id,
p_ship_to => p_ship_to,
p_bill_to => p_bill_to,
p_line_one_id => p_line_one_id,
p_line_one_qty => p_line_one_qty,
p_line_two_id => p_line_two_id,
p_line_two_qty => p_line_two_qty);
exception
when order_api_savepoint.bad_data then
order_form(p_cust_id => p_cust_id,
p_ship_to => p_ship_to,
p_bill_to => p_bill_to,
p_line_one_id => p_line_one_id,
p_line_one_qty => p_line_one_qty,
p_line_two_id => p_line_two_id,
p_line_two_qty => p_line_two_qty,
p_message => 'Please correct data');
end;
end if;
end order_form;
end order_ui;
/
begin
order_ui.order_form(p_cust_id => 1,
p_ship_to => 1,
p_bill_to => 1,
p_line_one_id => 1,
p_line_one_qty => 10,
p_line_two_id => 1,
p_line_two_qty => 20,
p_what => 'save');
commit;
end;
/
select * from order_header;
select * from order_line;
delete from order_line;
delete from order_header;
commit;
begin
order_ui.order_form(p_cust_id => 1,
p_ship_to => 1,
p_bill_to => 1,
p_line_one_id => 1,
p_line_one_qty => 10,
p_line_two_id => 1,
p_line_two_qty => 20,
p_what => 'save_savepoint');
commit;
end;
/
select * from order_header;
select * from order_line;
----------------------------------------------------------------------------
--
-- the call below has intentional "bad data" that will raise
-- bad_data exception ( simulated dup_val_on_index )
-- but since there's no savepoint the order header and
-- one of the order lines will be saved anyway ...
--
DBATOOLZ> begin
2 order_ui.order_form(p_cust_id => 1,
3 p_ship_to => 1,
4 p_bill_to => 1,
5 p_line_one_id => 1,
6 p_line_one_qty => 10,
7 p_line_two_id => 1,
8 p_line_two_qty => 20,
9 p_what => 'save');
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
--
-- so now we have incomplete order that
-- wasn't even supposed to be saved
--
DBATOOLZ>
DBATOOLZ>
DBATOOLZ> select * from order_header;
OH_ID SHIP_TO_ID BILL_TO_ID
---------- ---------- ----------
1 1 1
1 row selected.
DBATOOLZ> select * from order_line;
OL_ID OH_ID OL_PROD_ID OL_PROD_QTY OL_DESC
---------- ---------- ---------- ----------- ------------------------------
1 1 1 10 order_api line 1
1 row selected.
DBATOOLZ>
DBATOOLZ> delete from order_line;
1 row deleted.
DBATOOLZ> delete from order_header;
1 row deleted.
DBATOOLZ> commit;
Commit complete.
--
-- now we make the same call with intentional "bad data" that will raise
-- bad_data exception ( simulated dup_val_on_index )
-- but this time there IS savepoint so the order header and
-- one of the order lines will NOT be saved (correct behavior)
--
DBATOOLZ>
DBATOOLZ> begin
2 order_ui.order_form(p_cust_id => 1,
3 p_ship_to => 1,
4 p_bill_to => 1,
5 p_line_one_id => 1,
6 p_line_one_qty => 10,
7 p_line_two_id => 1,
8 p_line_two_qty => 20,
9 p_what => 'save_savepoint');
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
DBATOOLZ>
DBATOOLZ>
DBATOOLZ> select * from order_header;
no rows selected
DBATOOLZ> select * from order_line;
no rows selected
DBATOOLZ>
|
4559
2006-NOV-28 19:07:15
|
|
Moderator
|
|
|
Registered On: Mar 2006
Total Posts: 267
|
|
If you are wondering what this is all about ... well it all started here:
AskTom question about Database Locks