Error Message Ora-01403 No Data Found Ora-01403 No Data Found
Contents |
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 SQL with a BEGIN and END statement. ora 01403 no data found ora 06512 in oracle Handle the exception and raise a user-friendly message or handle the rest of ora 01403 no data found in forms 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 ora-01403 no data found in oracle WHERE empno = p_empno; IF l_empname = 'Sarah Jones' THEN INSERT INTO empresult values ('105', 'Found Sarah Jones'); END IF; END; / The above procedure has not handled the error that would be raised if ora-01403: no data found ora-06512 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 errors on the screen, then you would have to handle the error. The Exception needs to be controlled by
Ora-01403 No Data Found In Package
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 been raised. But if you do have some code that needs to be executed after the select has been executed, irrespective of whether the select was successful or not, then you would need to nest the begin and
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color
Ora-01403 No Data Found Select Into
Picker Languages C Language More ASCII Table Linux UNIX ora-01403 no data found in oracle apps Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON ora-01403 no data found exception handling OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR http://www.orafaq.com/wiki/ORA-01403 ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-01403 Error Message Learn https://www.techonthenet.com/oracle/errors/ora01403.php the cause and how to resolve the ORA-01403 error message in Oracle. Description When you encounter an ORA-01403 error, the following error message will appear: ORA-01403: no data found Cause You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package. Resolution The option(s) to resolve this Oracle error are: Option #1 Terminate processing of the data. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/9104153/what-is-the-correct-way-to-deal-with-this-oracle-ora-01403-no-data-found-except Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, no data just like you, helping each other. Join them; it only takes a minute: Sign up What is the correct way to deal with this Oracle ORA-01403: no data found Exception? up vote 5 down vote favorite 1 I have a DB table that I am more or less treating like a queue. I'm trying to fetch no data found a single item from it. This works, except when the SELECT..INTO fails (which can happen if there is only one item in the queue and two users on separate machines try and fetch it; only one is going to win). This leads to the familiar ORA-01403: no data found exception. I tried to change the SP so that it would return NULL record in this case - the same sort of result you'd get it a query couldn't find any records - but to no avail. I am doing something wrong here. PROCEDURE sp_GetNextEmailFromQueue (pAgentId IN NUMBER, pRecs OUT recordSet) IS EMAIL_ID INTEGER; BEGIN SELECT id INTO EMAIL_ID FROM (SELECT id, is_replied_to, is_being_worked, date_received FROM SSQ_EMAILS WHERE is_replied_to = 0 AND is_being_worked =0 ORDER BY date_received ASC) WHERE rownum = 1; UPDATE SSQ_EMAILS x SET x.is_being_worked = 1, x.agent_id = pAgentId, x.work_started_date = SYSDATE WHERE x.id = EMAIL_ID; OPEN pRecs FOR SELECT x.id, x.message_id, x.to_email, x.from_email, x.subject, x.message, x.date_received, x.href_link, x.is_being_worked, x.work_started_date, x.is_replied_to FROM SSQ_EMAILS