Home » SQL & PL/SQL » SQL & PL/SQL » doubt regarding exception handling? (11g)
| doubt regarding exception handling? [message #616740] |
Fri, 20 June 2014 04:28  |
 |
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
|
Member |
|
|
i have a sp.i want to modify the sp to handle dup_val_on_index to update instead of insert.
CREATE OR REPLACE PROCEDURE SUPPLIER.sp_sc_update(
iv_reg_code IN VARCHAR2,
iv_user_id IN VARCHAR2,
iv_load_ref_num IN VARCHAR2,
iv_order_number IN VARCHAR2,
iv_item_number IN VARCHAR2,
iv_search_id IN VARCHAR2,
ov_err_code OUT NOCOPY VARCHAR2,
ov_err_messge OUT NOCOPY VARCHAR2)
IS
ln_count NUMBER;
lcur_orders SYS_REFCURSOR;
lv_err_code VARCHAR2 (200);
lv_err_messge VARCHAR2 (200);
lv_search_id VARCHAR2 (200);
BEGIN
IF iv_reg_code <> 'AA'
THEN
ov_err_code := 1;
ov_err_messge :=
'Error updating : Region '
|| iv_reg_code
|| ' not recognized';
RETURN;
END IF;
SELECT COUNT (search_id)
INTO ln_count
FROM search
WHERE search_id = iv_search_id;
IF NVL (ln_count, 0) <> 1
THEN
ov_err_code := 1;
ov_err_messge := 'Error updating l : Invalid search';
RETURN;
END IF;
SELECT COUNT (*)
INTO ln_count
FROM table1
WHERE order_number = iv_order_number AND item_number = iv_item_number;
IF NVL (ln_count, 0) = 0
THEN
INSERT INTO table1 (order_number,
country_iso_code,
item_number,
loading_ref_num,
mod_user,
mod_timestamp)
VALUES (iv_order_number,
'WS',
iv_item_number,
iv_load_ref_num,
iv_user_id,
SYSDATE);
COMMIT;
else
update table1 set loading_ref_num = iv_load_ref_num,
mod_user = iv_user_id,
mod_timestamp = sysdate
where order_number = iv_order_number and
item_number = iv_item_number;
COMMIT;
END IF;
ov_err_code := 0;
EXCEPTION
WHEN OTHERS
THEN
ov_err_code := 1;
ov_err_messge := 'Error updating load confirmation : ' || SQLERRM;
END sp_sc_update;
/
wat i tried is
Exception
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE table1
SET loading_ref_num = iv_load_ref_num,
mod_user = iv_user_id,
mod_timestamp = SYSDATE
WHERE order_number = iv_order_number AND item_number = iv_item_number;
COMMIT;
(by removing update statement in if condition and placing it in exception section)
is this correct?i want to modify the sp to handle dup_val_on_index to update instead of insert.
[Updated on: Fri, 20 June 2014 04:31] Report message to a moderator |
|
|
|
|
|
|
|
| Re: doubt regarding exception handling? [message #616743 is a reply to message #616740] |
Fri, 20 June 2014 04:45  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Keep the insert statement inside BEGIN-END and handle exception there itself,
BEGIN
IF NVL (ln_count, 0) = 0
THEN
INSERT ...
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE...
END;
And remove COMMIT inside the procedure. It is a bad practice and welcoming unwanted things to happen.
|
|
|
|
|
|
|
|
| Re: doubt regarding exception handling? [message #616752 is a reply to message #616749] |
Fri, 20 June 2014 07:20 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
kiranrathodkr916 wrote on Fri, 20 June 2014 16:22then how do you COMMIT the changes
You should commit the changes only when the CALLER is sure about the successful transaction. Transaction control decisions should remain with the CALLER. So, don't commit inside the procedure.
|
|
|
|
| Re: doubt regarding exception handling? [message #616761 is a reply to message #616749] |
Fri, 20 June 2014 08:11 |
|
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
|
Member |
|
|
my modified code looks like this
CREATE OR REPLACE PROCEDURE sp_sc_update(
iv_reg_code IN VARCHAR2,
iv_user_id IN VARCHAR2,
iv_load_ref_num IN VARCHAR2,
iv_order_number IN VARCHAR2,
iv_item_number IN VARCHAR2,
iv_search_id IN VARCHAR2,
ov_err_code OUT NOCOPY VARCHAR2,
ov_err_messge OUT NOCOPY VARCHAR2)
IS
ln_count NUMBER;
lcur_orders SYS_REFCURSOR;
lv_err_code VARCHAR2 (200);
lv_err_messge VARCHAR2 (200);
lv_search_id VARCHAR2 (200);
BEGIN
IF iv_reg_code <> 'EU'
THEN
ov_err_code := 1;
ov_err_messge :=
'Error updating : Region '
|| iv_reg_code
|| ' not recognized';
RETURN;
END IF;
SELECT COUNT (search_id)
INTO ln_count
FROM search
WHERE search_id = iv_search_id;
IF NVL (ln_count, 0) <> 1
THEN
ov_err_code := 1;
ov_err_messge := 'Error updating l : Invalid search';
RETURN;
END IF;
SELECT COUNT (*)
INTO ln_count
FROM table1
WHERE order_number = iv_order_number AND item_number = iv_item_number;
BEGIN
IF NVL (ln_count, 0) = 0
THEN
INSERT INTO table1 (order_number,
country_iso_code,
item_number,
loading_ref_num,
mod_user,
mod_timestamp)
VALUES (iv_order_number,
'WS',
iv_item_number,
iv_load_ref_num,
iv_user_id,
SYSDATE);
COMMIT;
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
update table1 set loading_ref_num = iv_load_ref_num,
mod_user = iv_user_id,
mod_timestamp = sysdate
where order_number = iv_order_number and
item_number = iv_item_number;
commit;
ov_err_code := 0;
END sp_sc_update;
/
i have an unique constraint on tabe1(order_number,item_number);
i execute the sp as
declare
err_code varchar2(100);
err_msg varchar2(100);
begin
sp_sc_update('EU','BE8926','ab','3147970280','0001','2251',err_code,err_msg);
end;
/
table already conatins a record with this order number,so when it encouters duplicate records it should update,i can check the update by looking at the mod_timestamp of table1
but when i execute the sp i dont see any update.what im missisng here
i also created a unique index table1(order_number) and tried to excute.that dint work as well
[Updated on: Fri, 20 June 2014 08:14] Report message to a moderator |
|
|
|
|
|
|
|
| Re: doubt regarding exception handling? [message #616766 is a reply to message #616762] |
Fri, 20 June 2014 08:26 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You aren't paying attention to what the code you've written does.
Originally you were checking a count, if it's 0 you do an insert, otherwise you do an update.
You've changed the point at which the update is run, but the check of the count remains.
So what happens when the count isn't 0?
|
|
|
|
| Re: doubt regarding exception handling? [message #616814 is a reply to message #616766] |
Fri, 20 June 2014 14:44 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
you don't need to get a count to see if the row is there. simply use exception handling
begin
INSERT INTO table1 (order_number,
country_iso_code,
item_number,
loading_ref_num,
mod_user,
mod_timestamp)
VALUES (iv_order_number,
'WS',
iv_item_number,
iv_load_ref_num,
iv_user_id,
SYSDATE);
COMMIT;
END IF;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
update table1 set loading_ref_num = iv_load_ref_num,
mod_user = iv_user_id,
mod_timestamp = sysdate
where order_number = iv_order_number and
item_number = iv_item_number;
end;
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 05:24:43 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
