Handle No Data Found Error
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 post a blank message. Please type your message and try
No Data Found Exception In Oracle
again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion ora-01403 no data found in oracle is archived 5 Replies Latest reply on Feb 25, 2010 8:15 AM by 693765 No Data Found in SELECT INTO 693765 Feb 25,
Ora-01403 No Data Found Ora-06512
2010 8:03 AM Hi all, I try to select into a variable in a cursor loop but I get NO DATA FOUND when the select doesn't return a value. I want to set the variable to be 0 oracle no data found exception example when the select fails. I tried to add an exception but I get the following: ORA-00900: invalid SQL statement Here is the code I'm trying to execute (The exception is commented): Line: ----- PROCEDURE MyProc IS v_op NUMBER; BEGIN FOR cursor1 IN (select * from myTable) LOOP SELECT operation INTO v_op FROM operationsTable where id=cursor1.ID; --EXCEPTION* -- WHEN NO_DATA_FOUND THEN -- v_op :=0; --END;* END LOOP; END MyProc ; Line: ----- Does anybody know how to set pl sql exception handling a variable when the select fails with NO DATA FOUND? Thanks dyahav 77260Views Tags: none (add) This content has been marked as final. Show 5 replies 1. Re: No Data Found in SELECT INTO Tubby Feb 25, 2010 8:05 AM (in response to 693765) You need to contain your exception with BEGIN and END BEGIN select into... EXCEPTION WHEN NO_DATA_FOUND do_something END; Like Show 0 Likes(0) Actions 2. Re: No Data Found in SELECT INTO 693765 Feb 25, 2010 8:08 AM (in response to Tubby) Thanks a lot for the quick response! Like Show 0 Likes(0) Actions 3. Re: No Data Found in SELECT INTO Tubby Feb 25, 2010 8:10 AM (in response to 693765) Also, this would be much better code than the slow by slow approach you are undertaking now. PROCEDURE MyProc IS v_op NUMBER; BEGIN FOR cursor1 IN ( select nvl(o.operation, 0) from myTable t, operationsTable o where t.id = o.id (+) ) LOOP --presumably some processing here? END LOOP; END MyProc ;Join the tables, databases are good at doing this, which you're basically trying to do yourself .... and you'll only make the performance bad by doing that. Since you aren't handling (or trying to handle) the TOO_MANY_ROWS exception i am assuming there is a 1 - 1 (optional) relationship between your tables. Like Show 0 Likes(0) Ac
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
Ora 01403 No Data Found Ora 06512 In Oracle
company Business Learn more about hiring developers or posting ads with us Stack Overflow ora-01403 no data found select into Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7
Select Into No Data Found Exception Handling
million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL/SQL block problem: No data found up vote 25 down vote favorite 6 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := https://community.oracle.com/thread/1036850?start=0&tstart=0 &sv_student_id; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; CASE -- outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTPUT.PUT_LINE ('There is no final grade.'); ELSE CASE -- inner CASE WHEN v_final_grade >= 90 THEN v_letter_grade := 'A'; WHEN v_final_grade >= 80 THEN v_letter_grade := 'B'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 60 THEN http://stackoverflow.com/questions/1256112/pl-sql-block-problem-no-data-found v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END CASE; -- control resumes here after inner CASE terminates DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade); END CASE; -- control resumes here after outer CASE terminates END; the above code i have taken from the book oracle pl-sql by example fourth edition 2009 my problem is when i enter a student id not present in the table it returns me the following errors Error report: ORA-01403: no data found ORA-06512: at line 7 01403. 00000 - "no data found" *Cause: *Action: but according to book it should have returned a null value and then follow the case flow.. please help me out... thanks in advance sql oracle plsql oracle10g ora-01403 share|improve this question edited Feb 12 '11 at 1:33 OMG Ponies 199k37356416 asked Aug 10 '09 at 17:21 Orapps 141237 Any chance of formatting your PL SQL as code? –pjp Aug 10 '09 at 17:22 Looks nice and pretty now :) –pjp Aug 10 '09 at 17:27 add a comment| 4 Answers 4 active oldest votes up vote 50 down vote accepted When you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to w
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
http://www.dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm Oracle PL/SQL "select into" clause Oracle https://www.techonthenet.com/oracle/questions/no_data.php Database Tips by Burleson Consulting The SELECT INTO Clause The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables. no data 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_key = 'A103'; 10 11 dbms_output.put_line('Name: '||v_authName); 12 end; 13 / Name: weaton Here the author_key was used to retrieve one author's last name and place it in the variable called v_authName. The query can also retrieve an no data found entire row as a record with SELECT INTO. In the example below a record based on the columns of the author table is declared in line two below. Because v_author is declared as an author table %rowtype , you can safely use the SELECT * clause to retrieve all the columns. SQL> declare 2 v_author author%rowtype; 3 begin 4 select 5 * into v_author 6 from 7 author 8 where 9 author_key = 'A103'; 10 11 dbms_output.put_line('Name: '||v_author.author_first_name||' '|| v_author.author_last_name); 12 end; 13 / Name: erin weaton If the DBA adds a column to the author table, the query above will still execute. The record variable v_author contains a record that includes all the columns in the author table. If the value of a column in the table is NULL, it will also be NULL in the record. The individual columns are accessed using the dot "." notation with SELECT INTO. You can see this in line 11 of the listing. Although it is important to define variables using the database datatype definition of the retrieved data, sometime this is not possible. This is showMariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX 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 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: Avoid data not found error in PLSQL code Question: I'm a new programmer and am trying to include the following statement in my PLSQL code: SELECT msa_code, mda_desc, zip_code_nk FROM sales.msa WHERE zip_code_nk = prod_rec.zip_code_nk; When there is not a zip_code_nk in the msa table, I'm getting an oracle error saying "Data not found". How can I code around this? It seem the processor just drops to the exception code and records the record as a failed insert. Answer: To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned. For example: -- Check to make sure that at least one record is returned SELECT COUNT(1) INTO v_count FROM sales.msa WHERE zip_code_nk = prod_rec.zip_code_nk; IF v_count > 0 THEN SELECT msa_code, mda_desc, zip_code_nk FROM sales.msa WHERE zip_code_nk = prod_rec.zip_code_nk; END IF; If the COUNT(1) function returns at least one record, then you can perform the "original select statement". 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 develop