Oracle Web Application Programming for PL/SQL Developers

Solutions Specification for student_zipcode

Description: Create a PSP called Student_Zipcode.psp. This PSP, used to invoke a JavaScript popup window, allowing a user to choose a zip code value for the student she is editing, should be reusable.

1. If the student being edited exists in the database, then that student's zip code value should be pre-selected in the zip code JavaScript pop-up window when the pop-up is invoked.

2. Otherwise, no zip code value should be pre-selected.

Sample Screen

Here is an example of a zip code pop-up window with a student's zip code value pre-selected.

Here is an example of a zip code pop-up window where no zip code value is pre-selected.

Sample Code

<%@ page language="PL/SQL" %>
<%@ plsql procedure="student_zipcode" %>
<%@ plsql parameter="p_student_id" type="number" default="null" %> 
<%
---------------------------------------------------------
-- FILENAME:    student_zipcode.psp
-- FILEDATE:    02.02.2002
-- CREATED BY:  Melanie Caffrey
-- DESCRIPTION: Change Zipcode (and City and State)
-- URL:         http://local_host/pls/any/student_zipcode
---------------------------------------------------------
%>
<%! CURSOR c_zip IS
    SELECT city, state, zip
      FROM zipcode
    ORDER BY state, city, zip;

    v_count INTEGER        := 0;
    v_zip zipcode.zip%TYPE := NULL;
%>

<% SELECT COUNT(*)
     INTO v_count
     FROM student
    WHERE student_id = p_student_id; 

   IF v_count > 0
   THEN 
      SELECT zip
        INTO v_zip
        FROM student
       WHERE student_id = p_student_id; 
   END IF;
%>

<HTML>
<HEAD>
<TITLE>Student Zipcodes</TITLE>
<SCRIPT LANGUAGE="JavaScript">
   function chooseZip() {
   window.opener.student_personal_form.p_zip.value=document.zip_form.p_newzip.value;
   window.close();
}
</SCRIPT>
<%
-- Remember that the code for this JavaScript function 
-- MUST be written on one line. Otherwise, your
-- function call with fail.
%>
</HEAD>
<BODY BGCOLOR="pink">
<H2>List of Available Zipcodes With Associated City/State Values</H2>
<FORM NAME="zip_form" ACTION="">
<CENTER><TABLE BORDER="1" BORDERCOLOR="forest green" CELLPADDING=5>
<TR>
<TH ALIGN="center">City, State and Zipcode</TH>
</TR>
<TR>
<TD ALIGN="left"><SELECT SIZE="20" SCROLLBARS="yes" NAME="p_newzip">
<% FOR rec IN c_ZIP
   LOOP
      IF rec.zip = v_zip
      THEN
%> 
<OPTION VALUE="<%= rec.zip %>" SELECTED><%= rec.city %>, <%= rec.state %> <%= rec.zip %>
</OPTION>
   <% ELSE %>
<OPTION VALUE="<%= rec.zip %>"><%= rec.city %>, <%= rec.state %> <%= rec.zip %>
</OPTION>
   <% END IF;
   END LOOP;
%>
</SELECT>
</TD>
</TR>
</TABLE></CENTER>
<BR>
<CENTER><INPUT TYPE="button" VALUE="Select Zipcode" onclick="javascript:chooseZip();">&nbsp;&nbsp;
<INPUT TYPE="button" VALUE="Close" onClick="window.close();"></CENTER>
</FORM>
</BODY>
</HTML>

Select a Sample Application Code Unit

main_frame (15.2)
top_menu (13.3) / (15.2)
splash (13.1)
student_main_frame (6.3)
students_left_nav (6.3)
search_student (12.4)
get_student (12.4)
student_list (12.4)
student_personal_info (12.4)
student_zipcode (18.5)
update_student (18.5)
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)
classes_main_frame (6.3)
classes_left_nav (6, Building App)
classes_list (6, Building App)
classes_location (13.4)
classes_location_update (13.4)
set_cookie (15.2)
get_cookie (15.2)
cookie_exists (15.2)
visitor_name (15.2)
process_visitor_name (15.2)
student_list_update (not in book)

Main Solutions Page