Home » SQL & PL/SQL » SQL & PL/SQL » Exception in proceedure (Oracle 10g)
Exception in proceedure [message #446206] Sat, 06 March 2010 04:32 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All,

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.Please explain in my case the code is below.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
   l_item_code        IN       VARCHAR2,
   l_main_acnt_code   OUT      VARCHAR2
)
IS
   p_item_type   NUMBER;
   CURSOR c1
   IS
      SELECT wip_main_acntno
        FROM om_item, wip_main_acnt
       WHERE item_ig_code = wip_charge_code AND item_code = l_item_code;
   CURSOR c2
   IS
      SELECT wip_main_acntno
        FROM om_item, ns_wip_acnt
       WHERE item_code = wip_charge_code AND item_code = l_item_code;
   CURSOR c3
   IS
      SELECT item_stk_yn_num
        FROM om_item
       WHERE item_code = l_item_code;
BEGIN
   IF c3%ISOPEN
   THEN
      CLOSE c3;
   END IF;
   OPEN c3;
   FETCH c3
    INTO p_item_type;
   CLOSE c3;
   IF NVL (p_item_type, 0) = 1
   THEN
      IF c1%ISOPEN
      THEN
         CLOSE c1;
      END IF;
      OPEN c1;
      FETCH c1
       INTO l_main_acnt_code;
      CLOSE c1;
   ELSE
      IF c2%ISOPEN
      THEN
         CLOSE c2;
      END IF;
      OPEN c2;
      FETCH c2
       INTO l_main_acnt_code;
      CLOSE c2;
   END IF;
END;





CM: fixed code tags

[Updated on: Sat, 06 March 2010 04:44] by Moderator

Report message to a moderator

Re: Exception in proceedure [message #446208 is a reply to message #446206] Sat, 06 March 2010 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Explicit cursors - which is what you are using - never raise NO_DATA_FOUND. With those you have to check %NOTFOUND

Only Implicit cursors (SELECT ... INTO ...) raise NO_DATA_FOUND.
Re: Exception in proceedure [message #446209 is a reply to message #446208] Sat, 06 March 2010 04:48 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks cookiemonster , can you add that exception in my code as i am little confused about using it.
Re: Exception in proceedure [message #446211 is a reply to message #446206] Sat, 06 March 2010 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
%NOTFOUND is not an exception.
It's a check to see if the cursor didn't find anything. I don't believe you need it.
Re: Exception in proceedure [message #446212 is a reply to message #446211] Sat, 06 March 2010 04:54 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Can u please add it in my code as if its not found it should prompt an error message.
Re: Exception in proceedure [message #446214 is a reply to message #446206] Sat, 06 March 2010 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not writing basic code for you.
Go look it up in the documentation if you're not sure how to use it - there will be examples.
Re: Exception in proceedure [message #446215 is a reply to message #446214] Sat, 06 March 2010 05:07 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I am really sorry for troubling you so much.Do you have any such examples and one more thing i want to learn forms 6i as i am new to it can you give me some links which will be helpful for me to quickly build forms and basically how to start with...
Re: Exception in proceedure [message #446216 is a reply to message #446206] Sat, 06 March 2010 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Documentation - it has a search facility, use it.
Re: Exception in proceedure [message #446224 is a reply to message #446206] Sat, 06 March 2010 09:19 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    cursor c1 is select dummy from dual where 1=1;
  3    cursor c2 is select dummy from dual where 1=2;
  4    cursor c3 is select dummy from dual where 1=1;
  5    v varchar2(10);
  6  begin
  7    v := '';
  8    open c1;
  9    fetch c1 into v;
 10    if c1%found then dbms_output.put_line('Found '||v||' in c1');
 11    else dbms_output.put_line('Found nothing in c1');
 12    end if;
 13  
 14    v := '';
 15    open c2;
 16    fetch c2 into v;
 17    if c2%found then dbms_output.put_line('Found '||v||' in c2');
 18    else dbms_output.put_line('Found nothing in c2');
 19    end if;
 20  
 21    v := '';
 22    open c3;
 23    fetch c3 into v;
 24    if c3%found then dbms_output.put_line('Found '||v||' in c3');
 25    else dbms_output.put_line('Found nothing in c3');
 26    end if;
 27  
 28    close c3;
 29    close c2;
 30    close c1;
 31  end;
 32  /
Found X in c1
Found nothing in c2
Found X in c3

PL/SQL procedure successfully completed.

Regards
Michel
Previous Topic: Not able to apply session tranforms
Next Topic: check_constraint
Goto Forum:
  


Current Time: Sun Dec 11 08:11:22 CST 2016

Total time taken to generate the page: 0.15426 seconds