[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do i avoid an Oracle ORA-01403: no data found error with this procedure

Posted on 2009-02-11
5
Medium Priority
?
1,736 Views
Last Modified: 2012-06-27
I have recently re-written a script that i originall done in Sql Server to port it over to Oracle.
The purpose of the script is basically to read through the colleges E-register table and then transform the Students Marks down so we get one row per mark per register as opposed to one line per studentwhich then relies on a week pattern to pull the mark for the right date / time etc.
I have included the code for this transform.
t keeps throwing a ORA-01403: no data found error which is confusing me slighltly as i have nvl'd all the relevant columns to ensure they never return null.
Many Thanks in advance
THis code is for the script:-
 
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  v_cursor SYS_REFCURSOR;
  I NUMBER;
  RegisterMarkCount NUMBER;
  FirstSesStart DATE;
  NextSesWk DATE;
  PreSRRegisterID VARCHAR(20);
  MarkValue NUMBER;
  Section varchar(20);
  m_reference varchar(20);
  m_id varchar(20);
  m_name varchar(20);
  m_Start date;
  m_end date;
  sr_student NUMBER;
  r_id  NUMBER; 
   a_id  NUMBER;
  sr_register  NUMBER;
  r_reference  VARCHAR(20);
  a_weekpattern VARCHAR(250);
   sr_weekpattern VARCHAR(250);
  DayOfWeek VARCHAR(12);
  a_time VARCHAR(12);
  start_date date;
  end_date date;
 
BEGIN
 
  sa_cursor (p_cursor => v_cursor);
            
  LOOP 
    FETCH v_cursor
    INTO  sr_student, r_id, a_id, sr_register, r_reference, a_weekpattern, sr_weekpattern, dayofweek, a_time, start_date, end_date;
    FirstSesStart := start_date;
    NextSesWk := FirstSesStart;
    I := 1;
    WHILE I < (length(a_Weekpattern))
   LOOP
      MarkValue := 0;
     SELECT vc_context INTO MarkValue FROM caps_valid_codes WHERE vc_domain = 'studentregistermark' and vc_code = Substr(SR_WEEKPATTERN, I, 1);
    Insert Into MIS_StudentMarks
      VALUES
      (SR_STUDENT, R_ID, R_REFERENCE, A_ID, NextSesWk, a_time, DAYOFWEEK, 
			nvl(Substr(sr_weekpattern, I, 1), ' '), I,
			Case When Substr(A_WEEKPATTERN, I, 1)= '1' Then 1 Else 0 End, 
			Case When  MarkValue = 1 Then 1 Else 0 End,
			Case When Substr(A_WEEKPATTERN, I, 1) = '1' and  nvl(Substr(SR_WEEKPATTERN, I, 1), ' ') = ' ' Then 1 Else 0 End, 
			nvl(SR_WEEKPATTERN, ' '),
			nvl(A_WEEKPATTERN, ' '));
     
      COMMIT;
		I := I + 1;
 NextSesWk := MIS_DateAdd('dd', (I - 1) * 7, FirstSesStart);
If NextSesWk > to_date(sysdate, 'DD MON YYYY') then
  	I := Length(A_WEEKPATTERN)  + 1;
	End if;
    END LOOP;
    EXIT WHEN v_cursor%NOTFOUND;
  END LOOP;
  CLOSE v_cursor;
END;
 
And here is the code for the cursor:-
 
create or replace procedure sa_cursor(p_cursor OUT SYS_REFCURSOR)
is
begin
  open p_cursor FOR 
Select nvl(SR.SR_STUDENT, 100000000000000) as SR_STUDENT, nvl(R.R_ID, 100000000000000) as R_ID, nvl(A.A_ID, 100000000000000) as A_ID, nvl(SR.SR_REGISTER, 100000000000000) as SR_REGISTER, nvl(R.R_REFERENCE, 'Unknown') as R_REFERENCE,  nvl(A.A_WEEKPATTERN, ' ') as A_WEEKPATTERN, nvl(SR.SR_WEEKPATTERN, ' ') as SR_WEEKPATTERN, nvl(TO_CHAR(A.A_START, 'DAY'), 'Unkknown')  as  DAY_OF_WEEK, nvl(TO_CHAR(A.A_START, 'HH24:MI'), '0:00') as A_TIME, TO_DATE(A.A_START, 'DD MON YYYY') START_DATE, TO_DATE(A.A_END,'DD MON YYYY') END_DATE
From
CAPD_ACTIVITY A inner join CAPD_STUDENTREGISTER SR
ON
A.A_ID = SR.SR_ACTIVITY inner join CAPD_REGISTER R
ON
R.R_ID = SR.SR_REGISTER
WHERE
A.A_START > '01 AUG 2008'
AND
A.A_START < SYSDATE;
end sa_cursor;

Open in new window

0
Comment
Question by:MISServices
  • 3
  • 2
5 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 23609193
One of your select statement is returning NO rows, that is the reason for this exception, mostly this one.
>> SELECT vc_context INTO MarkValue FROM caps_valid_codes WHERE vc_domain = 'studentregistermark' and vc_code = Substr(SR_WEEKPATTERN, I, 1);

You need to have an exception handler or you need to change the program logic to handle this.

you can use dbms_output.put_line statements in between to display debug information to see the values of variables during the exception.
0
 

Author Comment

by:MISServices
ID: 23609363
I had previously tried adding exception handling to that line and oracle didn't like it, this is the code i used, i'll just take an excerpt of the procedure:-


  MarkValue := 0;
     SELECT vc_context INTO MarkValue FROM caps_valid_codes WHERE vc_domain = 'studentregistermark' and vc_code = Substr(SR_WEEKPATTERN, I, 1);
Exception
When no_data Then
MarkValue := 0




0
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 23609408
The below two lines in your code should be replaced with the block shown
>>  MarkValue := 0;
>>  SELECT vc_context INTO MarkValue FROM caps_valid_codes WHERE vc_domain = 'studentregistermark' and vc_code = Substr(SR_WEEKPATTERN, I, 1);

   
   
    BEGIN
      MarkValue := 0;
     SELECT vc_context INTO MarkValue FROM caps_valid_codes WHERE vc_domain = 'studentregistermark' and vc_code = Substr(SR_WEEKPATTERN, I, 1);
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
      MarkValue := 0;
    END;
0
 

Author Closing Comment

by:MISServices
ID: 31545472
Thank you, you have saved me many a sleepless night :-)
0
 
LVL 27

Expert Comment

by:sujith80
ID: 23609695
welcome. glad to help :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question