Description: The Instructor package was complete with the following elements:
CREATE OR REPLACE
PACKAGE INSTRUCT
AS
---------------------------------------------------------------
-- FILE : instruct_header.sql
-- CREATED BY : Ben Rosenzweig
-- CREATED BY DATE: January 12, 2003
---------------------------------------------------------------
V_FONT VARCHAR2(150);
CURSOR c_instruct_list IS
SELECT first_name, last_name, instructor_id
FROM instructor;
TYPE Inst_Tbl_Type IS TABLE OF c_instruct_list%ROWTYPE
INDEX BY BINARY_INTEGER;
Instruct_Table inst_tbl_type;
i NUMBER := 1;
PROCEDURE grade_calc
(P_student_id IN student.student_id%type,
p_section_id IN section.section_id%TYPE,
p_final_grade OUT enrollment.final_grade%TYPE,
p_exit_code OUT CHAR);
PROCEDURE instruct_personal_info
(p_instructor_id IN instructor.instructor_id%TYPE);
PROCEDURE instructor_list_info;
PROCEDURE showzip
(p_zip IN instructor.zip%TYPE DEFAULT '10019');
PROCEDURE instructors_frame;
PROCEDURE instructors_left_nav;
PROCEDURE update_instructor
(p_instructor_id IN instructor.instructor_id%TYPE,
p_salutation IN instructor.salutation%TYPE,
p_first_name IN instructor.first_name%TYPE,
p_last_name IN instructor.last_name%TYPE,
p_street_address IN instructor.street_address%TYPE,
p_zip IN instructor.zip%TYPE,
p_phone IN instructor.phone%TYPE);
PROCEDURE instructor_list_class;
PROCEDURE instruct_classes
(p_instructor_id IN instructor.instructor_id%TYPE);
PROCEDURE student_list
(p_section_id IN section.section_id%TYPE DEFAULT 87);
END INSTRUCT;
CREATE OR REPLACE
PACKAGE BODY INSTRUCT AS
---------------------------------------------------------------
-- FILE : instruct_body.sql
-- CREATED BY : Ben Rosenzweig
-- CREATED BY DATE: January 12, 2003
---------------------------------------------------------------
---------------------------------------------------------------
-- NAME : grade_calc
-- CREATED BY : Ben Rosenzweig
-- DESCRIPTION: Calculates the final grade for the student and
-- section passed in.
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.grade_calc
---------------------------------------------------------------
PROCEDURE grade_calc
(P_student_id IN student.student_id%type,
P_section_id IN section.section_id%TYPE,
P_final_grade OUT enrollment.final_grade%TYPE,
P_exit_code OUT CHAR)
AS
v_student_id student.student_id%TYPE;
v_section_id section.section_id%TYPE;
v_grade_type_code grade_type_weight.grade_type_code%TYPE;
v_grade_percent NUMBER;
v_final_grade NUMBER;
v_grade_count NUMBER;
v_lowest_grade NUMBER;
v_exit_code CHAR(1) := 'S';
-- Next two variables are used to calculate whether a cursor
-- has no result set.
v_no_rows1 CHAR(1) := 'N';
v_no_rows2 CHAR(1) := 'N';
e_no_grade EXCEPTION;
-- Cursor to loop through all grade types for a given section.
CURSOR c_grade_type
(pc_section_id section.section_id%TYPE)
IS
SELECT GRADE_TYPE_CODE,
NUMBER_PER_SECTION,
PERCENT_OF_FINAL_GRADE,
NVL(DROP_LOWEST, 'N') DROP_LOWEST
FROM grade_Type_weight
WHERE section_id = pc_section_id
AND section_id IN (SELECT section_id
FROM grade
WHERE student_id = p_student_id);
-- Cursor to loop through all grades for a given student
-- in a given section.
CURSOR c_grades
(p_grade_type_code
grade_Type_weight.grade_type_code%TYPE,
pc_student_id student.student_id%TYPE,
pc_section_id section.section_id%TYPE) IS
SELECT grade_type_code,grade_code_occurrence,
numeric_grade
FROM grade
WHERE student_id = pc_student_id
AND section_id = pc_section_id
AND grade_type_code = p_grade_type_code;
BEGIN
v_section_id := p_section_id;
v_student_id := p_student_id;
-- Start loop of grade types for the section.
FOR r_grade in c_grade_type(v_section_id)
LOOP
-- Since cursor is open it has a result
-- set, change indicator.
v_no_rows1 := 'Y';
-- To hold the number of grades per section,
-- reset to 0 before detailed cursor loops
v_grade_count := 0;
v_grade_type_code := r_grade.GRADE_TYPE_CODE;
-- Variable to hold the lowest grade.
-- 500 will not be the lowest grade.
v_lowest_grade := 500;
-- Determine what to multiply a grade by to
-- compute final grade, must take into consideration
-- if the drop lowest grade indicator is Y
SELECT (r_grade.percent_of_final_grade /
DECODE(r_grade.drop_lowest, 'Y',
(r_grade.number_per_section - 1),
r_grade.number_per_section
))* 0.01
INTO v_grade_percent
FROM dual;
-- Open cursor of detailed grade for a student in a
-- given section.
FOR r_detail in c_grades(v_grade_type_code,
v_student_id, v_section_id) LOOP
-- Since cursor is open it has a result
-- set, change indicator.
v_no_rows2 := 'Y';
v_grade_count := v_grade_count + 1;
-- Handle the situation where there are more
-- entries for grades of a given grade type
-- than there should be for that section.
If v_grade_count > r_grade.number_per_section THEN
v_exit_code := 'T';
raise e_no_grade;
END IF;
-- If drop lowest flag is Y determine which is lowest
-- grade to drop
IF r_grade.drop_lowest = 'Y' THEN
IF nvl(v_lowest_grade, 0) >=
r_detail.numeric_grade
THEN
v_lowest_grade := r_detail.numeric_grade;
END IF;
END IF;
-- Increment the final grade with percentage of current
-- grade in the detail loop.
v_final_grade := nvl(v_final_grade, 0) +
(r_detail.numeric_grade * v_grade_percent);
END LOOP;
-- Once detailed loop is finished, if the number of grades
-- for a given student for a given grade type and section
-- is less than the required amount, raise an exception.
IF v_grade_count < r_grade.NUMBER_PER_SECTION THEN
v_exit_code := 'I';
raise e_no_grade;
END IF;
-- If the drop lowest flag was Y then you need to take
-- the lowest grade out of the final grade, it was not
-- known when it was added which was the lowest grade
-- to drop until all grades were examined.
IF r_grade.drop_lowest = 'Y' THEN
v_final_grade := nvl(v_final_grade, 0) -
(v_lowest_grade * v_grade_percent);
END IF;
END LOOP;
-- If either cursor had no rows then there is an error.
IF v_no_rows1 = 'N' OR v_no_rows2 = 'N' THEN
v_exit_code := 'N';
raise e_no_grade;
END IF;
P_final_grade := v_final_grade;
P_exit_code := v_exit_code;
EXCEPTION
WHEN e_no_grade THEN
P_final_grade := null;
P_exit_code := v_exit_code;
WHEN OTHERS THEN
P_final_grade := null;
P_exit_code := 'E';
END grade_calc;
---------------------------------------------------------------
-- NAME : instruct_personal_info
-- CREATED BY : Sol Morse, Susan Boardman
-- DESCRIPTION: Form to collect instructor personal information
-- for the instructor id passed in.
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instruct_personal_info
---------------------------------------------------------------
PROCEDURE instruct_personal_info
(p_instructor_id IN instructor.instructor_id%TYPE)
AS
v_instructor_id instructor.instructor_id%TYPE;
v_salutation instructor.salutation%TYPE;
v_first_name instructor.first_name%TYPE;
v_last_name instructor.last_name%TYPE;
v_street_address instructor.street_address%TYPE;
v_city zipcode.city%TYPE;
v_state zipcode.state%TYPE;
v_zip instructor.zip%TYPE;
v_phone instructor.phone%TYPE;
BEGIN
SELECT instructor_id,
salutation,
first_name,
last_name,
street_address,
city,
state,
i.zip,
phone
INTO v_instructor_id,
v_salutation,
v_first_name,
v_last_name,
v_street_address,
v_city,
v_state,
v_zip,
v_phone
FROM instructor i, zipcode z
WHERE i.zip = z.zip
AND instructor_id = p_instructor_id;
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<TITLE>Instructor Personal Info</TITLE>');
htp.p('</HEAD>');
htp.p('<BODY bgColor="#99CCCC">');
htp.p('<CENTER>');
htp.p('<H1>Personal Info For '||v_first_name||' '||v_last_name||'</H1>');
htp.p('<H2>This Is The Current Information On Record.</H2>');
htp.p('<H3>To Make Changes, Edit The Information And Select ''Save''.</H3>');
htp.p('<FORM ACTION="instruct.update_instructor" METHOD="get" NAME="instructor_personal_form">');
htp.p('<INPUT TYPE="hidden" NAME="p_instructor_id" VALUE="'||v_instructor_id||'">');
htp.p('<TABLE >');
htp.p('<TR>');
htp.p('<TD>Salutation:</TD>');
htp.p('<TD><INPUT TYPE="text" NAME="p_salutation" VALUE="'||v_salutation||'" SIZE="5"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>First Name:</TD>');
htp.p('<TD><INPUT TYPE="text" NAME="p_first_name" VALUE="'||v_first_name||'" SIZE="25"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>Last Name:</TD>');
htp.p('<TD><INPUT TYPE="text" NAME="p_last_name" VALUE="'||v_last_name||'" SIZE="25"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>Street Address:</TD>');
htp.p('<TD><INPUT TYPE="text" NAME="p_street_address" VALUE="'||v_street_address||'"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>City:</TD>');
htp.p('<TD>'||v_city||'</TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>State:</TD>');
htp.p('<TD>'||v_state||'</TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>Zipcode:</TD>');
htp.p('<TD><INPUT TYPE="text" NAME="p_zip" VALUE="'||v_zip||'"></TD>');
htp.p('<TD><INPUT TYPE="button" VALUE="Change Zipcode" onClick="javascript:window.open(''instruct.showzip?p_zip=''+document.instructor_personal_form.p_zip.value, ''instructor_zip'', ''TOOLBAR=NO, STATUS=yes, MENUBAR=no, SCROLLBARS=auto, RESIZABLE=yes, WIDTH=640, HEIGHT=480'');"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD>Telephone:</TD>');
htp.p('<TD><INPUT TYPE="text" VALUE="'||v_phone||'" NAME="p_phone" MAXLENGTH="15" SIZE="15"></TD>');
htp.p('</TR>');
htp.p('<TR>');
htp.p('<TD></TD>');
htp.p('<TD ALIGN=left>');
htp.p('<INPUT TYPE="submit" VALUE="SUBMIT">');
htp.p('<INPUT TYPE="reset" VALUE="RESET">');
htp.p('</TD>');
htp.p('</TR>');
htp.p('</TABLE>');
htp.p('</FORM>');
htp.p('</CENTER>');
htp.p('</BODY>');
htp.p('</HTML>');
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END instruct_personal_info;
---------------------------------------------------------------
-- NAME : instructor_list_info
-- CREATED BY : Susan Boardman
-- DESCRIPTION: List of hyperlinked instructor names
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instructor_list_info
---------------------------------------------------------------
PROCEDURE instructor_list_info
IS
CURSOR get_instructor IS
SELECT first_name, last_name, instructor_id
FROM instructor
ORDER BY last_name, first_name;
BEGIN
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<TITLE>Instructor Maintenance Web Site</TITLE>');
htp.p('</HEAD>');
htp.p('<BODY BgColor="#99CCCC">');
htp.p('<CENTER>');
htp.p('<H2>List of Instructors</H2>');
htp.p('<TABLE ALIGN="center" BORDER="3" BORDERCOLOR="midnight blue"
CELLPADDING="5" WIDTH="100%">');
htp.p('<TR>');
htp.p('<TH ALIGN="center">Instructor Names</TH>');
htp.p('</TR>');
FOR rec IN get_instructor
LOOP
htp.p('<TR>');
htp.p('<TD><FONT FACE="Arial">
<A HREF="instruct.instruct_personal_info?p_instructor_id='
||rec.instructor_id||'">'
||rec.last_name||','||rec.first_name||'</A></FONT></TD>');
htp.p('</TR>');
END LOOP;
htp.p('</TABLE>');
htp.p('</CENTER>');
htp.p('</BODY>');
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END instructor_list_info;
---------------------------------------------------------------
-- NAME : showzip
-- CREATED BY : Sol Morse, Susan Boardman
-- DESCRIPTION: List of hyperlinked instructor names
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.showzip
---------------------------------------------------------------
PROCEDURE showzip
(p_zip IN instructor.zip%TYPE DEFAULT '10019')
IS
CURSOR c_zip IS
SELECT city, state, zip
FROM zipcode
ORDER BY state, city, zip;
BEGIN
htp.htmlOpen;
htp.headOpen;
htp.title('Instructor Zipcode');
htp.script('
<!--Begin hiding javascript contents from old browsers.
function chooseZip() {
window.opener.instructor_personal_form.p_zip.value=document.zipcode_form.p_new_zip.value;
window.close();
}
//End the hiding here-->', 'JavaScript');
htp.headClose;
htp.bodyOpen(cattributes => 'BGCOLOR="#99CCCC"');
htp.centerOpen;
htp.p('<FORM NAME="zipcode_form" ACTION="" METHOD="post">');
htp.p('<SELECT NAME="p_new_zip" SIZE="15">');
FOR rec IN c_zip
LOOP
IF rec.zip = p_zip THEN
htp.p('<OPTION VALUE="'||rec.zip||'" SELECTED>'
||rec.city||', '||rec.state||' '||rec.zip||
'</OPTION>');
ELSE
htp.p('<OPTION VALUE="'||rec.zip||'">'
||rec.city||', '||rec.state||' '||rec.zip||
'</OPTION>');
END IF;
END LOOP;
htp.p('</SELECT>');
htp.p('<BR>');
htp.p('<BR>');
htp.p('<INPUT TYPE="button" VALUE="Select Zipcode" onClick="chooseZip();">');
htp.p('<INPUT TYPE="button" VALUE="Cancel" onClick="window.close();">');
htp.formClose;
htp.centerClose;
htp.bodyClose;
htp.htmlClose;
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END showzip;
---------------------------------------------------------------
-- NAME : instructors_frame
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Creates frameset for instructor-related pages
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instructors_frame
---------------------------------------------------------------
PROCEDURE instructors_frame AS
BEGIN
htp.htmlOpen;
htp.headOpen;
htp.title('Instructors Frame');
htp.headClose;
htp.framesetOpen(NULL,'150,*');
htp.frame('instruct.instructors_left_nav','instructors_left',null,null,null,'Y');
htp.frame('instruct.instructor_list_info','instructors_main',null,null,'AUTO');
htp.noframesOpen;
htp.p('<BODY>
<P>This page uses frames, but your browser doesn''t support them.</P>
</BODY>');
htp.noframesClose;
htp.framesetClose;
htp.htmlClose;
END instructors_frame;
---------------------------------------------------------------
-- NAME : instructors_left_nav
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Paints left nav menus for instructor-related pages
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instructors_left_nav
---------------------------------------------------------------
PROCEDURE instructors_left_nav
AS
BEGIN
htp.p('
<HTML>
<HEAD>
<TITLE>Instructors Left Nav</TITLE>
</HEAD>
<BODY BGCOLOR="#99CCCC">
<TABLE BORDER="0" WIDTH="100%">
<TR>
<TD ALIGN="center">
<A HREF="instruct.instructor_list_info" TARGET="instructors_main">
<FONT FACE="Arial"><SMALL>Edit Instructor</SMALL></FONT>
</A>
</TD>
</TR>
<TR>
<TD ALIGN="center">
<A HREF="instruct.instructor_list_class" TARGET="instructors_main">
<FONT FACE="Arial"><SMALL>View Classes</SMALL></FONT>
</A>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
');
END instructors_left_nav;
---------------------------------------------------------------
-- NAME : update_instructor
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Updates instructor record with information
-- collected in instruct.instruct_personal_info
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.update_instructor
---------------------------------------------------------------
PROCEDURE update_instructor
(p_instructor_id IN instructor.instructor_id%TYPE,
p_salutation IN instructor.salutation%TYPE,
p_first_name IN instructor.first_name%TYPE,
p_last_name IN instructor.last_name%TYPE,
p_street_address IN instructor.street_address%TYPE,
p_zip IN instructor.zip%TYPE,
p_phone IN instructor.phone%TYPE)
IS
BEGIN
UPDATE instructor
SET salutation = p_salutation,
first_name = p_first_name,
last_name = p_last_name,
street_address = p_street_address,
zip = p_zip,
phone = p_phone
WHERE instructor_id = p_instructor_id;
COMMIT;
htp.p('<HTML>
<HEAD><TITLE>Update Instructor Personal Info</TITLE></HEAD>
<BODY>');
htp.script('alert("The new information for this instructor has been saved successfully!");', 'JavaScript');
htp.p('</BODY></HTML>');
instruct.instruct_personal_info(p_instructor_id);
htp.script('document.write("<H3><CENTER><FONT COLOR=RED>The new information for this instructor has been successfully saved!</FONT></CENTER></H3>");',
'JavaScript');
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END update_instructor;
---------------------------------------------------------------
-- NAME : instructor_list_class
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Hyperlinked list of instructors
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instructor_list_class
---------------------------------------------------------------
PROCEDURE instructor_list_class
IS
CURSOR get_instructor IS
SELECT first_name, last_name, instructor_id
FROM instructor
ORDER BY last_name, first_name;
BEGIN
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<TITLE>Instructor Maintenance Web Site</TITLE>');
htp.p('</HEAD>');
htp.p('<BODY BgColor="#99CCCC">');
htp.p('<CENTER>');
htp.p('<H2>List of Instructors</H2>');
htp.p('<TABLE ALIGN="center" BORDER="3" BORDERCOLOR="midnight blue"
CELLPADDING="5" WIDTH="100%">');
htp.p('<TR>');
htp.p('<TH ALIGN="center">Instructor Names</TH>');
htp.p('</TR>');
FOR rec IN get_instructor
LOOP
htp.p('<TR>');
htp.p('<TD><FONT FACE="Arial">
<A HREF="instruct.instruct_classes?p_instructor_id='
||rec.instructor_id||'">'
||rec.last_name||','||rec.first_name||'</A></FONT></TD>');
htp.p('</TR>');
END LOOP;
htp.p('</TABLE>');
htp.p('</CENTER>');
htp.p('</BODY>');
htp.p('</HTML>');
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred in instructor_list_class: '
||SQLERRM||'. Please try again later.');
END instructor_list_class;
---------------------------------------------------------------
-- NAME : instruct_classes
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Lists classes taught by instructor passed in.
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.instruct_classes
---------------------------------------------------------------
PROCEDURE instruct_classes
(p_instructor_id IN instructor.instructor_id%TYPE)
IS
v_instructor_name VARCHAR2(60);
v_count INTEGER := 0;
CURSOR c_courses IS
SELECT s.course_no, description, section_id,
TO_CHAR(start_date_time, 'DD-MON-YY') start_date,
TO_CHAR(start_date_time, 'HH:MI A.M.') start_time,
location
FROM course c, section s
WHERE c.course_no = s.course_no
AND instructor_id = p_instructor_id
ORDER BY s.course_no, section_id;
BEGIN
SELECT first_name||' '||last_name
INTO v_instructor_name
FROM instructor
WHERE instructor_id = p_instructor_id;
SELECT COUNT(*)
INTO v_count
FROM course c, section s
WHERE c.course_no = s.course_no
AND instructor_id = p_instructor_id
ORDER BY s.course_no, section_id;
htp.p('<HTML>
<HEAD><TITLE>Instructor View Classes</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF">
<CENTER>');
IF v_count > 0
THEN
htp.p('<H1>Classes for '||v_instructor_name||'</H1>
<H2>Below is the current list of courses.</H2>
<TABLE ALIGN="center" BORDER=3 BORDERCOLOR="#0099CC" CELLPADDING=5 WIDTH="100%">
<TR>
<TH>Course No.</TH>
<TH>Course_Description</TH>
<TH>Section ID</TH>
<TH>Start_Date/Time</TH>
<TH>Location</TH>
<TH>Roster</TH>
</TR>');
FOR rec IN c_courses
LOOP
htp.p('<TR>
<TD ALIGN="center">'||rec.course_no||'</TD>
<TD ALIGN="center">'||rec.description||'</TD>
<TD ALIGN="center">'||rec.section_id||'</TD>
<TD ALIGN="center">'||rec.start_date||'<br>'||rec.start_time||'</TD>
<TD ALIGN="center">'||rec.location||'</TD>
<TD ALIGN="center">'||rec.section_id||'</TD>
<TD><INPUT TYPE="button" VALUE="Student List"
onClick="javascript:window.open(''instruct.student_list?p_section_id='||rec.section_id||''',
''student_list'', ''toolbar=no, status=yes, menubar=no, scrollbars=auto, resizable=yes, width=640, height=480''); ">
</TD>
</TR>');
END LOOP;
htp.p('</TABLE>');
ELSE
htp.p('<H3>'||v_instructor_name||' is currently on sabbatical.</H3>');
END IF;
htp.p('</CENTER></BODY></HTML>');
EXCEPTION
WHEN OTHERS
THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END instruct_classes;
---------------------------------------------------------------
-- NAME : student_list
-- CREATED BY : Susan Boardman
-- DESCRIPTION: Lists students enrolled in section passed in.
-- CREATED BY DATE: January 12, 2003
-- URL: instruct.student_list
---------------------------------------------------------------
PROCEDURE student_list
(p_section_id IN section.section_id%TYPE DEFAULT 87)
IS
v_section_no section.section_no%TYPE;
v_course_no course.course_no%TYPE;
CURSOR c_students IS
SELECT s.student_id, first_name, last_name,
street_address, city, state, s.zip,
final_grade
FROM student s, zipcode z, enrollment e
WHERE s.zip = z.zip
AND s.student_id = e.student_id
AND section_id = p_section_id
ORDER BY s.student_id;
BEGIN
SELECT section_no, course_no
INTO v_section_no, v_course_no
FROM section
WHERE section_id = p_section_id;
htp.p('<HTML>
<HEAD>
<TITLE>Instructor Classes/Section Student List</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<CENTER>
<H2>Student List for Section '||v_section_no
||' of Course '||v_course_no||'</H2>
<TABLE BORDER=3 BORDERCOLOR="#0099CC" CELLPADDING=5>
<TR>
<TH>Student ID</TH>
<TH>First Name</TH>
<TH>Last Name</TH>
<TH>Address</TH>
<TH>City, State and Zip</TH>
<TH>Final Grade</TH>
</TR>');
FOR rec IN c_students
LOOP
htp.p('
<TR><TD ALIGN="center">'||rec.student_id||'</TD>
<TD ALIGN="center">'||rec.first_name||'</TD>
<TD ALIGN="center">'||rec.last_name||'</TD>
<TD ALIGN="center">'||rec.street_address||'</TD>
<TD ALIGN="center">'||rec.city||', '||rec.state
||' '||rec.zip||'</TD>');
IF rec.final_grade IS NULL
THEN
htp.p('<TD ALIGN="center">
<FORM NAME="my_repeating_form"
ACTION="student_list_update" METHOD="POST">
<INPUT TYPE="hidden" NAME="p_student_id"
VALUE="'||rec.student_id||'">
<INPUT TYPE="hidden" NAME="p_section_id"
VALUE="'||p_section_id||'">
<INPUT TYPE="submit"
VALUE="Calculate Grade">
</FORM>
</TD>');
ELSE
htp.p('<TD ALIGN="center">'||rec.final_grade||'</TD>');
END IF;
htp.p('</TR>');
END LOOP;
htp.p('</TABLE>');
htp.p('<BR>');
htp.p('<INPUT TYPE="button" VALUE="Close"
onClick="window.close();">');
htp.p('</CENTER>');
htp.p('</BODY>');
htp.p('</HTML>');
EXCEPTION
WHEN OTHERS THEN
htp.p('An error occurred: '||SQLERRM||'. Please try again later.');
END student_list;
BEGIN /* One time Code */
FOR rec IN c_instruct_list LOOP
Instruct_Table( i ).first_name := rec.first_name;
Instruct_Table( i ).last_name := rec.last_name;
Instruct_Table( i ).instructor_id := rec.instructor_id;
i := i + 1;
END LOOP;
END INSTRUCT;