Forum Stats

  • 3,826,393 Users
  • 2,260,641 Discussions
  • 7,896,930 Comments

Discussions

No Data Found in SELECT INTO

693765
693765 Member Posts: 74
edited Feb 25, 2010 3:15AM in SQL & PL/SQL
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
simon87

Best Answer

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Answer ✓
    You need to contain your exception with BEGIN and END
    BEGIN
    
    select into...
    
    EXCEPTION WHEN NO_DATA_FOUND
    do_something
    END;
  • 693765
    693765 Member Posts: 74
    Thanks a lot for the quick response!
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    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.
  • Prazy
    Prazy Member Posts: 822 Silver Badge
    Hi,

    Exception block should come outside the loop. i.e. it should be at the end of the block statement.
    [email protected]> create table emp_temp as select * from emp where 1=3;
    
    Table created.
    
    Elapsed: 00:00:00.03
    [email protected]> 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;
    [email protected]> /
    0
    
    PL/SQL procedure successfully completed.
    Regards,
    Prazy
  • 693765
    693765 Member Posts: 74
    The code given by me was only an example. But thanks anyway
This discussion has been closed.