CODEWITHSUNDEEP
sqloracle-databaseprocedure
miapaulina
miapaulina

Reputation: 25

create procedure sql success with compilation error

I am beginner in sql and learning procedures, functions and anonymous blocks. I have checked my code and see no spelling errors to cause a compilation error. Can you kindly help me understand what I am doing wrong here? This is the question 4. Convert the file show_class_offerings.sql to a procedure. Accept a start date and end date. For each class found, display the CLASS_ID, START_DATE, instructor FIRST_NAME and LAST_NAME, course TITLE and SECTION_CODE, and average grade. Find the average grade by a call to the function compute_average_grade.

CREATE OR REPLACE PROCEDURE show_class_offerings
 (p_start_date IN DATE,
  p_end_date IN DATE)
   IS
  v_avg_grade NUMBER;
   IS
  CURSOR classes_info_cur IS
   SELECT cl.class_id, cl.start_date, i.first_name,
     i.last_name, co.title, co.section_code
  FROM classes cl, courses co, instructors i
  WHERE start_date BETWEEN p_start_date AND p_end_date
    AND cl.course_id = co.course_id
    AND cl.instr_id = i.instructor_id;
  BEGIN
  DBMS_OUTPUT.PUT_LINE ('Date range: Between ' ||
  p_start_date || ' and ' || p_end_date || '.');
  DBMS_OUTPUT.PUT_LINE('Classes Information.');
   FOR classes_info_rec IN classes_info_cur 
  LOOP
   v_avg_grade := compute_average_grade(classes_info_rec.class_id);
  DBMS_OUTPUT.PUT_LINE(
  'Class ID' || classes_info_rec.class_id ||
  '-Average Grade' || v_avg_grade ||
  '-Start Date' || classes_info_rec.start_date ||
  '-Instructor' || classes_info_rec.first_name ||
   classes_info_rec.last_name ||
  '-Course Title' || classes_info_rec.title ||
  '-Offering Section' || classes_info_rec.section_code);
   END LOOP;
    END show_class_offerings;

error: ORA-24344: success with compilation error

Upvotes: 0

Views: 73

Answers (1)

MT0
MT0

Reputation: 167774

Remove the second IS:

CREATE OR REPLACE PROCEDURE show_class_offerings(
  p_start_date IN DATE,
  p_end_date   IN DATE
)
IS
  v_avg_grade NUMBER;

  CURSOR classes_info_cur IS
    SELECT cl.class_id,
           cl.start_date,
           i.first_name,
           i.last_name,
           co.title,
           co.section_code
    FROM   classes cl
           INNER JOIN courses co
           ON (cl.course_id = co.course_id)
           INNER JOIN instructors i
           ON (cl.instr_id = i.instructor_id)
    WHERE  start_date BETWEEN p_start_date AND p_end_date;
BEGIN
  DBMS_OUTPUT.PUT_LINE (
    'Date range: Between ' || p_start_date || ' and ' || p_end_date || '.'
  );
  DBMS_OUTPUT.PUT_LINE('Classes Information.');
  FOR classes_info_rec IN classes_info_cur 
  LOOP
    v_avg_grade := compute_average_grade(classes_info_rec.class_id);
    DBMS_OUTPUT.PUT_LINE(
      'Class ID' || classes_info_rec.class_id ||
      '-Average Grade' || v_avg_grade ||
      '-Start Date' || classes_info_rec.start_date ||
      '-Instructor' || classes_info_rec.first_name || classes_info_rec.last_name ||
      '-Course Title' || classes_info_rec.title ||
      '-Offering Section' || classes_info_rec.section_code
    );
  END LOOP;
END show_class_offerings;
/

(Note: You can also use ANSI joins instead of, the less readable, legacy comma joins.)

db<>fiddle here

Upvotes: 1

Related Questions