5 Ответы Последний ответ: 25.02.2010 8:15, автор: 693765

    No Data Found in SELECT INTO

    693765
      Hi all,

      I try to select into a variable in a cursor loop but I get NO DATA FOUND when
      the select doesn't return a value.
      I want to set the variable to be 0 when the select fails.

      I tried to add an exception but I get the following:

      ORA-00900: invalid SQL statement


      Here is the code I'm trying to execute (The exception is commented):

      Line: -----
      PROCEDURE MyProc IS
      v_op NUMBER;
      BEGIN

      FOR cursor1 IN (select * from myTable)
      LOOP
      SELECT operation INTO v_op FROM operationsTable where id=cursor1.ID;


      --EXCEPTION*
      --    WHEN NO_DATA_FOUND THEN
      --      v_op :=0;

      --END;*

      END LOOP;
      END MyProc ;
      Line: -----

      Does anybody know how to set a variable when the select fails with NO DATA FOUND?
      Thanks
      dyahav
        • 1. Re: No Data Found in SELECT INTO
          Tubby
          You need to contain your exception with BEGIN and END
          BEGIN
          
          select into...
          
          EXCEPTION WHEN NO_DATA_FOUND
          do_something
          END;
          • 2. Re: No Data Found in SELECT INTO
            693765
            Thanks a lot for the quick response!
            • 3. Re: No Data Found in SELECT INTO
              Tubby
              Also, this would be much better code than the slow by slow approach you are undertaking now.
              PROCEDURE MyProc IS
              v_op NUMBER;
              BEGIN
              
              FOR cursor1 IN 
              (
                 select 
                    nvl(o.operation, 0)
                 from myTable t, operationsTable o
                 where t.id = o.id (+)
              )
              LOOP
                 --presumably some processing here?
              END LOOP;
              END MyProc ;
              Join the tables, databases are good at doing this, which you're basically trying to do yourself .... and you'll only make the performance bad by doing that.

              Since you aren't handling (or trying to handle) the TOO_MANY_ROWS exception i am assuming there is a 1 - 1 (optional) relationship between your tables.
              • 4. Re: No Data Found in SELECT INTO
                Prazy
                Hi,

                Exception block should come outside the loop. i.e. it should be at the end of the block statement.
                PRAZY@solarc> create table emp_temp as select * from emp where 1=3;
                
                Table created.
                
                Elapsed: 00:00:00.03
                PRAZY@solarc> ed
                Wrote file afiedt.buf
                
                  1  declare
                  2  a number;
                  3  begin
                  4  <<mainblock>>
                  5  begin
                  6  for c1 in (select * from emp) loop
                  7  select empno into a from emp_temp where empno=c1.empno;
                  8  end loop;
                  9  exception when no_data_found then
                 10  a:=0;
                 11  end mainblock;
                 12  dbms_output.put_line(a);
                 13* end;
                PRAZY@solarc> /
                0
                
                PL/SQL procedure successfully completed.
                Regards,
                Prazy
                • 5. Re: No Data Found in SELECT INTO
                  693765
                  The code given by me was only an example. But thanks anyway