Home » SQL & PL/SQL » SQL & PL/SQL » doubt regarding exception handling? (11g)
doubt regarding exception handling? [message #616740] Fri, 20 June 2014 04:28 Go to next message
kiranrathodkr916
Messages: 33
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 #616741 is a reply to message #616740] Fri, 20 June 2014 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what's stopping you from doing that?
Re: doubt regarding exception handling? [message #616742 is a reply to message #616741] Fri, 20 June 2014 04:38 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
It should do the job, I suggest you test it.
Or you could use merge.
Re: doubt regarding exception handling? [message #616743 is a reply to message #616740] Fri, 20 June 2014 04:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
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 #616748 is a reply to message #616742] Fri, 20 June 2014 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

cookiemonster wrote on Fri, 20 June 2014 11:38
...
Or you could use merge.


Put it in bold. Smile

@OP, and I add, read WHEN OTHERS.

[Updated on: Fri, 20 June 2014 05:47]

Report message to a moderator

Re: doubt regarding exception handling? [message #616749 is a reply to message #616748] Fri, 20 June 2014 05:52 Go to previous messageGo to next message
kiranrathodkr916
Messages: 33
Registered: March 2014
Location: India
Member
Quote:
remove COMMIT inside the procedure. It is a bad practice and welcoming unwanted things to happen.


then how do you COMMIT the changes
Re: doubt regarding exception handling? [message #616752 is a reply to message #616749] Fri, 20 June 2014 07:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
kiranrathodkr916 wrote on Fri, 20 June 2014 16:22
then 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 Go to previous messageGo to next message
kiranrathodkr916
Messages: 33
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 #616762 is a reply to message #616761] Fri, 20 June 2014 08:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
You did not pay attention to any of the suggestions provided above, YOYO!

And if you want someone to replicate your issue, then provide a Test Case.
Re: doubt regarding exception handling? [message #616765 is a reply to message #616762] Fri, 20 June 2014 08:25 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
when order number exist below is FALSE
IF NVL (ln_count, 0) = 0
so no action is taken.

a solution is to use MEGRE

COMMIT can result in ORA-01555 error for any "long" running procedure inside a LOOP
Re: doubt regarding exception handling? [message #616766 is a reply to message #616762] Fri, 20 June 2014 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 11287
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 Go to previous messageGo to next message
Bill B
Messages: 1141
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;


Re: doubt regarding exception handling? [message #616819 is a reply to message #616814] Fri, 20 June 2014 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you don't need INSERT and UPDATE just MERGE.

Re: doubt regarding exception handling? [message #616820 is a reply to message #616819] Fri, 20 June 2014 15:28 Go to previous messageGo to next message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
true, merge is the way to go.
Re: doubt regarding exception handling? [message #616821 is a reply to message #616820] Fri, 20 June 2014 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
Bill B wrote on Fri, 20 June 2014 13:28
true, merge is the way to go.


I said the same about 7 hours ago.
Re: doubt regarding exception handling? [message #616822 is a reply to message #616821] Fri, 20 June 2014 15:33 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And cookiemonster before but it seems OP can't read it so it is worth to repeat it. Smile

Previous Topic: SYSDATE Central to Eastern time
Next Topic: cursor is not working
Goto Forum:
  


Current Time: Mon Dec 22 19:51:42 CST 2014

Total time taken to generate the page: 0.08822 seconds