No_data_found Oracle Error Code
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type
No Data Found Exception In Oracle
your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application oracle raise_application_error DevelopmentPL/SQL and SQL This discussion is archived 5 Replies Latest reply on Feb 25, 2010 8:15 AM by 693765 No Data Found
Oracle Raise Exception
in SELECT INTO 693765 Feb 25, 2010 8:03 AM 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 oracle sqlerrm 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;* oracle exception END LOOP; END MyProc ; Line: ----- Does anybody know how to set a variable when the select fails with NO DATA FOUND? Thanks dyahav 77830Views Tags: none (add) This content has been marked as final. Show 5 replies 1. Re: No Data Found in SELECT INTO Tubby Feb 25, 2010 8:05 AM (in response to 693765) You need to contain your exception with BEGIN and END BEGIN select into... EXCEPTION WHEN NO_DATA_FOUND do_something END; Like Show 0 Likes(0) Actions 2. Re: No Data Found in SELECT INTO 693765 Feb 25, 2010 8:08 AM (in response to Tubby) Thanks a lot for the quick response! Like Show 0 Likes(0) Actions 3. Re: No Data Found in SELECT INTO Tubby Feb 25, 2010 8:10 AM (in response to 693765) 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 han
22, 2013 - 1:56 pm UTC Category: Database � Version: 9.2 Latest Followup You Asked Tom, We´ve just migrated from Oracle 9.0.1 to 9.2 and, coincidence or not, I´m facing a problem I had never faced before. The NO_DATA_FOUND
Oracle Predefined Exceptions
exception is not being raised from my PL/SQL functions anymore!!! If I put a oracle exception when others exception block to handle the excpetion, it´s trapped and all the instructions in the block are executed. But if I remove
Functions For Error Trapping Are Contained In Which Section Of A Pl/sql Block
the exception block, no exception is raised to the caller. In procedures, this doesn´t happen. Some examples of this strange behavior follows: create or replace procedure proc_foo is l_value varchar2(1); begin select dummy into l_value https://community.oracle.com/thread/1036850?start=0&tstart=0 from dual where dummy = '-'; DBMS_OUTPUT.Put_Line(l_value); end; / Procedure created. create or replace function func_foo return varchar2 is l_value varchar2(1); begin select dummy into l_value from dual where dummy = '-'; return l_value; end; / Function created. -- This one raises an error begin proc_foo; end; / begin * ERROR at line 1: ORA-01403: no data found ORA-06512: at "AMESP.PROC_FOO", line 5 ORA-06512: at line 2 -- This one does https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:10321465390114 not select func_foo from dual; FUNC_FOO ---------------------------------------------- Is there some parameter (or anything else) that controls this? Thanks. and we said... it has ALWAYS been that way tkyte@ORA716> select * from v$version; BANNER ---------------------------------------------------------------- Oracle7 Server Release 7.1.6.2.0 - Production Release PL/SQL Release 2.1.6.2.0 - Production CORE Version 2.3.7.1.0 - Production (LSF Alpha) TNS for SVR4: Version 2.1.6.0.0 - Production NLSRTL Version 2.3.6.0.0 - Production tkyte@ORA716> exec proc_foo begin proc_foo; end; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "TKYTE.PROC_FOO", line 4 ORA-06512: at line 1 tkyte@ORA716> select func_foo from dual; no rows selected no data found in a function called from sql just says "ok, no more data, please stop" this is true for 71, 72, 73, 80, 8ir1, 8ir2, 8ir3, 9ir1, 9ir2.... Reviews Write a Review Is this behaviour because... June 10, 2003 - 9:46 pm UTC Reviewer: Kamal Kishore from New Jersey, USA Hi Tom, Is this behaviour because in case of SQL, for ANSI standard, the NO DATA FOUND is not an error condition (rather it is an indicator of so to say, END-OF-FILE or EOF). This just tells the system that what you were trying to read is no more to be found. In this regard, than it appears that Oracle P
easiest fix would be is to handle the error in the PL/SQL block, When a SQL statement is written within a PL/SQL block, enclose the http://www.orafaq.com/wiki/ORA-01403 SQL with a BEGIN and END statement. Handle the exception and raise a user-friendly message or handle the rest of the processing. Eg: CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS l_empname VARCHAR2(50); BEGIN SELECT empname INTO l_empname FROM emp WHERE empno = p_empno; IF l_empname = 'Sarah Jones' THEN INSERT INTO empresult values ('105', 'Found Sarah Jones'); END IF; END; / The oracle raise above procedure has not handled the error that would be raised if the select statement did not find the specified empno. See the following for the error raised: SQL> exec test_proc (1) begin test_proc (1); end; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "TAROT.TEST_PROC", line 4 ORA-06512: at line 1 If you want the procedure to execute properly without no_data_found oracle error errors on the screen, then you would have to handle the error. The Exception needs to be controlled by adding an exception handler to the code. The above code has been modified to handle an exception NO_DATA_FOUND. This is the name of the exception that the error relates to. CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS l_empname VARCHAR2(50); BEGIN SELECT empname INTO l_empname FROM emp WHERE empno = p_empno; IF l_empname = 'Sarah Jones' THEN INSERT INTO empresult values ('105', 'Found Sarah Jones'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones'); END; / If you execute the above procedure now, this is what you would see: SQL> exec test_proc (1) PL/SQL procedure successfully completed. SQL> select * 2 from empresult; EMPNO EMPNAME --------- -------------------------------------------------- 1 Did not find Sarah Jones As the exception was handled, a row was inserted into the empresult table with the error message you specified. When the exception is raised, the control jumps from the select statement to the exception handler routine. Any code after the Select will not get executed if an exception has b