Unit 15.4. Storing State in Database Tables

Track Page Visits

Cookies are useful for storing small pieces of data. Using cookies in conjunction with database tables makes cookies much more useful. Information stored in the database will be held there permanently. You can analyze the statistics that accumulate in database tables.

Statistics about Web traffic are frequently a requirement for a Web application. This is the kind of information that you would want to hold in a database table, so that you can query the table later and collect more and more information over time.

In Unit 15.2, you create a cookie called VISITOR that holds the name the user provided. Since the user’s name is captured in a cookie, each request can be identified as coming from a specific user. The user’s activity—the pages the user visits—can be tracked in a database table.

First, create a table that will hold information about which pages are visited. Include the visitor name, since that can be retrieved from the VISITOR cookie. Also include the page requested.

Other potentially valuable pieces of information can be collected easily. Each request comes from an IP address, and since the IP address is included in the HTTP header, it can be retrieved. Also, the browser type can be an interesting statistic to collect, particularly if you want to be sure that you are supporting all of your users. Finally, it is useful to collect a timestamp.

Here is a sample table for holding general statistics.

FOR EXAMPLE

CREATE TABLE page_visit 
  (visitor    VARCHAR2(200), 
   ip_address VARCHAR2(20), 
   browser    VARCHAR2(200), 
   page       VARCHAR2(200), 
   timestamp  DATE); 

Instead of asking the user for a first name, you can assign them a visitor ID code. This can be a unique identifier for the visitor, and you could use a sequence to help generate this ID code for any user that does not have one already. Many sites track users this way because it does not alert the user’s attention to the fact that their requests for pages are monitored.

Now create a procedure that populates the page_visit table. One piece of information, the page name, will be passed as an IN parameter, p_page. Values for the other columns in the table can be obtained programmatically. The cookie value for the VISITOR cookie can be obtained by using the get_cookie procedure, created earlier in this chapter. The IP address and the browser are available by using the OWA_UTIL.get_cgi_env procedure, as described in Chapter 16, “The OWA_UTIL Package: Dynamic SQL, HTML, Date Utilities, and More.” The timestamp is available through the Oracle pseudocolumn, sysdate.

FOR EXAMPLE

CREATE OR REPLACE PROCEDURE track_page_visit 
     (p_page IN VARCHAR2) 
  AS 
BEGIN 
 INSERT INTO page_visit 
  (visitor, 
   ip_address, 
   browser, 
   page, 
   timestamp) 
 VALUES 
  (get_cookie('VISITOR'), 
   OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR'), 
   OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT'), 
   p_page, 
   sysdate); 
END; 

Finally, insert the call for this procedure into any page where you want to track information. Here is a sample call.

FOR EXAMPLE

track_page_visit(OWA_UTIL.GET_PROCEDURE); 

The function OWA_UTIL.get_procedure used above to pass the procedure name is described in Chapter 16. It retrieves the name of the current procedure. It could not be placed directly in the track_page_visit procedure, because the procedure name would always come up as track_page_visit.

Using this Oracle-supplied package, sometimes called a “built-in” package, offers advantages over a hard-coded procedure name. If a procedure name were hard-coded in the call to track_page_visit, then this call would need to be changed for each procedure, to hard-code a different procedure name. Also, using the owa_util.get_procedure retrieves the correct procedure name, without typos. The above line can be copied into any procedure without changing it each time. Since this call must be included in every page that you want to track, coding the call in this manner not only promotes code reusability, but also provides you with a time savings that is a distinct advantage.

Track Content

A shopping site might find it useful to know that a visitor looked at a page called show_product, but it would be more interesting to know which product was being viewed. Likewise, knowing that a user visited the instruct_personal_info page is helpful, but it might also be interesting to know which instructor’s information was viewed.

In order to do this, you create a new table called instructor_visit. It is similar to the page_visit table, except for the addition of two columns to hold the instructor’s id and name.

FOR EXAMPLE

CREATE TABLE instructor_visit 
  (visitor         VARCHAR2(200), 
   ip_address      VARCHAR2(200), 
   browser         VARCHAR2(200), 
   page            VARCHAR2(200), 
   instructor_id   NUMBER, 
   instructor_name VARCHAR2(200), 
   timestamp       DATE); 

Then, create a procedure called track_instructor_visit, based upon track_page_visit. It must include the two additional IN parameters that hold the values for populating instructor_id and instructor_name.

FOR EXAMPLE

CREATE OR REPLACE PROCEDURE track_instructor_visit 
     (p_page IN VARCHAR2, 
      p_instructor_id IN NUMBER, 
      p_instructor_name IN VARCHAR2) 
  AS 
BEGIN 
 INSERT INTO instructor_visit 
  (visitor, 
   ip_address, 
   browser, 
   page, 
   instructor_id, 
   instructor_name, 
   timestamp) 
 VALUES 
  (get_cookie('VISITOR'), 
   OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR'), 
   OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT'), 
   p_page, 
   p_instructor_id, 
   p_instructor_name, 
   sysdate); 
END; 

Finally, insert the call for this procedure into any page where you want to track students’ visited information. Here is a sample call.

FOR EXAMPLE

track_instructor_visit 
   (OWA_UTIL.GET_PROCEDURE, 
    v_instructor_id, 
    v_first_name||' '||v_last_name); 

Here is the sample call embedded into the instruct_personal_info procedure.

FOR EXAMPLE

CREATE OR REPLACE PROCEDURE instruct_personal_info 
(p_instructor_id IN instructor.instructor_id%TYPE) 
AS 
   v_instructor_id   instructor.instructor_id%TYPE; 
   v_salutation      instructor.salutation%TYPE; 
   v_first_name      instructor.first_name%TYPE; 
   v_last_name       instructor.last_name%TYPE; 
   v_street_address  instructor.street_address%TYPE; 
   v_city            zipcode.city%TYPE; 
   v_state           zipcode.state%TYPE; 
   v_zip             instructor.zip%TYPE; 
   v_phone           instructor.phone%TYPE; 
BEGIN 

   SELECT instructor_id, 
          salutation, 
          first_name, 
          last_name, 
          street_address, 
          city, 
          state, 
          i.zip, 
          phone 
     INTO v_instructor_id, 
          v_salutation, 
          v_first_name, 
          v_last_name, 
          v_street_address, 
          v_city, 
          v_state, 
          v_zip, 
          v_phone 
     FROM instructor i, zipcode z 
    WHERE i.zip        = z.zip 
      AND instructor_id = p_instructor_id; 

track_instructor_visit
							(OWA_UTIL.GET_PROCEDURE,
							v_instructor_id,
							v_first_name||' '||v_last_name); 


htp.p('<HTML>'), 

<rest of procedure> 

Note that the call to track_instructor_visit is made when the instructor’s ID, first name, and last name are known. If the call were made before the SELECT statement, then v_instructor_id, v_first_name, and v_last_name would be null, and there would be no information to pass to the track_instructor_visit procedure for the instructor’s name and ID.

In similar fashion, a news site could track exactly which articles are being rendered in the browser. For a retail application, a table called window_shopping could track each product that a user views, along with a user id from a cookie. An analysis of the data stored in window_shopping would tell a site which items generate any interest. If a user clicks that they are interested in an item, a table called shopping_cart could keep track of every item that a user was interested in by getting the visitor’s id from a cookie and storing the visitor’s id with the item they are interested in. When the user wants to see all of the items they have put into their shopping cart, a query to the shopping_cart table for the user identified by a cookie would produce the results.

Unit 15.4 Exercises

15.4.1. Create Tables to Hold Statistics

15.4.2. Compile the Procedures That Populate the Tables

15.4.3. Track Visits by Calling the Procedures

15.4.4. Display Collected Statistics

15.4.5. Track Visits to Specific Instructor Records

15.4.6. Display Instructor Statistics

Unit 15.4 Exercise Answers

15.4.1. Answer

CREATE TABLE page_visit 
  (visitor    VARCHAR2(200), 
   ip_address VARCHAR2(20), 
   browser    VARCHAR2(200), 
   page       VARCHAR2(200), 
   timestamp  DATE); 

CREATE TABLE instructor_visit 
  (visitor         VARCHAR2(200), 
   ip_address      VARCHAR2(200), 
   browser         VARCHAR2(200), 
   page            VARCHAR2(200), 
   instructor_id   NUMBER, 
   instructor_name VARCHAR2(200), 
   timestamp       DATE); 

15.4.2. Answer

CREATE OR REPLACE PROCEDURE track_page_visit 
     (p_page IN VARCHAR2) 
  AS 
BEGIN 
 INSERT INTO page_visit 
  (visitor, 
   ip_address, 
   browser, 
   page, 
   timestamp) 
 VALUES 
  (get_cookie('VISITOR'), 
   OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR'), 
   OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT'), 
   p_page, 
   sysdate); 
END; 

CREATE OR REPLACE PROCEDURE track_instructor_visit 
     (p_page IN VARCHAR2, 
      p_instructor_id IN NUMBER, 
      p_instructor_name IN VARCHAR2) 
  AS 
BEGIN 
 INSERT INTO instructor_visit 
  (visitor, 
   ip_address, 
   browser, 
   page, 
   instructor_id, 
   instructor_name, 
   timestamp) 
 VALUES 
  (get_cookie('VISITOR'), 
   OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR'), 
   OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT'), 
   p_page, 
   p_instructor_id, 
   p_instructor_name, 
   sysdate); 
END; 

15.4.3. Answer

track_page_visit(OWA_UTIL.GET_PROCEDURE); 

15.4.4. Answer

CREATE OR REPLACE PROCEDURE SHOW_PAGE_VISITS AS 
   CURSOR c_visits IS 
      SELECT * FROM PAGE_VISIT; 
BEGIN 
    htp.p('<HTML> 
       <HEAD><TITLE>Page Visits</TITLE></HEAD> 
       <BODY> 
       <TABLE BORDER="2" CELLPADDING="5"> 
         <TR><TH>Visitor</TH> 
             <TH>IP Address</TH> 
             <TH>Browser</TH> 
             <TH>Page</TH> 
             <TH>Timestamp</TH> 
         </TR>'), 
    FOR rec IN c_visits LOOP 
       htp.p('<TR><TD>'||rec.visitor||'</TD> 
                 <TD>'||rec.ip_address||'</TD> 
                 <TD>'||rec.browser||'</TD> 
                 <TD>'||rec.page||'</TD> 
                 <TD>'||TO_CHAR(rec.timestamp, 'DD-Mon-YYYY HH:MI 
                 AM')|| '</TD> 
             </TR>'), 
    END LOOP; 
    htp.p('</TABLE> 
       </BODY> 
       </HTML>'), 
EXCEPTION 
   WHEN OTHERS THEN 
     htp.p('Error in SHOW_PAGE_VISITS: '||SQLERRM); 
END; 

15.4.5. Answer

CREATE OR REPLACE PROCEDURE instruct_personal_info 
(p_instructor_id IN instructor.instructor_id%TYPE) 
AS 
   v_instructor_id   instructor.instructor_id%TYPE; 
   v_salutation      instructor.salutation%TYPE; 
   v_first_name      instructor.first_name%TYPE; 
   v_last_name       instructor.last_name%TYPE; 
   v_street_address  instructor.street_address%TYPE; 
   v_city            zipcode.city%TYPE; 
   v_state           zipcode.state%TYPE; 
   v_zip             instructor.zip%TYPE; 
   v_phone           instructor.phone%TYPE; 
BEGIN 

   SELECT instructor_id, 
             salutation, 
             first_name, 
             last_name, 
             street_address, 
             city, 
             state, 
             i.zip, 
             phone 
   INTO   v_instructor_id, 
             v_salutation, 
             v_first_name, 
             v_last_name, 
             v_street_address, 
             v_city, 
             v_state, 
             v_zip, 
             v_phone 
   FROM instructor i, zipcode z 
   WHERE i.zip = z.zip 
    AND instructor_id = p_instructor_id; 
track_instructor_visit
											(OWA_UTIL.GET_PROCEDURE,
											v_instructor_id,
											v_first_name||' '||v_last_name); 

htp.p('<HTML>'), 

<rest of procedure> 

15.4.6. Answer

CREATE OR REPLACE PROCEDURE SHOW_INSTRUCTOR_VISITS 
  AS 
   CURSOR c_visits IS 
      SELECT instructor_id, 
             instructor_name, 
             visitor, 
             count(*)  thecount 
        FROM INSTRUCTOR_VISIT 
    GROUP BY instructor_id, 
             instructor_name, 
             visitor; 
BEGIN 
    htp.p('<HTML> 
       <HEAD> 
       <TITLE>Instructor Page Visits</TITLE> 
       </HEAD> 
       <BODY> 
       <TABLE BORDER="2" CELLPADDING="5"> 
         <TR><TH>Instructor ID</TH> 
             <TH>Instructor Name</TH> 
             <TH>Visitor</TH> 
             <TH># of Visits</TH> 
         </TR>'), 
    FOR rec IN c_visits LOOP 
       htp.p('<TR><TD>'||rec.instructor_id||'</TD> 
                 <TD>'||rec.instructor_name||'</TD> 
                 <TD>'||rec.visitor||'</TD> 
                 <TD>'||rec.thecount||'</TD> 
             </TR>'), 
    END LOOP; 
    htp.p('</TABLE> 
       </BODY> 
       </HTML>'), 
EXCEPTION 
   WHEN OTHERS THEN 
     htp.p('Error in SHOW_INSTRUCTOR_VISITS: '||SQLERRM); 
END;