Ora-06512 No Data Found Error
Contents |
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 ora-01403 no data found in oracle site About Us Learn more about Stack Overflow the company Business Learn more
No Data Found Exception In Oracle
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Ora-01403 No Data Found Select Into
Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORACLE: NO DATA
Ora-01403 No Data Found Exception Handling
FOUND — but data exists up vote 2 down vote favorite Debugging a package procedure and am getting a no data found when there is in fact data. Testing just the SELECT SELECT trim(trailing '/' from GL_SECURITY) as DUMMY FROM b2k_user@b2k WHERE sms_username = 'FUCHSB'; This happily returns my value : '23706*706' As soon as i try to have this selected INTO i get ora-01403 no data found in oracle apps a NO_DATA _FOUND error (commented out the error handling i put in) set serveroutput on DECLARE p_BAS_user_name varchar2(20); v_gl_inclusion varchar2(1000); v_gl_exclusions varchar2(1000); BEGIN --inputs p_BAS_user_name := 'FUCHSB'; dbms_output.put_line(p_BAS_user_name); ----- GOOD ----- --BEGIN SELECT trim(trailing '/' from GL_SECURITY) as DUMMY INTO v_gl_inclusion FROM b2k_user@b2k WHERE sms_username = p_BAS_user_name; --EXCEPTION -- WHEN NO_DATA_FOUND THEN -- v_gl_inclusion := 'SUPER EFFING STUPID'; --END; dbms_output.put_line(v_gl_inclusion); END; Error report: ORA-01403: no data found ORA-06512: at line 12 01403. 00000 - "no data found" *Cause: *Action: FUCHSB I can catch the error just fine except for the fact that based on the 1st query i know 100% there is a value for FUCHSB in the database. Any ideas.. I'm really starting to despise Oracle. Yes this query is being run over a datalink as seen in the 1st query the data is there. Thanks SOLVED strange behavior in SQL developer caused me to overlook potential whitespace: It looks as though SQL Developer when running the standalone select applies its own trimming comparator when doing the 'WHERE sms_username = p_BAS_user_name;' portion.. turns out when sitting in the package it does not.. bunch of white space was causing the issue.. s
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 ora-01403 no data found in package and END statement. Handle the exception and raise a user-friendly message or handle oracle no data found exception example 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 ora 01403 no data found in forms 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 above procedure has not handled the error http://stackoverflow.com/questions/8401785/oracle-no-data-found-but-data-exists 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 errors on the screen, then you would have to handle the http://www.orafaq.com/wiki/ORA-01403 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 been raised. But if you do have some code that needs to be executed after the select has been executed, irrespective of whether
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 https://community.oracle.com/thread/699262 it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 1 2 no data Previous Next 24 Replies Latest reply on Feb 7, 2013 11:52 AM by 978878 PL/SQL 101 : Exception Handling BluShadow Jun 14, 2013 10:35 AM Frequently I see questions and issues around the use of Exception/Error Handling in PL/SQL. More often than not the issue comes from the no data found questioners misunderstanding about how PL/SQL is constructed and executed, so I thought I'd write a small article covering the key concepts to give a clear picture of how it all hangs together. (Note: the examples are just showing examples of the exception handling structure, and should not be taken as truly valid code for ways of handling things)Exception HandlingContents1. Understanding Execution Blocks (part 1)2. Execution of the Execution Block3. Exceptions4. Understanding Execution Blocks (part 2)5. How to continue exection of statements after an exception6. User defined exceptions7. Line number of exception8. Exceptions within code within the exception block1. Understanding Execution Blocks (part 1)The first thing that one needs to understand is almost taking us back to the basics of PL/SQL... how a PL/SQL execution block is constructed.Essentially an execution block is made of 3 sections... +---------------------------+| Declaration Section |+---------------------------+| Statements Section |+-----------