CREATE OR REPLACE PROCEDURE prepareNextSemester IS cur_student INT; cur_study INT; cur_subject INT; cur_subject_desc VARCHAR(50); first_student INT; TYPE student_table IS TABLE OF studenten%ROWTYPE; all_students student_table; TYPE subject_table IS TABLE OF unterricht%ROWTYPE; all_subjects subject_table; BEGIN --DBMS_OUTPUT.PUT_LINE('Well well, first index is ' || all_students.first); SELECT * BULK COLLECT INTO all_students FROM studenten; -- FOR i IN all_students.first .. all_students.last LOOP -- DBMS_OUTPUT.PUT_LINE('Current person... ' || all_students(i).personen_id); -- END LOOP; -- selecting all students into all_students collection FOR i IN all_students.first .. all_students.last LOOP BEGIN SELECT u.* BULK COLLECT INTO all_subjects FROM studien s INNER JOIN faecher f ON f.studium_id = s.STUDIUM_ID INNER JOIN unterricht u ON u.fach_id = f.FACH_ID WHERE s.STUDIUM_ID = all_students(i).studium_id; FOR x IN all_subjects.first .. all_subjects.last LOOP BEGIN SELECT kbz INTO cur_subject_desc FROM unterricht u INNER JOIN faecher f ON f.fach_id = u.fach_id WHERE all_subjects(x).unterricht_id = u.unterricht_id; -- DBMS_OUTPUT.PUT_LINE('Current subject ' || cur_subject_desc); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No subject with found!'); END; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('No data found'); END; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Damned, come on, wrong SQL'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('well i guess others'); END;