This content has been marked as final. Show 5 replies
You need to contain your exception with BEGIN and END
BEGIN select into... EXCEPTION WHEN NO_DATA_FOUND do_something END;
Thanks a lot for the quick response!
Also, this would be much better code than the slow by slow approach you are undertaking now.
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.
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 ;
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.
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.
The code given by me was only an example. But thanks anyway