procedure student_menu(u_access in varchar2 default NULL,
id in number) as
access_check varchar2(30);
no_access exception;
cursor c1 is
select courses.term,cno,courses.lineno
from courses,enrolls
where enrolls.sid = id and
courses.term = enrolls.term and
courses.lineno = enrolls.lineno;
begin
if (u_access = NULL) then
raise no_access;
end if;
access_check := user_access.check_access(u_access);
if (access_check != 'Student') then
raise no_access;
end if;
htp.htmlOpen;
htp.headOpen;
htp.htitle('Student Menu');
htp.headClose;
htp.bodyOpen;
htp.formOpen(owa_util.get_owa_service_path ||
'menus.process_student_option');
htp.formSelectOpen('tcl','Term: ');
for c1_rec in c1 loop
htp.formSelectOption('(' ||c1_rec.term||','||
c1_rec.cno||','||
c1_rec.lineno || ')');
end loop;
htp.formSelectClose;
htp.nl;
htp.formHidden('u_access', u_access);
htp.formHidden('id', id);
htp.formSubmit('opt','Display Scores');
htp.formSubmit('opt','Change Password');
htp.formSubmit('opt','Logout');
htp.formclose;
htp.bodyClose;
htp.htmlClose;
Exception
when no_access then
htp.print('You do not have an access to this page');
when others then
htp.print('Error in menus.student_menu');
end student_menu;
function check_access(
u_access in varchar2 default NULL)
return varchar2 as
return_value varchar(30);
auth_buffer varchar(20);
no_access exception;
begin
if u_access is null
then raise no_access;
end if;
select authority into auth_buffer
from users U
where U.u_access = check_access.u_access;
return_value := auth_buffer;
return(return_value);
Exception
when no_data_found then return('0');
when no_access then return ('0');
when others then
htp.print('Error in user_access.check_access');
end check_access;
procedure process_student_option(
u_access in varchar2 default NULL,
opt in varchar2 default null,
tcl in varchar2 default null,
id in number) as
begin
if (opt = 'Display Scores') then
display.display_own_scores(id,tcl,u_access);
elsif (opt = 'Change Password') then
user_access.change_password(u_access);
elsif (opt = 'Logout') then
user_access.logout(u_access);
end if;
end;
procedure display_own_scores(
id in number,
tcl in varchar2,
u_access in varchar2 default NULL) as
counter integer := 0;
access_check varchar2(30);
no_access exception;
trm scores.term%type;
lnum scores.lineno%type;
p1 integer;
p2 integer;
cursor c1 is
select components.compname,points,maxpoints
from scores,components
where sid = id and scores.term = trm and
scores.lineno = lnum and
components.compname = scores.compname and
components.term = scores.term and
components.lineno = scores.lineno;
begin
if (u_access = NULL) then
raise no_access;
end if;
access_check := user_access.check_access(u_access);
if (access_check != 'Student') then
raise no_access;
end if;
htp.htmlOpen;
htp.headOpen;
htp.title('Display Scores');
htp.header(1,'Student Scores');
htp.headClose;
htp.nl;
htp.bodyOpen;
p1 := instr(tcl,',',1,2)+1;
p2 := instr(tcl,')',1,1)-1;
trm := substr(tcl,2,instr(tcl,',',1)-2);
lnum := to_number(substr(tcl,p1,p2-p1+1));
htp.tableOpen(cattributes => 'border=2 width=60%' );
htp.tableRowOpen;
htp.tableHeader('Component Name');
htp.tableHeader('Points');
htp.tableHeader('Max Points');
htp.tableRowClose;
for scr_rec in c1 loop
htp.tableRowOpen;
htp.tableData(scr_rec.compname,'left');
htp.tableData(scr_rec.points,'right');
htp.tableData(scr_rec.maxpoints,'right');
htp.tableRowClose;
counter := counter + 1;
end loop;
htp.tableClose;
htp.nl;
htp.print('Number of records: ');
htp.print(counter);
htp.bodyClose;
htp.htmlClose;
Exception
when NO_DATA_FOUND then
htp.print('No records found');
when no_access then
htp.print('You are not logged on');
when others then
htp.print('Something wrong in display_own_scores');
htp.br;
htp.print('SQLCODE = ' || SQLCODE);
htp.br;
htp.print('SQLERRM = ' || SQLERRM);
end display_own_scores;
procedure logout(u_access in varchar2 default NULL) as
auth_buffer varchar(20);
no_access exception;
begin
auth_buffer := check_access(u_access);
if (auth_buffer < '1')
then raise no_access;
end if;
update users
set u_access = null
where u_access = logout.u_access;
commit;
htp.print('Thank you for using Grade Book Program');
Exception
when no_access then
htp.print('You are not logged in');
when others then
htp.print('Error in users_access.logout');
end logout;