plsql learning - one:debug and track

All these codes test on oracle9.2.First,give the all the base tables and datas for learning pl/sql.
They will be all used in the following testing.

create sequence student_sequence
start with 10000
increment by 1;

create sequence room_sequence
start with 20000
increment by 1;

create table students(
  id number(5) primary key,
  first_name varchar2(20),
  last_name varchar2(20),
  major varchar2(30),
  current_credits number(3)
);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Scott','Smith','Computer Science',11);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Margaret','Mason','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Joanne','Junebug','Computer Science',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Manish','Murgratroid','Economics',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Patrick','Poll','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Timothy','Taller','History',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Barbara','Blues','Economics',7);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'David','Dinsmore','Music',4);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Ester','Elegant','Nutrition',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Rose','Riznit','Music',7);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Rita','Razmataz','Nutrition',8);

insert into students(id,first_name,last_name,major,current_credits)
values(student_sequence.nextval,'Shay','Shariatpanahy','Computer Science',3);

create table major_stats(
  major varchar2(30),
  total_credits number,
  total_students number
)

insert into major_stats(major,total_credits,total_students)
values('Computer Science',22,3);

insert into major_stats(major,total_credits,total_students)
values('History',12,3);

insert into major_stats(major,total_credits,total_students)
values('Economics',15,2);

insert into major_stats(major,total_credits,total_students)
values('Music',11,2);

insert into major_stats(major,total_credits,total_students)
values('Nutrition',16,2);

create table rooms(
  room_id number(5) primary key,
  building varchar2(15),
  room_number number(4),
  number_seats number(4),
  description varchar2(50)
);

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',201,1000,'Large Lecture Hall');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',101,500,'Small Lecture Hall');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',150,50,'Discussion Room A');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',160,50,'Discussion Room B');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 6',170,50,'Discussion Room C');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Music Building',100,10,'Music Practise Room');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Music Building',200,1000,'Concert Room');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',300,75,'Discussion Room D');

insert into rooms(room_id,building,room_number,number_seats,description)
values(room_sequence.nextval,'Building 7',310,50,'Discussion Room E');

create table classes(
  department char(3),
  course number(3),
  description varchar2(2000),
  max_students number(3),
  current_students number(3),
  num_credits number(1),
  room_id number(5),
  constraint classes_department_course primary key (department,course),
  constraint classes_room_id foreign key (room_id) references rooms(room_id)
);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('HIS',101,'History 101',30,11,4,20000);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('HIS',301,'History 301',30,0,4,20004);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('CS',101,'Computer Science 101',50,0,4,20001);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('ESN',203,'Economics 203',15,0,3,20002);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('CS',102,'Computer Science 102',35,3,4,20003);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('MUS',410,'Music 410',5,4,3,20005);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('ECN',101,'Economics 101',50,0,4,20007);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('NUT',307,'Nutrition 307',20,2,4,20008);

insert into classes(department,course,description,max_students,current_students,num_credits,room_id)
values('NUS',100,'Music 100',100,0,3,null);

create table registered_students(
  student_id number(5) not null,
  department char(3) not null,
  course number(3) not null,
  grade char(1),
  constraint rs_grade check(grade in('A','B','C','D','E')),
  constraint rs_student_di foreign key (student_id) references students(id),
  constraint rs_department_course foreign key(department,course) references classes(department,course)
);

insert into registered_students(student_id,department,course,grade)
values(10000,'CS',102,'A');

insert into registered_students(student_id,department,course,grade)
values(10002,'CS',102,'B');

insert into registered_students(student_id,department,course,grade)
values(10003,'CS',102,'C');

insert into registered_students(student_id,department,course,grade)
values(10000,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10001,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10002,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10003,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10004,'HIS',101,'C');

insert into registered_students(student_id,department,course,grade)
values(10005,'HIS',101,'C');

insert into registered_students(student_id,department,course,grade)
values(10006,'HIS',101,'E');

insert into registered_students(student_id,department,course,grade)
values(10007,'HIS',101,'B');

insert into registered_students(student_id,department,course,grade)
values(10008,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10009,'HIS',101,'D');

insert into registered_students(student_id,department,course,grade)
values(10010,'HIS',101,'A');

insert into registered_students(student_id,department,course,grade)
values(10008,'NUT',307,'A');

insert into registered_students(student_id,department,course,grade)
values(10010,'NUT',307,'A');

insert into registered_students(student_id,department,course,grade)
values(10009,'MUS',410,'B');

insert into registered_students(student_id,department,course,grade)
values(10006,'MUS',410,'E');

insert into registered_students(student_id,department,course,grade)
values(10011,'MUS',410,'B');

insert into registered_students(student_id,department,course,grade)
values(10010,'MUS',410,'B');

create table rs_audit(
  change_type char(1) not null,
  changed_by varchar2(8) not null,
  timestamp date not null,
  old_student_id number(5),
  old_department char(3),
  old_course number(3),
  old_grade char(1),
  new_student_id number(5),
  new_department char(3),
  new_course number(3),
  new_grade char(1)
);

create table log_table(
  code number,
  message varchar2(200),
  info varchar2(100)
);

create table temp_table(
  num_col number,
  char_col varchar2(60)
);

create table connect_audit(
  user_name varchar2(30),
  operation varchar2(30),
  timestamp date
);

create table debug_table(
  linecount number,
  debug_str varchar2(100)
);

create table source(
  key number(5),
  value varchar2(50)
);

create table destination(
 key number(5),
 value number
);

 Then to create the function and procdure to show how to debug when the error raised up.

create or replace function AverageGrade(
  p_Department in registered_students.department%TYPE,
  p_Course in registered_students.course%TYPE) return char as
  v_AverageGrade char(1);
  v_NumericGrade number;
  v_NumberStudents number;
  
  --cursor c_Grades is
    --select grade from registered_students
    --where department = p_Department and course = p_Course;
  begin
    Debug.Reset;
    Debug.Debug('p_Department',''''||p_Department||'''');
    Debug.Debug('p_Course',''''||p_Course||'''');
    --Check the number of student that queried.
    select count(*) into v_NumberStudents
    from registered_students
    where department = p_Department and course = p_Course;
    Debug.Debug('After select,the number is: ',v_NumberStudents);
    
    if v_NumberStudents = 0 then
      raise_application_error(-20001,'No students registered for '||p_Department||' '||p_Course);
    end if;
    
    select avg(decode(grade,'A',5,'B',4,'C',3,'D',2,'E',1)) into v_NumericGrade
    from registered_students
    where department = p_Department and course = p_Course;
    
    select decode(round(v_NumericGrade),5,'A',4,'B',3,'C',2,'D',1,'E')
    into v_AverageGrade from dual;
    return v_AverageGrade;
 end AverageGrade;
   
create or replace package Debug as
  --select debug_str from debug_table order by linecount;
    procedure Debug(p_Description in varchar2,p_Value in varchar2);
    procedure Reset;
  end Debug;
  
create or replace package body Debug as
  v_LineCount number;
  
  --procedure debug
  procedure Debug(p_Description in varchar2,p_Value in varchar2) is
  begin 
    insert into debug_table(linecount,debug_str)
      values(v_LineCount,p_Description||':'||p_Value);
    commit;
    v_LineCount := v_LineCount+1;
  end Debug;
  --procedure reset
  procedure Reset is
  begin
    v_LineCount:=1;
    delete from debug_table;
  end Reset;
  
  begin
    reset;
  end Debug;

 Then we can execute this function to test,the log will be show when:

select * from debug_table

 This mode donesnot good enough for us,it may caused some errors.Use "dbms_output.put_line" is a better way .