Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
No Data Found in SELECT INTO

693765
Member Posts: 74
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
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
Best Answer
-
You need to contain your exception with BEGIN and END
BEGIN select into... EXCEPTION WHEN NO_DATA_FOUND do_something END;
Answers
-
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.
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. -
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 -
The code given by me was only an example. But thanks anyway
This discussion has been closed.