1403 Sql Error
Contents |
easiest fix would be is to handle the error in the PL/SQL block, When a SQL statement is written sql error 1403 mapping within a PL/SQL block, enclose the SQL with a BEGIN and
Oracle Error 1403
END statement. Handle the exception and raise a user-friendly message or handle the rest of the processing. Eg:
Ora 1403
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
Sql Error Code 1403
INSERT INTO empresult values ('105', 'Found Sarah Jones'); END IF; END; / The 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 ora 01403 error in oracle 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 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 e
Library mySQL Code Library PHP Code Library JavaScript Code Library Oracle Terms java.sql.sqlexception: ora-01403: no data found & Definitions Oracle Error Codes PSOUG Community Blogs Oracle Jobs ora-01403 no data found ora-06512 Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations ora-01403 no data found select into Advanced Code Search News and Events Sponsors Page Submit Code Contact Us Oracle Error: ORA-01403 Error Description: No data found Error http://www.orafaq.com/wiki/ORA-01403 Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query. In other words, this error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data. This may http://psoug.org/oraerror/ORA-01403.htm be because you executed a SELECT INTO statement and no rows were returned, or you referenced an uninitialized row in a table, or because you read past the end of file with the UTL_FILE package. Action: Terminate processing for the SELECT statement. This error is often associated with the SELECT INTO clause. To avoid ORA-01403, the PL/SQL has to contain exceptions or the query will offer no values to the defined variable. If the exceptions are not defined properly, you will encounter ORA-01403. There haven't been any comments added for this error yet. You may add one if you like. Add a comment Name: Email: URL: Chars left:1000 (1000 max) (No HTML, but newlines will be preserved) Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 129 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS https://www.techonthenet.com/oracle/errors/ora01403.php Color Picker Languages C Language More ASCII Table Linux UNIX http://stackoverflow.com/questions/22026100/oracle-pl-sql-ora-01403-no-data-found-when-using-select-into Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR sql error 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 Learn the no data found 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 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, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL - ORA-01403 “No data found” when using “SELECT INTO” up vote 4 down vote favorite I faced this problem while developing a Trigger in Oracle: ORA-01403: no data found. I did some research and understood the root of the problem. Nevertheless handling the error exception prevents the above error, but does not solve my problem. What I am currently looking for is an optimal workaround to perform the lesser query amount/achieve the best performance as possible. I'll try to describe the scenario creating simple examples to the real structure. Scenario I have a "date reference" table to establish periods of time, say: CREATE TABLE DATE_REFERENCE ( DATE_START DATE NOT NULL, DATE_END DATE NOT NULL, -- Several other columns here, this is just a silly example CONSTRAINT PK_DATE_REFERENCE PRIMARY KEY(DATE_START, DATE_END) ); When the trigger is triggered, I'll have one DATE field - say DATE_GIVEN (for example sake). What I need is: To find the DATE_REFERENCE row in which DATE_GIVEN BETWEEN DATE_START AND DATE_END (easy); OR If the previous option returns no data, I need to find the next closest DATE_START to DATE_GIVEN. In both cases, I need to retrieve the row with all columns from table DATE_REFERENCE, no matter if it matches Opt 1 or 2. That's exactly where I faced the problem described. I wrote this test block to test and try to find a solution. The example below is not working, I know; but it is exactly what I want to accomplish (in concept). I have added comments like -- Lots of code to make clear that will be part of a more elaborate trigger: DECLARE DATE_GIVEN DATE; RESULTROW DATE_REFERENCE%ROWTYPE; BEGIN -- Lots of code -- Lots of code -- Lots of code DATE_GIVEN := TO_DATE('2014-02-26 12:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- This one throws the ORA-01403 exception if no data was found SELECT * INTO RESULTROW FROM DATE_REFERENCE WHERE DATE_GIVEN BETWEEN DATE_START AND DATE_END; -- If