Oracle Web Application Programming for PL/SQL Developers

Solutions Specification for get_student

Description: Create a PSP called Get_Student.psp.  This PSP is an intermediate PSP to be created solely for the purpose of handling the programmatic decision as to whether to invoke Student_Personal_Info.psp or Student_List.psp.

1. When Search_Student passes the student last_name and first_name parameter information to Get_Student, create a cursor that uses the parameters as its filter (WHERE clause).

2. To handle NULLS don't use too many IF constructs.  Instead, make use of Oracle's NVL function.

3. When looping through the cursor, you might try using a record counter to help you accomplish your decision branching logic.

4. If you have more than one matching result in your cursor, invoke Student_List. If you have just one matching record, invoke Student_Personal_Info. Otherwise, display a JavaScript alert to your user informing her that no records match her search criteria. (This will especially come in handy if anyone has entered something along the lines of !@#$% .)

5. VERY IMPORTANT: If you create the ALERT, be sure to recall Search_Student so that your user may try again.

Sample Code

<%@ page language="PL/SQL" %>
<%@ plsql procedure="get_student" %>
<%@ plsql parameter="p_first_name" default="null" %>
<%@ plsql parameter="p_last_name" default="null" %>
<%
------------------------------------------------------
-- FILENAME:    get_student.psp
-- FILEDATE:    02.02.2002
-- CREATED BY:  Melanie Caffrey
-- DESCRIPTION: Get Student
-- URL :        http://local_host/pls/any/get_student
------------------------------------------------------
%>
<%! CURSOR get_student IS
    SELECT student_id
      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));

v_counter INTEGER := 0;
v_student_id student.student_id%TYPE := 0;
%>
<HTML>
<HEAD>
<TITLE>Get Student</TITLE>
</HEAD>
<BODY BGCOLOR="#99CCCC">
<% FOR rec IN get_student
  
LOOP
     
v_counter    := v_counter + 1;
     
v_student_id := rec.student_id;
  
END LOOP;
  
IF v_counter > 1
  
THEN
     
student_list(p_first_name, p_last_name);
  
ELSIF v_counter = 1
  
THEN
     
student_personal_info(v_student_id); 
  
ELSE
%>
<SCRIPT language="JavaScript">   alert("Sorry. No Records Match Your Search Criteria.");
</SCRIPT>
<%    search_student;
  
END IF;
%>
</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