Forum Stats

  • 3,826,400 Users
  • 2,260,641 Discussions
  • 7,896,931 Comments

Discussions

Categories

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
· Share on TwitterShare on Facebook

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;
    · Share on TwitterShare on Facebook
  • 693765
    693765 Member Posts: 74
    Thanks a lot for the quick response!
    · Share on TwitterShare on Facebook
  • 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.
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 693765
    693765 Member Posts: 74
    The code given by me was only an example. But thanks anyway
    · Share on TwitterShare on Facebook
This discussion has been closed.