Home » SQL & PL/SQL » SQL & PL/SQL » Handling Exceptions! (Oracle 10g)  () 1 Vote
Handling Exceptions! [message #571651] Wed, 28 November 2012 09:34 Go to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Hi guys! I have an issue with handling exceptions, here is my problem. If i make an anominous block with a cursor and if the cursor didn't retrieve any data, it should trigger the exception no_data_found if I am not mistaken but it didn't show any data wich is correct because the ticket that I am trying to retrieve does not exist in the BD but it does not trigger the exception either.

Help you help me out ?

Here is the code


    DECLARE

    cursor c_data is

    select * from audit_wa
    where ticket = '0000000000000000000000000000001';

    BEGIN

    for i in c_data loop

    DBMS_OUTPUT.PUT_LINE('ID: ' || i.id);

    END LOOP;

    EXCEPTION
       
       WHEN NO_DATA_FOUND
       THEN
          DBMS_OUTPUT.put_line ('Ticket does not exist');
    END;



Thanks in advance.-

[Updated on: Wed, 28 November 2012 09:36]

Report message to a moderator

Re: Handling Exceptions! [message #571652 is a reply to message #571651] Wed, 28 November 2012 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+no_data_found

[Updated on: Wed, 28 November 2012 09:42]

Report message to a moderator

Re: Handling Exceptions! [message #571653 is a reply to message #571651] Wed, 28 November 2012 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it should trigger the exception no_data_found if I am not mistaken


You are.
FOR cursor loop does not raise this exception as it handles it, and it is one of its purposes.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Handling Exceptions! [message #571660 is a reply to message #571653] Wed, 28 November 2012 11:06 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Here I found a posible solution for my issue

    DECLARE

    cursor c_data is

    select * from wa_audit
    where ticket = 'INC000003154946';
    
    V_ERROR   EXCEPTION;
    v_data    c_data%rowtype;

    BEGIN
    OPEN c_data;
    
    LOOP
            FETCH c_data into v_data;
            
            IF (c_data%rowcount > 0) THEN
        
            EXIT WHEN c_data%notfound;
            DBMS_OUTPUT.put_line ('ID: ' || v_data.id);
             
            ELSE
            
            RAISE V_ERROR;
            
            END IF;
    END LOOP;

    

    EXCEPTION
       
       WHEN V_ERROR
       THEN
          DBMS_OUTPUT.put_line ('It does not exist');
          
         
          
    END;

Re: Handling Exceptions! [message #571662 is a reply to message #571660] Wed, 28 November 2012 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
Flawed implementation!
In production environment, nobody will ever see any DBMS_OUTPUT.
What happens when different EXCEPTION occurs?
DECLARE 
    CURSOR c_data IS 
      SELECT * 
      FROM   wa_audit 
      WHERE  ticket = 'INC000003154946'; 
    v_data c_data%ROWTYPE; 
BEGIN 
    OPEN c_data; 

    LOOP 
        FETCH c_data INTO v_data; 

        IF ( c_data%rowcount > 0 ) THEN 
          EXIT WHEN c_data%NOTFOUND; 

          dbms_output.Put_line ('ID: ' 
                                || v_data.id); 
        ELSE 
          dbms_output.Put_line ('It does not exist'); 
        END IF; 
    END LOOP; 
END; 
Re: Handling Exceptions! [message #571664 is a reply to message #571660] Wed, 28 November 2012 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is over-complicated, so much that I'm not sure it is correct.

For instance:
Quote:
IF (c_data%rowcount > 0) THEN
EXIT WHEN c_data%notfound;

If rowcount > 0 then you can't have "c_data%notfound;", so this line is useless.
Do you try to obfuscate what your code does?
Do you try to make the maintenance guy's job a nightmare?

Why don't you just have a counter in your cursor FOR loop?
Or check rowcount at the end of the loop?

SQL> declare curs sys_refcursor; rec emp%rowtype;
  2  begin
  3    open curs for select * from emp;
  4    loop
  5      fetch curs into rec;
  6      exit when curs%notfound;
  7    end loop;
  8    dbms_output.put_line('Rowcount is: '||curs%rowcount);
  9    close curs;
 10  end;
 11  /
Rowcount is: 14

PL/SQL procedure successfully completed.


Regards
Michel

[Updated on: Wed, 28 November 2012 11:23]

Report message to a moderator

Re: Handling Exceptions! [message #571667 is a reply to message #571662] Wed, 28 November 2012 11:29 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Dear BlackSwan!

Nobody will ever seen the DBMS because it was only for testing and I am not going to put that code in production environment, I had only the issue and comes to my mind try to solve it. To be honest I didn't know that a cursor does not throw the no_data_found exception and I wanted it to throw one, then I realize that I had to create my own and raise it.

Thank you for everything to everyone!

Regards,
Esteban.
Re: Handling Exceptions! [message #571668 is a reply to message #571667] Wed, 28 November 2012 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then I realize that I had to create my own and raise it.


Wrong, see the code I posted.

Regards
Michel
Re: Handling Exceptions! [message #571670 is a reply to message #571668] Wed, 28 November 2012 11:39 Go to previous messageGo to next message
akull
Messages: 41
Registered: July 2012
Location: Argentina
Member
Thank you Sir. Once again it was only for testing and I know it is complicated and obfuscating but I wanted to raise an exception within a cursor. I really do know that it had been easier the way you did (with IF-ELSE statement)

Regards,
Esteban.
Re: Handling Exceptions! [message #571671 is a reply to message #571668] Wed, 28 November 2012 11:41 Go to previous message
BlackSwan
Messages: 23060
Registered: January 2009
Senior Member
>I wanted it to throw one,
why this obsession with throwing an EXCEPTION when it adds NOTHING to the code results?
Previous Topic: Time Comparison
Next Topic: Creating and refreshing materialized view group from another schema
Goto Forum:
  


Current Time: Thu Nov 27 12:39:24 CST 2014

Total time taken to generate the page: 0.12308 seconds