3 Replies Latest reply: Jun 4, 2013 1:52 AM by Karthick_Arp RSS

    ORA-01403: no data found

    Ora_83
      Hi

      The following procedure works fine when the select statement returns a row.
      However, it is generating "ORA-01403: no data found" error when no data is returned from select.

      When I put following exception after the select, It doest go inside if block. ( It stops after select). Not sure why.
      exception
      when NO_DATA_FOUND then null;
      How can I put an exception for this?
      CREATE OR REPLACE PROCEDURE kill_blocking
      IS
      l_sid number(10); 
      l_serial number(10);
      l_wait number(10);
      l_username varchar2(20);
      sqlStmt VARCHAR2(1000);
      BEGIN
      
      SELECT s1.sid,s1.serial#,s2.seconds_in_wait,s2.username
      into l_sid,l_serial,l_wait,l_username
      FROM v$lock l1, v$session s1, v$lock l2,v$session s2,v$sql sqlt1, v$sql sqlt2
      WHERE s1.sid =l1.sid
      AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
      AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
      
      if l_username='APP' and l_wait > 100
      then 
      sqlStmt := 'ALTER SYSTEM KILL SESSION ''' || TO_CHAR(l_sid) ||',' || TO_CHAR(l_serial) ||'''' ;
      dbms_output.put_line( sqlStmt );
      
      EXECUTE IMMEDIATE sqlStmt;
      
      
      END IF;
      END;
      / 
        • 1. Re: ORA-01403: no data found
          dariyoosh
          Hi,
          Ora_83 wrote:
          When I put following exception after the select, It doest go inside if block. ( It stops after select). Not sure why.
          exception
          when NO_DATA_FOUND then null;

          Because, this is how exceptions work. When an exception is raised, the normal flow of the program is stopped and oracle looks for a handler (if any).
          How can I put an exception for this?
          It depends on what you want to do ( = how do you want to handle the exception) when there is no data

          For more information about Exceptions, you can read the following

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#CHDFGBJI

          Regards,
          Dariyoosh

          Edited by: dariyoosh on Jun 4, 2013 8:43 AM
          • 2. Re: ORA-01403: no data found
            S10390
            Check this
            SQL> CREATE OR REPLACE PROCEDURE kill_blocking IS
              2     l_sid        NUMBER (10);
              3     l_serial     NUMBER (10);
              4     l_wait       NUMBER (10);
              5     l_username   VARCHAR2 (20);
              6     sqlstmt      VARCHAR2 (1000);
              7  BEGIN
              8     SELECT s1.SID, s1.serial#, s2.seconds_in_wait, s2.username
              9       INTO l_sid, l_serial, l_wait, l_username
             10       FROM v$lock l1,
             11            v$session s1,
             12            v$lock l2,
             13            v$session s2,
             14            v$sql sqlt1,
             15            v$sql sqlt2
             16      WHERE s1.SID = l1.SID
             17        AND s2.SID = l2.SID
             18        AND sqlt1.sql_id = s2.sql_id
             19        AND sqlt2.sql_id = s1.prev_sql_id
             20        AND l1.BLOCK = 1
             21        AND l2.request > 0
             22        AND l1.id1 = l2.id1
             23        AND l2.id2 = l2.id2;
             24
             25     IF l_username = 'APP' AND l_wait > 100
             26     THEN
             27        sqlstmt :=
             28              'ALTER SYSTEM KILL SESSION '''
             29           || TO_CHAR (l_sid)
             30           || ','
             31           || TO_CHAR (l_serial)
             32           || '''';
             33        DBMS_OUTPUT.put_line (sqlstmt);
             34
             35        EXECUTE IMMEDIATE sqlstmt;
             36     END IF;
             37  EXCEPTION
             38     WHEN NO_DATA_FOUND
             39     THEN
             40
             41        DBMS_OUTPUT.put_line ('No Data Found');
             42  END;
             43  /
            No Data Found
            
            PL/SQL procedure successfully completed.
            • 3. Re: ORA-01403: no data found
              Karthick_Arp
              It all depends on where you place your exception. If you want the code to proceed even after SELECT returns no data then you need to place the exception accordingly.
               
              create or replace procedure kill_blocking 
              is 
                  l_sid       number(10); 
                  l_serial    number(10); 
                  l_wait      number(10); 
                  l_username  varchar2(20); 
                  sqlstmt     varchar2(1000); 
              begin 
                  begin 
                      select s1.sid 
                           , s1.serial# 
                           , s2.seconds_in_wait 
                           , s2.username 
                        into l_sid 
                           , l_serial 
                           , l_wait 
                           , l_username 
                        from v$lock l1 
                           , v$session s1 
                           , v$lock l2 
                           , v$session s2 
                           , v$sql sqlt1 
                           , v$sql sqlt2 
                       where s1.sid ;      = l1.sid 
                         and s2.sid ;      = l2.sid 
                         and sqlt1.sql_id = s2.sql_id 
                         and sqlt2.sql_id = s1.prev_sql_id 
                         and l1.block ;    = 1 
                         and l2.request ;  > 0 
                         and l1.id1 ;      = l2.id1 
                         and l2.id2 ;      = l2.id2; 
                  exception 
                      when no_data_found then 
                          null; 
                  end; 
              
                  if l_username='app' and l_wait > 100 
                  then 
                      sqlstmt := 'alter system kill session ''' || to_char(l_sid) ||',' || to_char(l_serial) ||'''' ; 
                      dbms_output.put_line( sqlstmt ); 
              
                      execute immediate sqlstmt; 
                  end if; 
              end; 
              / 
              Take some time to read the FAQ on {message:id=9360013}

              Said that the primary question you need to ask is why a session is being BLOCKED. Just killing them periodically with a procedure will not solve your issue. Identify the root cause and fix it. Then you will not need such procedure.