Oracle Web Application Programming for PL/SQL Developers

Solutions Specification for student_list

Description:  Create a PSP called Student_List.psp.  If more than one record matches your user's search criteria, she should be brought to a page painted by Student_List.

1. Display the search results as a tabled list of students with each name displayed as a hyperlink to the Add/Edit Student screen (the PSP for the Add/Edit Student screen is Student_Personal_Info.)

2. Display the names (concatenated last_name, first_name) and sort this list alphabetically by last_name, first_name.

Sample Screen

student_list.gif

Sample Code

<%@ page language="PL/SQL" %>
<%@ plsql procedure="student_list" %>
<%@ plsql parameter="p_first_name" %>
<%@ plsql parameter="p_last_name" %>
<%
--------------------------------------------------------
-- FILENAME:    student_list.psp
-- FILEDATE:    02.02.2002
-- CREATED BY:  Melanie Caffrey
-- DESCRIPTION: Student List
-- URL :        http://local_host/pls/any/student_list
--------------------------------------------------------
%>
<%! CURSOR get_student IS
    SELECT student_id, first_name, last_name
      FROM student
     WHERE NVL(UPPER(first_name), 'QQ') LIKE 
           NVL(UPPER('%'||p_first_name||'%'), 'QQ')
       AND UPPER(last_name) LIKE 
           NVL(UPPER('%'||p_last_name||'%'), UPPER(last_name))
     ORDER BY last_name, first_name;
%> 
<HTML>
<HEAD>
<TITLE>Student List</TITLE>
</HEAD>
<BODY BGCOLOR="#99CCCC">
<CENTER>
<H2>List of Students</H2>
<TABLE BORDER="3" BORDERCOLOR="midnight blue" CELLPADDING="5">
<TR>
<TH ALIGN=center>Student Names</TH>
</TR>
<% FOR rec IN get_student
   LOOP 
%>
<TR>
<TD>
<A HREF="student_personal_info?p_student_id=<%= rec.student_id %>">
<FONT FACE="Arial">
<%= rec.last_name||', '||rec.first_name %>
</FONT></A>
</TD>
</TR>
<% END LOOP; %>
</TABLE>
</CENTER>
</BODY>
</HTML>

Select a Sample Application Code Unit

  1. main_frame (15.2)
  2. top_menu (13.3) / (15.2)
  3. splash (13.1)
  4. student_main_frame (6.3)
  5. students_left_nav (6.3)
  6. search_student (12.4)
  7. get_student (12.4)
  8. student_list (12.4)
  9. student_personal_info (12.4)
  10. student_zipcode (18.5)
  11. update_student (18.5)
  12. instruct (Package Spec and Body)
    • grade_calc (9.2)
    • v_font (9.4)
    • c_instruct_list (9.4)
    • inst_tbl_type (9.4)
    • instruct_table (9.4)
    • [PL/SQL block to load instruct_table] (9.4)
    • instruct_personal_info (11.3)
    • instructor_list_info (11.3)
    • showzip (11.3)
    • instructors_frame (11.1)
    • instructors_left_nav (11.1)
    • update_instructor (11.2)
    • instructor_list_class (11.3)
    • instruct_classes (11.3)
    • student_list (11.2)
  13. classes_main_frame (6.3)
  14. classes_left_nav (6, Building App)
  15. classes_list (6, Building App)
  16. classes_location (13.4)
  17. classes_location_update (13.4)
  18. set_cookie (15.2)
  19. get_cookie (15.2)
  20. cookie_exists (15.2)
  21. visitor_name (15.2)
  22. process_visitor_name (15.2)
  23. student_list_update (not in book)

Main Solutions Page