Building the Web Site
NOTE
The remainder of this chapter builds the Web site (illustrated in Figure 9.7) of Chapter 8, reprinted here.
Figure 9.7.
The Sales Tracking Web site will be based on two PL/SQL procedures: auto_display (see Listing 9.9) and auto_buy (see Listing 9.10). The Web site will also reuse the display_banner PL/SQL procedure built in Listing 9.5 and make reference to the PL/SQL procedure display_error used in Listing 9.8.
The display_error PL/SQL procedure (see Listing 9.8) is a simple Web page that accepts a parameter, p_text, (see line 3) and displays this text on the Web page at line 15. The show errors syntax in line 23 is a SQL*Plus command that's useful in displaying any errors that might have occurred when creating the procedure.
NOTE
The procedures detailed in this chapter can also be created using the Quest SQL Navigator tool.
Listing 9.8. DISPLAY_ERROR PL/SQL Procedure Syntax
1: CREATE OR REPLACE PROCEDURE display_error
2: (
3: p_text IN VARCHAR2 DEFAULT NULL
4: ) IS
5: BEGIN
6: htp.htmlopen;
7: htp.headOpen;
8: htp.title('Error Page'),
9: htp.headClose;
10: htp.bodyOpen;
11:
12: htp.big('An Error has occurred'),
13:
14: htp.hr;
15: htp.p('The following error occurred: '||p_text);
16: htp.br;
17: htp.p('Please contact Metro Motors directly at: 265-4776'),
18:
19: htp.bodyClose;
20: htp.htmlClose;
21: END;
22: /
23: show errors
|
The main page of our Web site relies on the auto_display PL/SQL procedure. Because each car has a unique inventory number (INV_ID), easy access to the rows in the ST_CARS_FOR_SALE table object is enabled. This INV_ID column also makes a convenient parameter to look up a specific row if a parameter is passed to this Web page. The page should allow us to browse forward and backward through our entire inventory and should display a link to the auto_buy screen—so that customers can make an offer on the car. This is accomplished with lines 26–69 in Listing 9.9.
Lines 1–5 highlight the procedure and the parameters that can be passed to it. The procedure accepts a source (p_source), which tells the Web page to go to the next or previous record in the ST_CARS_FOR_SALE table object. The p_source parameter defaults to DISPLAY_NEXT if no parameter is passed. As discussed earlier in this chapter, this Web page can be called with parameters or with no parameters without causing any kind of parameter passing violation. The other parameter that can be passed is an inventory number (p_inv_id) that provides the row in the ST_CARS_FOR_SALE table object from which to perform the previous or next row function. The URL for this Web page is http://<computer name or IP address> /virtual_path/auto_display, which, with no parameters, will display the first auto in the ST_CARS_FOR_SALE table object.
Listing 9.9 shows the entire code for the display_auto PL/SQL procedure. Lines 6–14 are a cursor that selects the information about the next car in our inventory. If the inventory ID passed is NULL, the Web page starts at the beginning of the ST_CARS_FOR_SALE table object. Lines 15–23 are a cursor that selects the information about the previous row in ST_CARS_FOR_SALE. If the inventory ID passed is NULL, this procedure starts with the last row in the ST_CARS_FOR_SALE table object. Line 24 declares a PL/SQL record for the cursor defined in lines 6–23. When this cursor is opened, Oracle returns the row identified by the SQL select statement. Lines 26–30 verify that any passed parameters are valid. If any passed parameters are not found to be valid, the PL/SQL procedure display_error is then called at line 31. Lines 39–69 determine the row (previous or next) and use the appropriate cursor to select the correct row from ST_CARS_FOR_SALE.
Line 75 begins to build the actual Web page. Lines 81–84 call the display_banner PL/SQL procedure that we created in the previous section of this chapter (refer to Listing 9.5). This displays the standard Metro Motors banner at the top of the Web page. Review Figure 8.7 and you will notice that the picture is on the left with the description, pricing, and navigational buttons on the right. To accomplish this, an HTML table with 4 columns and 3 rows is used. The picture takes up column 1 and rows 1–3. The description takes up row 1 and columns 2–4. The price takes up row 2 and columns 2–4. The navigation buttons take up row 3 and each button takes up one column. Lines 89–113 display the information from the database stored in sale_row (see line 24). Lines 117–139 create the Previous button, and lines 141–166 create the Next button. Finally, lines 171–193 create the Buy This Car button. This button is created in a similar fashion to the Previous and Next buttons except that it calls the auto_buy PL/SQL procedure.
Listing 9.9. AUTO_DISPLAY PL/SQL Procedure Syntax
1: CREATE OR REPLACE PROCEDURE auto_display
2: (
3: p_source IN VARCHAR2 DEFAULT 'DISPLAY_NEXT'
4: , p_inv_id IN NUMBER DEFAULT NULL
5: ) AS
6: CURSOR next_CUR
7: (
8: p_inv_id IN NUMBER
9: ) IS
10: SELECT inv_id, auto_description
11: , sale_amt, sale_photo_location
12: FROM st_cars_for_sale
13: WHERE inv_id > NVL(p_inv_id,0)
14: ORDER BY inv_id ASC;
15: CURSOR prev_CUR
16: (
17: p_inv_id IN NUMBER
18: ) IS
19: SELECT inv_id, auto_description
20: , sale_amt, sale_photo_location
21: FROM st_cars_for_sale
22: WHERE inv_id < NVL(p_inv_id,10000000)
23: ORDER BY inv_id DESC;
24: sale_row prev_CUR%ROWTYPE;
25: BEGIN
26: IF p_source NOT IN
27: (
28: 'DISPLAY_NEXT'
29: , 'DISPLAY_PREV'
30: ) THEN
31: display_error
32: (
33: p_text => p_source
34: );
35: ELSE
36: -- -------------------------------------------------
37: -- Retrieve the car that we want to display
38: -- -------------------------------------------------
39: IF p_source = 'DISPLAY_NEXT' THEN
40: OPEN next_CUR
41: (
42: p_inv_id
43: );
44: FETCH next_CUR INTO sale_row;
45: IF next_CUR%NOTFOUND THEN
46: OPEN prev_CUR
47: (
48: p_inv_id
49: );
50: FETCH prev_CUR INTO sale_row;
51: CLOSE prev_CUR;
52: END IF; -- next_CUR%NOTFOUND
53: CLOSE next_CUR;
54: ELSIF p_source = 'DISPLAY_PREV' THEN
55: OPEN prev_CUR
56: (
57: p_inv_id
58: );
59: FETCH prev_CUR INTO sale_row;
60: IF prev_CUR%NOTFOUND THEN
61: OPEN next_CUR
62: (
63: p_inv_id
64: );
65: FETCH next_CUR INTO sale_row;
66: CLOSE next_CUR;
67: END IF; -- prev_CUR%NOTFOUND
68: CLOSE prev_CUR;
69: END IF; -- p_source = 'DISPLAY_NEXT'
70:
71: -- -------------------------------------------------
72: -- Now that we have the auto to display lets
73: -- build our display screen
74: -- -------------------------------------------------
75: htp.htmlopen;
76: htp.headOpen;
77: htp.title('Metro Motors'),
78: htp.headClose;
79: htp.bodyOpen;
80:
81: display_banner
82: (
83: p_caption => 'Metro Motors Web Site'
84: );
85:
86: -- -------------------------------------------------
87: -- Display the dynamic content - the selected car
88: -- -------------------------------------------------
89: htp.tableOpen;
90: htp.tableRowOpen;
91: htp.tableData
92: (
93: cvalue => htf.img
94: (
95: curl => sale_ROW.sale_photo_location
96: , cattributes => 'WIDTH=300'
97: )
98: , crowspan => 3
99: );
100: htp.tableData
101: (
102: cvalue => 'Description: '||sale_ROW.auto_description
103: , ccolspan => 3
104: );
105: htp.tableRowClose;
106: htp.tableRowOpen;
107: htp.tableData
108: (
109: cvalue => 'Suggested Price:'||TO_CHAR(sale_ROW.sale_amt, '$999,990.00')
110: , ccolspan => 3
111: );
112: htp.tableRowClose;
113: htp.tableRowOpen;
114: -- -------------------------------------------------
115: -- previous button
116: -- -------------------------------------------------
117: htp.p('<TD>'),
118: htp.formOpen
119: (
120: curl => 'auto_display'
121: , cmethod => 'post'
122: , ctarget => ''
123: );
124: htp.formHidden
125: (
126: cname => 'p_source'
127: , cvalue => 'DISPLAY_PREV'
128: );
129: htp.formHidden
130: (
131: cname => 'p_inv_id'
132: , cvalue => TO_CHAR(sale_ROW.inv_id)
133: );
134: htp.formSubmit
135: (
136: cvalue => 'Previous'
137: );
138: htp.formClose;
139: htp.p('</TD>'),
140:
141: -- -------------------------------------------------
142: -- next button
143: -- -------------------------------------------------
144: htp.p('<TD>'),
145: htp.formOpen
146: (
147: curl => 'auto_display'
148: , cmethod => 'post'
149: , ctarget => ''
150: );
151: htp.formHidden
152: (
153: cname => 'p_source'
154: , cvalue => 'DISPLAY_NEXT'
155: );
156: htp.formHidden
157: (
158: cname => 'p_inv_id'
159: , cvalue => TO_CHAR(sale_ROW.inv_id)
160: );
161: htp.formSubmit
162: (
163: cvalue => 'Next'
164: );
165: htp.formClose;
166: htp.p('</TD>'),
167:
168: -- -------------------------------------------------
169: -- Buy this car
170: -- -------------------------------------------------
171: htp.p('<TD>'),
172: htp.formOpen
173: (
174: curl => 'auto_buy'
175: , cmethod => 'post'
176: , ctarget => ''
177: );
178: htp.formHidden
179: (
180: cname => 'p_source'
181: , cvalue => 'MAKE_OFFER'
182: );
183: htp.formHidden
184: (
185: cname => 'p_inv_id'
186: , cvalue => TO_CHAR(sale_ROW.inv_id)
187: );
188: htp.formSubmit
189: (
190: cvalue => 'Buy This Car'
191: );
192: htp.formClose;
193: htp.p('</TD>'),
194:
195: htp.tableRowClose;
196: htp.tableClose;
197:
198:
199: htp.bodyClose;
200: htp.htmlClose;
201: END IF; -- p_source NOT IN
202: END auto_display;
203: /
|
The auto_display PL/SQL procedure is accessed by pointing a Web browser to http://<computer name or IP address>/virtual_path/auto_display. Notice that the Web page functions perfectly when no parameters are passed. This same Web page (refer to the bottom-left panel of Figure 8.7) also processes parameters correctly, as in this example:
http://<computer name or IP address>/virtual_path/auto_display?p_source=DISPLAY_PREV&p_inv_id=15
The auto_buy PL/SQL procedure is called from the Web page auto_display and updates the BUYER information in the ST_CARS_FOR_SALE table object (refer to the bottom-left panel of Figure 9.7).
Lines 1–8 name the procedure (auto_buy) and define the parameters it will accept. Note that all the parameters have a default value that will allow this procedure to be called without having to supply any parameters at all. Lines 20–24 check to see whether any supplied parameters are correct and display an error (using the display_error routine built earlier in this chapter) if they are not (lines 25–28).
Lines 33–45 select the car from inventory (based on the passed p_inv_id). If the car is not found, the user is directed again to the error page.
Line 46 checks to see whether the p_source variable is set to RECORD_OFFER. If it is, the UPDATE statement in lines 47–72 is executed.
Lines 46–72 check to see whether our source should record our offer or not. If the variable p_source does not contain RECORD_OFFER, the MAKE_OFFER Web page is displayed (roughly the remainder of this PL/SQL procedure). Notice that line 101 sets the p_source variable to RECORD_OFFER so that, when the Web page is returned, this UPDATE code is then executed.
If any Oracle error codes are returned, the failure message at lines 50–53 is shown.
Lines 54–56 check the parameters to make sure that a person making the offer has filled out the name, phone, and amount fields.
Lines 58–63 are the actual UPDATE to the ST_CARS_FOR_SALE table object.
NOTE
If this were a realistic situation, we would insert a record in a separate table so that multiple buyers and offers could be tendered. This example is just to show functionality between the Web page and the Oracle database.
Lines 64–67 set the status message indicating that the offer was successfully recorded.
Lines 68–69 catch any Oracle errors that might have occurred from the UPDATE statement. If an error does occur, the new status message is not set and we are left with the failure message.
Lines 76–203 create the HTML output. Lines 82–85 display the Metro Motors standard banner, whereas lines 87–90 display the status message (if one was created) from lines 50–69. Lines 92–107 open up an HTML form and set some hidden values (parameters) that are not to be displayed but are important to the Web page when it is returned. Lines 113–195 generate the main structure of the HTML table, laying out elements similar to the way auto_display was laid out.
Notice in lines 143 and 166 that the htp.p procedure is used to hard code the HTML <TD> tags. Lines 144–149 call the htp.formtext procedure, which generates a text input box.
Lines 196–216 create a second HTML form and button that take the Web user back to the display_auto page.
Listing 9.10. AUTO_BUY PL/SQL Procedure Syntax
1: CREATE OR REPLACE PROCEDURE auto_buy
2: (
3: p_source IN VARCHAR2 DEFAULT 'MAKE_OFFER'
4: , p_inv_id IN NUMBER DEFAULT NULL
5: , p_buyer_name IN VARCHAR2 DEFAULT NULL
6: , p_buyer_phone IN VARCHAR2 DEFAULT NULL
7: , p_buyer_offer IN VARCHAR2 DEFAULT NULL
8: ) AS
9: CURSOR inv_CUR
10: (
11: p_inv_id IN NUMBER
12: ) IS
13: SELECT inv_id, auto_description
14: , sale_amt, sale_photo_location
15: FROM st_cars_for_sale
16: WHERE inv_id = p_inv_id;
17: inv_ROW inv_CUR%ROWTYPE;
18: v_status_msg VARCHAR2(200);
19: BEGIN
20: IF p_source NOT IN
21: (
22: 'MAKE_OFFER'
23: , 'RECORD_OFFER'
24: ) THEN
25: display_error
26: (
27: p_text => p_source
28: );
29: ELSE
30: -- -------------------------------------------------
31: -- get the car they are interested in
32: -- -------------------------------------------------
33: OPEN inv_CUR
34: (
35: p_inv_id
36: );
37: FETCH inv_CUR INTO inv_ROW;
38: IF inv_CUR%NOTFOUND THEN
39: CLOSE inv_CUR;
40: display_error
41: (
42: p_text => 'The auto selected could not be found'
43: );
44: ELSE
45: CLOSE inv_CUR;
46: IF p_source = 'RECORD_OFFER' THEN
47: -- -------------------------------------------------
48: -- update the table with the offer
49: -- -------------------------------------------------
50: v_status_msg := 'Offer could not be recorded at this time for '
51: ||p_buyer_name||'.<BR> Please make sure the name and phone '
52: ||' are filled in and that the offer ('||p_buyer_offer
53: ||') is a number.<BR> Please correct your data and try again.';
54: IF p_buyer_name IS NOT NULL
55: AND p_buyer_phone IS NOT NULL
56: AND p_buyer_offer IS NOT NULL THEN
57: BEGIN
58: UPDATE st_cars_for_sale
59: SET buyer_name = p_buyer_name
60: , buyer_phone = p_buyer_phone
61: , buyer_offer = TO_NUMBER(p_buyer_offer)
62: , buyer_date = SYSDATE
63: WHERE inv_id = p_inv_id;
64: v_status_msg := 'Offer for '||p_buyer_name||' on car '
65: ||inv_ROW.auto_description||' for amount '
66: ||TO_CHAR(TO_NUMBER(p_buyer_offer),'$999,990.00')
67: ||' was recorded.';
68: EXCEPTION WHEN OTHERS THEN
69: NULL; -- leave the original status message as is...
70: END;
71: END IF; -- p_buyer_name IS NOT NULL AND ...
72: END IF; -- p_source = 'RECORD_OFFER'
73: -- -------------------------------------------------
74: -- display the offer form...
75: -- -------------------------------------------------
76: htp.htmlopen;
77: htp.headOpen;
78: htp.title('Metro Motors'),
79: htp.headClose;
80: htp.bodyOpen;
81:
82: display_banner
83: (
84: p_caption => 'Metro Motors Web Site - Make an offer'
85: );
86:
87: IF v_status_msg IS NOT NULL THEN
88: htp.p(v_status_msg);
89: htp.br;
90: END IF; -- v_status_msg IS NOT NULL
91:
92: htp.formOpen
93: (
94: curl => 'auto_buy'
95: , cmethod => 'post'
96: , ctarget => ''
97: );
98: htp.formHidden
99: (
100: cname => 'p_source'
101: , cvalue => 'RECORD_OFFER'
102: );
103: htp.formHidden
104: (
105: cname => 'p_inv_id'
106: , cvalue => TO_CHAR(p_inv_id)
107: );
108:
109:
110:
111: htp.tableOpen;
112:
113: htp.tableRowOpen;
114: htp.tableData
115: (
116: cvalue => htf.img
117: (
118: curl => inv_ROW.sale_photo_location
119: , cattributes => 'WIDTH=300'
120: )
121: , crowspan => 5
122: );
123:
124: htp.tableData
125: (
126: cvalue => 'Description: '
127: , calign => 'RIGHT'
128: );
129: htp.tableData
130: (
131: cvalue => inv_ROW.auto_description
132: , calign => 'LEFT'
133: );
134:
135: htp.tableRowClose;
136:
137: htp.tableRowOpen;
138: htp.tableData
139: (
140: cvalue => 'Buyer Name:'
141: , calign => 'RIGHT'
142: );
143: htp.p('<TD ALIGN=LEFT>'),
144: htp.formText
145: (
146: cname => 'p_buyer_name'
147: , csize => 30
148: , cmaxlength => 20
149: );
150: htp.p('</TD>'),
151: htp.tableRowClose;
152:
153: htp.tableRowOpen;
154: htp.tableData
155: (
156: cvalue => 'Buyer Phone:'
157: , calign => 'RIGHT'
158: );
159: htp.p('<TD ALIGN=LEFT>'),
160: htp.formText
161: (
162: cname => 'p_buyer_phone'
163: , csize => 30
164: , cmaxlength => 10
165: );
166: htp.p('</TD>'),
167: htp.tableRowClose;
168:
169: htp.tableRowOpen;
170: htp.tableData
171: (
172: cvalue => 'Offer:'
173: , calign => 'RIGHT'
174: );
175: htp.p('<TD ALIGN=LEFT>'),
176: htp.formText
177: (
178: cname => 'p_buyer_offer'
179: , csize => 30
180: , cmaxlength => 10
181: );
182: htp.p('</TD>'),
183: htp.tableRowClose;
184:
185: htp.tableRowOpen;
186: htp.tableData
187: (
188: cvalue => ''
189: );
190: htp.p('<TD ALIGN=CENTER>'),
191: htp.formSubmit
192: (
193: cvalue => 'Place Offer'
194: );
195: htp.formClose;
196: htp.formOpen
197: (
198: curl => 'auto_display'
199: , cmethod => 'post'
200: , ctarget => ''
201: );
202: htp.formHidden
203: (
204: cname => 'p_source'
205: , cvalue => 'DISPLAY_NEXT'
206: );
207: htp.formHidden
208: (
209: cname => 'p_inv_id'
210: , cvalue => '0'
211: );
212: htp.formSubmit
213: (
214: cvalue => 'Browse Inventory'
215: );
216: htp.formClose;
217: htp.p('</TD>'),
218: htp.tableRowClose;
219:
220:
221: htp.tableClose;
222:
223: htp.bodyClose;
224: htp.htmlClose;
225: END IF; -- inv_CUR%NOTFOUND
226: END IF; -- p_source NOT IN
227: END auto_buy;
228: /
|