0

Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query just with the aim of practicing 'Cursor's . I have three tables with below structures :

Student:(Student_id , Student_name)
Course :(COURSE_NO , description)
Student_Course:(Student_id , Course_id , Nomreh)

Table Student_Course stores each student's mark in each course . the word 'Nomreh' is the Persian of Mark. I just want to know what wrong with my query is? Everything seems flawless! But I receive these errors :

ORA-01403:no data found
ORA-06512:at line 13

Here is my query :

DECLARE
  CURSOR C1 IS
        SELECT S.STUDENT_ID FROM STUDENT S;

        C1_REC               C1%ROWTYPE;
        V_STUDENT_FIRST_NAME STUDENT.STUDENT_NAME%TYPE;
        V_COURSE_DESCRIPTION COURSE.DESCRIPTION%TYPE;
        V_NOMRE              STUDENT_COURSE.NOMREH%TYPE;

  BEGIN
     FOR C1_REC IN C1 LOOP

        SELECT S.STUDENT_NAME, C.DESCRIPTION, SC.NOMREH
        INTO V_STUDENT_FIRST_NAME, V_COURSE_DESCRIPTION, V_NOMRE
        FROM STUDENT S
             INNER JOIN STUDENT_COURSE SC
        ON S.STUDENT_ID = SC.STUDENT_ID
             INNER JOIN COURSE C
        ON SC.COURSE_ID = C.COURSE_NO
        WHERE S.STUDENT_ID = C1_REC.STUDENT_ID;

  INSERT INTO TARGETTABLE
  (STUDENT_NAME, COURSE_NAME, NOMRE)
      VALUES
  (V_STUDENT_FIRST_NAME, V_COURSE_DESCRIPTION, V_NOMRE);
 END LOOP;
   COMMIT;
 END;

Thanks in advance

  • 3
    There is just no way this is flawless. You do not have COURSE_ID column anywhere, yet you use SC.COURSE_ID in the query. This code could not even run without other errors. Try again. – Balazs Papp Jun 30 at 13:07
  • 1
    Thanks . But 'Course_id' is 'Course_No' in 'Course' Table and the sql statement inside the loop is perfectly fine . And I have column 'Course_id ' in student_course table . I edited my question. Now what is wrong with my query? – Pantea Tourang Jun 30 at 13:16
  • 2
    You might be encountering a student that's not registered to any courses, which, because of the inner join, would fail to return a record inside the loop. – mustaccio Jun 30 at 14:15
2

The SQL statement inside the loop may be fine, but it still does not return data.

SELECT ... INTO ... throws an error ORA-01403 when "no data found":

declare
  l_num number;
begin
  select 1 into l_num from dual where 1 = 2;
end;
/

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

That code you wrote fails no only when the query does not return rows, it would also fail if the query returned multiple rows (a student attending multiple courses):

declare
  l_num number;
begin
  select 1 into l_num from dual connect by level <= 2;
end;
/

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

Here is something that should work (just for the sake of this example):

DECLARE
  CURSOR C1 IS
        SELECT S.STUDENT_ID FROM STUDENT S;
        C1_REC               C1%ROWTYPE;
BEGIN
    FOR C1_REC IN C1 LOOP

       INSERT INTO TARGETTABLE
       (STUDENT_NAME, COURSE_NAME, NOMRE)
       SELECT S.STUDENT_NAME, C.DESCRIPTION, SC.NOMREH
       FROM STUDENT S
            INNER JOIN STUDENT_COURSE SC
       ON S.STUDENT_ID = SC.STUDENT_ID
            INNER JOIN COURSE C
       ON SC.COURSE_ID = C.COURSE_NO
       WHERE S.STUDENT_ID = C1_REC.STUDENT_ID;
END LOOP;
  COMMIT;
END; 
/
  • 1
    you're completely right. I checked table records and I found that I have students that has not participated in any course and vice-versa . I correct the records in a way that my query works fine and everything worked fine then . Thanks – Pantea Tourang Jul 1 at 4:38
  • 1
    Just wanted to mention something. You can handle this no_data_found on exception part . I've just added this part to my code EXCEPTION WHEN No_data_found THEN INSERT INTO TARGETTABLE (STUDENT_NAME, COURSE_NAME, NOMRE) VALUES (NULL, NULL, NULL); COMMIT; – Pantea Tourang Jul 1 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.