Error 100 Ora-01403 No Data Found
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ora 01403 no data found ora 06512 in oracle ORA-01403: no data found tips Oracle Error TipsOra 01403 No Data Found In Forms
by Burleson Consulting Oracle docs note this about ORA-01403: ORA-01403 no data found Cause: In a host
Ora 01403 No Data Found Insert Statement
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. Action: Terminate processing for the SELECT statement Many
Ora-01403 No Data Found In Package
people experience ORA-01403 in association with the SELECT INTO clause. SELECT INTO clauses are standard SQL queries which pull a row or set of columns from a database, and put the retrieved data into variables which have been predefined. If the SELECT INTO statement doesn't return at least on e row, ORA-01403 is thrown. Here is an example: SQL> declare 2 v_authName author.author_last_name%type; 3 begin 4 select 5 author_last_name into ora-01403 no data found ora-06512 v_authName 6 from 7 author 8 where 9 author_state = 'FL'; 10 dbms_output.put_line('Name: '||v_authName); 11 end; 12 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 Because the query is looking to retrieve authors in Florida and there are none, the ORA-01403 error is thrown. To avoid ORA-01403, the PL/SQL has to contain exceptions , otherwise the query will offer to values to the defined variable. If the exceptions are not defined properly, you will encounter ORA-01403, and your situation may worsen each time you attempt to access the particular variable. Creating an exception in which only one row can be retrieved would allow the code to appropriately handle the exception without receiving the ORA-01403 error. To create this SELECT INTO query that will avoid the ORA-01403 error, and handle the appropriate exceptions, here is an example: SQL> create or replace function auth_Name 2 ( v_auth_state IN author.author_state%type) 3 return varchar2 4 as 5 v_authName author.author_last_name%type; 6 begin 7 select 8 author_last_name into v_authName 9 from 10 author 11 where 12 author_state = v_auth_state; 13 return v_authName; 14 exception 15 when TOO_MANY_ROWS 16 then return 'Too Many Authors in that State'; 17 when NO_DATA_FOUND 18 then return 'No Authors in that S
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 ora-01403 no data found select into or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question ora-01403 no data found in oracle apps 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; ora-01403 no data found exception handling 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 http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm 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 http://stackoverflow.com/questions/22026100/oracle-pl-sql-ora-01403-no-data-found-when-using-select-into 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 the above didn't throw exceptions, I would continue like so: IF RESULTROW IS NULL THEN SELECT * INTO RESULTROW FROM DATE_REFERENCE WHERE DATE_START > DATE_GIVEN AND ROWNUM = 1 ORDER B
Exception Handling Raised Exceptions Useful Techniques There is nothing more exhilarating than to be shot at without result. Winston Churchill Runtime errors arise from https://docs.oracle.com/cd/A57673_01/DOC/server/doc/PLS23/ch6.htm design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to http://www.java2s.com/Code/Oracle/System-Packages/ORA01403nodatafoundexceptionfromprocedure.htm your PL/SQL program. With many programming languages, unless you disable error checking, a runtime error such as stack overflow or division by zero stops normal processing and returns no data control to the operating system. With PL/SQL, a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors. Overview In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined no data found exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. In the example
ExpressionsReport Column PageResult SetSelect QuerySequenceSQL PlusStored Procedure FunctionSubquerySystem PackagesSystem Tables ViewsTableTable JoinsTriggerUser PreviliegeViewXMLORA-01403: no data found exception from procedure : ORA Error«System Packages«Oracle PL / SQLOracle PL / SQLSystem PackagesORA ErrorORA-01403: no data found exception from procedure SQL> CREATE TABLE emp ( 2 id NUMBER PRIMARY KEY, 3 fname VARCHAR2(50), 4 lname VARCHAR2(50) 5 ); Table created. SQL> SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B'); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D'); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F'); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H'); 1 row created. SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z'); 1 row created. SQL> SQL> PROMPT as bind variables as bind variables SQL> SQL> CREATE OR REPLACE PROCEDURE bind_test (i_emp_fname IN emp.fname%TYPE) 2 IS 3 v_emp_lname emp.lname%TYPE; 4 BEGIN 5 SELECT lname INTO v_emp_lname FROM emp WHERE fname = i_emp_fname; 6 7 DBMS_OUTPUT.PUT_LINE(i_emp_fname||' has a last name of '||v_emp_lname); 8 EXCEPTION 9 WHEN OTHERS 10 THEN 11 DBMS_OUTPUT.PUT_LINE(sqlerrm); 12 END; 13 / Procedure created. SQL> SQL> SQL> ALTER SESSION SET SQL_TRACE = TRUE; Session altered. SQL> EXEC bind_test('Ron') ORA-01403: no data found PL/SQL procedure successfully completed. SQL> EXEC bind_test('Mike') ORA-01403: no data found PL/SQL procedure successfully completed. SQL> ALTER SESSION SET SQL_TRACE = FALSE; Session altered. SQL> SQL> drop table emp; Table dropped. SQL> Related examples in the same category1.ORA-00918: column ambiguously defined2.ORA-00934: group function is not allowed here3.ORA-00979: not a GROUP BY expression4.ORA-01403: no data found5.ORA-01422: exact fetch returns more than requested number of rows6.ORA-01426: numeric overflow7.ORA-01839: date not valid for month specified8.ORA-06502: PL/SQL: numeric or value error9.ORA-06502: PL/SQL: numeric or value error: character to number conversion error10.ORA-06502: PL/SQL: numeric or value error: number precision too large11.ORA-06503: PL/SQL: Function returned without value12.ORA-14551: cannot perform a DML operation inside a queryjava2s.com |Email:info at java2s.com|© Demo Source and Support. All rights reserved.