Oracle Error Handling No Data Found
Contents |
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 it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not ora-01403 no data found in oracle post a blank message. Please type your message and try again. More discussions ora-01403 no data found ora-06512 in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 1 2 Previous Next 24 pl sql no data found continue 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 ora-01403 no data found select into Handling in PL/SQL. More often than not the issue comes from the 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
Ora 01403 No Data Found Ora 06512 In Oracle
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 |+---------------------------+| Exception Section |+---------------------------+ The Declaration section is the part defined between the PROCEDURE/FUNCTION header or the DECLARE keyword (for anonymous blocks) and the BEGIN keyword. (Optional section)The Statements section is where your code goes and lies between the BEGIN keyword and the EXCEPTION keyword (or END keyword if there is no EXCEPTION section). (Mandatory section)The Exception section is where any exception handling goes and lies between the EXCEPTION keyword at the END keyword. (Optional section)Example of an anonymous block... DECLARE .. declarative statements go here ..BEGIN .. code statements go here ..EXCEPTION .. exception handlers go here ..END; Example of a procedure/function block... [CREATE OR REPLACE] (PROCEDURE|FUNCTI
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
Select Into No Data Found Exception Handling
Don Burleson Blog
there was no data from the objects which may be due to end of fetch. ORA-01403: no data found tips Oracle Error Tips by Burleson Consulting Oracle docs ora 01403 no data found in forms note this about ORA-01403: ORA-01403 no data found Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating https://community.oracle.com/thread/699262 that all records have been returned from the SQL query. Action: Terminate processing for the SELECT statement Many 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 http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm 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 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 (FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeNO data found : Handle Exception«PL SQL Programming«Oracle PL/SQL http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/NOdatafound.htm TutorialOracle PL/SQL TutorialPL SQL ProgrammingHandle ExceptionSQL> SQL> SQL> create table employee ( 2 id number, 3 employee_type_id number, 4 external_id varchar2(30), 5 first_name varchar2(30), 6 middle_name varchar2(30), 7 last_name varchar2(30), 8 name varchar2(100), 9 birth_date date , 10 gender_id number ); Table created. SQL> SQL> SQL> create table gender ( 2 id no data number, 3 code varchar2(30), 4 description varchar2(80), 5 active_date date default SYSDATE not null, 6 inactive_date date ); Table created. SQL> SQL> SQL> SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' ); 1 row created. SQL> insert into gender ( id, code, description ) values ( 2, no data found 'M', 'Male' ); 1 row created. SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' ); 1 row created. SQL> SQL> SQL> SQL> set serveroutput on size 1000000; SQL> SQL> declare 2 3 d_birth_date employee.birth_date%TYPE; 4 n_gender_id employee.gender_id%TYPE; 5 n_selected number := -1; 6 n_id employee.id%TYPE; 7 v_first_name employee.first_name%TYPE; 8 v_last_name employee.last_name%TYPE; 9 v_middle_name employee.middle_name%TYPE; 10 v_name employee.name%TYPE; 11 12 begin 13 v_first_name := 'JOHN'; 14 v_middle_name := 'J.'; 15 v_last_name := 'DOUGH'; 16 v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name); 17 d_birth_date := to_date('19800101', 'YYYYMMDD'); 18 19 begin 20 select id into n_gender_id from gender where code = 'M'; 21 exception 22 when OTHERS then 23 raise_application_error(-20001, SQLERRM||' on select gender'); 24 end; 25 26 begin 27 select id 28 into n_id 29 from employee 30 where name = v_name 31 and birth_date = d_birth_date 32 and gender_id = n_gender_id; 33 34 n_selected := sql%rowcount; 35 exception 36 when NO_DATA_FOUND then