How To Avoid No Data Found Error In Oracle
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and no data found exception in oracle policies of this site About Us Learn more about Stack Overflow the company oracle no data found exception example Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users ora-01403 no data found in oracle Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a ora-01403 no data found ora-06512 minute: Sign up PL/SQL block problem: No data found up vote 25 down vote favorite 6 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; CASE -- outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTPUT.PUT_LINE ('There is no final
Pl Sql No Data Found Continue
grade.'); ELSE CASE -- inner CASE WHEN v_final_grade >= 90 THEN v_letter_grade := 'A'; WHEN v_final_grade >= 80 THEN v_letter_grade := 'B'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 60 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END CASE; -- control resumes here after inner CASE terminates DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade); END CASE; -- control resumes here after outer CASE terminates END; the above code i have taken from the book oracle pl-sql by example fourth edition 2009 my problem is when i enter a student id not present in the table it returns me the following errors Error report: ORA-01403: no data found ORA-06512: at line 7 01403. 00000 - "no data found" *Cause: *Action: but according to book it should have returned a null value and then follow the case flow.. please help me out... thanks in advance sql oracle plsql oracle10g ora-01403 share|improve this question edited Feb 12 '11 at 1:33 OMG Ponies 199k37356416 asked Aug 10 '09 at 17:21 Orapps 141237 Any chance of formatting your PL SQL as code? –pjp
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,
Ora 01403 No Data Found Ora 06512 In Oracle
coincidence or not, I´m facing a problem I had never faced before. ora-01403 no data found select into The NO_DATA_FOUND exception is not being raised from my PL/SQL functions anymore!!! If I put a exception select into no data found exception handling block to handle the excpetion, it´s trapped and all the instructions in the block are executed. But if I remove the exception block, no exception is raised to the http://stackoverflow.com/questions/1256112/pl-sql-block-problem-no-data-found 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 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 = https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:10321465390114 '-'; 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 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 Rev
get annoying error ORA-01403: No data found and execution stops. One way to get around this error is to add exception block in our PLSQL procedure as shown https://avdeo.com/2011/04/29/avoiding-no-rows-found-tips/ below EXCEPTION WHEN NO_DATA_FOUND THEN
Digital Records Management Enterprise Content Management Strategy Digital Asset Management Oracle Imaging & Process Management Web Content Management Oracle WebCenter Portal Enterprise Portal Support Enterprise Portal Strategy Enterprise Portal Upgrade Oracle WebCenter Sites Sourcing Staffing & Recruiting Recruiting Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & Dynamic Discounting Oracle WebCenter Contract Lifecycle Management (CLM) Search ORA-01403: no data foundYou are here: Home / Resources / ORA-01403: no data found ORA-01403 Error Message: "No Data Found" Some Oracle errors are a result of coding that does not prove quite as comprehensive as desired. These types of errors involve going back through your code and creating or editing your PL/SQL to compensate for the mistake. The ORA-01403 error ("no data found") is representative of this type of Oracle problem, and while it involves a bit more coding savvy than other errors, the fix tends to be much more long term. The ORA-01403 error derives from an SQL query meant to return all data requested, but no data was found. The error is often associated with SELECT INTO clauses, which fetch rows or sets of columns from a database. The data gathered is then inserted into a set of predefined variables. When a SELECT INTO query fails to return a single row, the ORA-01403 error is triggered in the system. The error can also be thrown when you attempt to select data from an uninitialized row in a table, or reading past the end of file with the UTF_FILE package. To initially work around the error, you need to terminate the processing data immediately, but what from there? To avoid this type of situation from coming about, the best approach is to make sure that your PL/SQL has the proper exceptions coded. Without exceptions, the query will attempt to offer multiple values to the variables at the end of the query. If this continues and the exceptions are left non-existent or incomplete, each time the variable is accessed again the problem will compound. Create an exception that would allow only one row to be retrieved each time the variable is accessed. Writing an exception that states "when NO_DATA_FOUND then return ‘No data in selected variable'". By creating the proper exceptions