Error 01403
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 ora-01403 no data found ora-06512 block, enclose the SQL with a BEGIN and END statement. Handle the ora-01403 no data found in package exception and raise a user-friendly message or handle the rest of the processing. Eg: CREATE OR REPLACE PROCEDURE ora-01403 no data found select into 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
Ora-01403 No Data Found In Oracle Apps
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 found ORA-06512: at "TAROT.TEST_PROC", line 4 ORA-06512: at line 1 ora 01403 no data found apex 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 empresult table with the error message you specified. When the exception is raised, the control jumps from the select statem
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings frm-40735 ora-01403 and policies of this site About Us Learn more about Stack Overflow
Ora 01403 No Data Found In Forms
the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Ora-01403 No Data Found Exception Handling
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 http://www.orafaq.com/wiki/ORA-01403 takes a minute: Sign up Why is no_data_found ORA-01403 an exception in Oracle? up vote 12 down vote favorite 2 If the SELECT INTO statement doesn't return at least one row, ORA-01403 is thrown. For every other DBMS I know this is normal on a SELECT. Only Oracle treats a SELECT INTO like this. CREATE OR REPLACE PROCEDURE no_data_proc IS http://stackoverflow.com/questions/3940522/why-is-no-data-found-ora-01403-an-exception-in-oracle dummy dual.dummy%TYPE; BEGIN BEGIN SELECT dummy INTO dummy FROM dual WHERE dummy = 'Y'; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('Why is this needed?'); END; END no_data_proc; Why? In my opinion you don't need this exception really. It is too much overhead. Sometimes it is handy but you have to write a whole BEGIN, EXCEPTION, WHEN, END Block. Are there any essential reasons I don't see? oracle exception exception-handling plsql ora-01403 share|improve this question edited Feb 12 '11 at 1:33 OMG Ponies 198k36356415 asked Oct 15 '10 at 8:12 Stephan Schielke 1,10551735 4 Don't forget to catch TOO_MANY_ROWS when the select returns more than one row. –Rene Oct 15 '10 at 13:19 add a comment| 6 Answers 6 active oldest votes up vote 15 down vote accepted The exception block is not needed, you might use it or not, depending on the context. Here you are actively ignoring the exception (the procedure will return successfully) but most of the time if you're doing a SELECT INTO you want it to fail if it doesn't return a row, consider: PROCEDURE update_employee_salary (
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 http://stackoverflow.com/questions/22026100/oracle-pl-sql-ora-01403-no-data-found-when-using-select-into 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 no data 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 no data found 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 AN