Execute Immediate No Data Found Error
Contents |
your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before
No Data Found Exception In Oracle
you can post: click the register link above to proceed. To oracle no data found exception example start viewing messages, select the forum that you want to visit from the selection below. Results 1
Pl Sql No Data Found Continue
to 10 of 10 Thread: Execute immediate throwing no data found exception Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search ora-01403 no data found in oracle Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-04-09,16:49 #1 qts1 View Profile View Forum Posts Registered User Join Date Feb 2009 Posts 25 Unanswered: Execute immediate throwing no data found exception I am using execute immediate in my stored procedure (Oracle 10G) as below EXECUTE IMMEDIATE 'SELECT BUCKET_COLUMN FROM TABLENAME ' || ora-01403 no data found ora-06512 'WHERE ID = '||var_ID||' AND CODE_ID = '||var_CODE_ID|| ' AND source_column = ''CODE''' INTO var_COLUMN_NAME; The query sometimes returns null whihc is expected however I get an exception as below how do I handle this so a null value is accepted ORA-1403: No Data Found Reply With Quote 03-04-09,17:49 #2 shammat View Profile View Forum Posts Registered User Join Date Nov 2003 Posts 2,918 Provided Answers: 8 Originally Posted by qts1 however I get an exception as below how do I handle this so a null value is accepted By catching the exception Reply With Quote 03-05-09,18:02 #3 qts1 View Profile View Forum Posts Registered User Join Date Feb 2009 Posts 25 Ok thats was pretty simple thanks! Reply With Quote 03-06-09,02:52 #4 shammat View Profile View Forum Posts Registered User Join Date Nov 2003 Posts 2,918 Provided Answers: 8 It's amazing what wealth of information the manual contains. Isn't it? Reply With Quote 04-08-09,22:21 #5 qts1 View Profile View Forum Posts Registered User Join
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
How To Handle No Data Found Exception In Cursor For Loop
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs ora-01403 no data found in package Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just
No_data_found
like you, helping each other. Join them; it only takes a minute: Sign up PL/SQL block problem: No data found up vote 24 down vote favorite 6 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER http://www.dbforums.com/showthread.php?1639149-Execute-immediate-throwing-no-data-found-exception := 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 v_letter_grade := 'D'; ELSE http://stackoverflow.com/questions/1256112/pl-sql-block-problem-no-data-found 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 136227 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 49 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 wrap the SELECT statement with it's own BEGIN/EXCEPTION/END block. Exa
data found If this is your first visit, be sure to check out the FAQ by clicking the http://www.dbasupport.com/forums/showthread.php?32489-ORA-01403-no-data-found link above. You may have to register before you can http://www.orafaq.com/forum/t/183600/ post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 5 of 5 Thread: ORA-01403: no data found Tweet Thread Tools Show Printable Version Email no data this Page… Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 12-18-2002,10:34 PM #1 Nikee View Profile View Forum Posts Member Join Date Jul 2001 Posts 108 ORA-01403: no data found Hello, When I tried to execute the following PL/SQL code, I am facing the ORA-01403: no no data found data found error: ------------------------------------------------------------ DECLARE Tname varchar2(30) := 'CUSTOM'; Crows Integer(10); Long varchar2(106); BEGIN Execute Immediate 'select data_type from DBA_TAB_COLUMNS where data_type=''LONG RAW'''||' and table_name='''||Tname||'''' into Long; If Long = 'LONG RAW' Then dbms_output.put_line( Tname ); End If; END; / ------------------------------------------------------------ Error Message: ERROR at line 1: ORA-01403: no data found ------------------------------------------------------------ The error is occuring when the Execute Immediate statement returns an empty value. The happens if the table do not contain LONG RAW data_type. I do not know how to handle this? ThanK You, Seenu Last edited by Nikee; 12-18-2002 at 10:39 PM. Reply With Quote 12-19-2002,12:28 AM #2 stecal View Profile View Forum Posts Super Moderator Join Date May 2002 Posts 2,645 You may want to check your query by using SQL*PLus to confirm there really is no data found (0 rows returned). Why? Because of your use of quotation marks in forming a string value. Also, your query can be coded without the
the exception and continue the process [message #564466] Fri, 24 August 2012 02:54 dhivyaenjoy Messages: 49Registered: June 2011 Member Hi Team, I am trying to execute the below package. While executing i face a problem where when NO DATA FOUND the excpetion is handled and coming out of the loop. but i want to to continue the loop after hadnling the exception. Is there anyway i can modify the code CREATE OR replace PACKAGE BODY pkg_purge_archive_check AS PROCEDURE Purge_archive_tables_check (purgerows IN NUMBER) IS v_num_1 NUMBER(10); v_num_2 NUMBER(10); v_multiplier NUMBER(10); num_total_rows NUMBER(10); num_total_rows_1 NUMBER(10); v_num_rows VARCHAR2(10); v_process VARCHAR2(50); n DATE; v_err_code NUMBER; v_err_msg VARCHAR2(200); BEGIN v_num_1 := purgerows; v_multiplier := 1; SELECT Nvl((SELECT 'no' FROM archive_data WHERE category = 'ARCHIVE PKG PURGE' AND Trunc(start_time) = Trunc(SYSDATE) AND ROWNUM = 1), 'yes') INTO v_num_rows FROM dual; IF v_num_rows = 'yes' THEN v_process := 'ARCHIVE_PROCESS_1'; ELSE v_process := 'ARCHIVE_PROCESS_2'; END IF; FOR c1 IN (SELECT owner ||'.' ||table_name table_name FROM all_tables WHERE owner = 'ARCHIVE' AND num_rows > 0 ORDER BY table_name) LOOP dbms_output.Put_line(c1.table_name); EXECUTE IMMEDIATE 'select ARCH_TS from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum=1' INTO n; IF SQL%FOUND THEN IF c1.table_name = 'ARCHIVE.SHIPMENT_STATUS_DMP' THEN v_multiplier := 5; ELSE v_multiplier := 1; END IF; v_num_2 := v_num_1 * v_multiplier; INSERT INTO archive_data (id, category, insert_user, start_time) VALUES (c1.table_name, 'ARCHIVE PKG PURGE', v_process, SYSDATE); EXECUTE IMMEDIATE 'delete from '||c1.table_name||' where arch_ts < (sysdate-650) and rownum <='||v_num_2; num_total_rows := SQL%rowcount; UPDATE archive_data SET end_time = SYSDATE, value1 = num_total_rows WHERE id = c1.table_name AND insert_user = v_process AND category = 'ARCHIVE PKG PURGE'; END IF; COMMIT; END LOOP; EXCEPTION WHEN no_data_found THEN dbms_output.Put_line('NO DATA FOUND MANUAL'); WHEN OTHERS THEN v_err_code := SQLCODE; v_err_msg := Substr(SQLERRM, 1, 200); dbms_output.Put_line('Error code: ' ||v_err_code); dbms_output.Put_line('Error message: ' ||v_err_msg); END; END pkg_purge_archive_check; Please advise Report message to a moderator Re: To handle the exception and continue the process [message #564468 is a reply to message #564466] Fri, 24 August 2012 03:18 Michel Cadot Messages: 63882R