CREATE OR REPLACE PACKAGE wow_pkg AS

    TYPE person_rec_type IS RECORD              -- person-info record
       (person_id       NUMBER(6),
        last_name       VARCHAR2(15),
        first_name      VARCHAR2(15),
        title           VARCHAR2(8),
        user_name       VARCHAR2(8),
        address         VARCHAR2(12),
        url             VARCHAR2(20));

    TYPE phone_rec_type IS RECORD               -- phone-list record
       (person_id       NUMBER(6),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    TYPE mix_rec_type IS RECORD
       (person_id       NUMBER(6),
        last_name       VARCHAR2(15),
        first_name      VARCHAR2(15),
        title           VARCHAR2(8),
        user_name       VARCHAR2(8),
        address         VARCHAR2(12),
        url             VARCHAR2(20),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    PROCEDURE get_person_phone_by_name(name IN VARCHAR2);

    PROCEDURE display_mix_rec(mix_rec IN mix_rec_type);

    PROCEDURE display_mix_head(ctitle IN VARCHAR2);

    PROCEDURE display_mix_end;
END wow_pkg;
/
CREATE OR REPLACE PACKAGE BODY wow_pkg AS




    PROCEDURE get_person_phone_by_name(name IN VARCHAR2)
    IS
        CURSOR mix_cur(name IN VARCHAR2)
        IS
            SELECT a.person_id, last_name, first_name, title,
                   user_name, address, url,
                   phone_no, phone_type
            FROM person_info_table a, phone_list_table b
            WHERE (name IS NULL OR 
                   last_name LIKE ('%' || LOWER(name) || '%') OR
                   first_name LIKE ('%' || LOWER(name) || '%')) AND
                  (a.person_id = b.person_id);

        mix_rec   mix_rec_type;

    BEGIN

        display_mix_head(name);

        IF NOT mix_cur%ISOPEN THEN          -- open the cursor if
            OPEN mix_cur(name);             -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH mix_cur INTO mix_rec;
            EXIT WHEN mix_cur%NOTFOUND;
            display_mix_rec(mix_rec);
        END LOOP;

        CLOSE mix_cur;

        display_mix_end;

    END get_person_phone_by_name;


    PROCEDURE display_mix_rec(mix_rec IN mix_rec_type)
    IS
        url_string      VARCHAR2(120);
    BEGIN
        htp.p('<TR>');
        htp.p('<TD>' || INITCAP(mix_rec.last_name) || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.first_name) || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.title) || '</TD>');
        htp.p('<TD>' || mix_rec.user_name || '</TD>');
        htp.p('<TD>' || UPPER(mix_rec.address) || '</TD>');
        url_string := '<a HREF=http://www.npac.syr.edu/users/' || 
                     mix_rec.user_name ||
                     '/homepage/ TARGET=wow_display>';
        htp.p('<TD>' || url_string || htf.italic(mix_rec.first_name) ||
                '</a>' || '</TD>');
        htp.p('<TD>' || mix_rec.phone_no || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.phone_type) || '</TD>');
        htp.p('</TR>');
    END display_mix_rec;

    PROCEDURE display_mix_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        htp.title('Query Results by Searching "' || ctitle || '"');
        htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        htp.p('<BR>');
        htp.p('<TABLE BORDER ALIGN="CENTER">');
        htp.p('<TR>');
        htp.p('<TD>Last Name</TD>');
        htp.p('<TD>First Name</TD>');
        htp.p('<TD>Title</TD>');
        htp.p('<TD>User Name</TD>');
        htp.p('<TD>Address</TD>');
        htp.p('<TD>URL</TD>');
        htp.p('<TD>Phone NO</TD>');
        htp.p('<TD>Phone Type</TD>');
        htp.p('</TR>');
    END display_mix_head;

    PROCEDURE display_mix_end
    IS
    BEGIN
        htp.p('</TABLE>');
    END display_mix_end;

END wow_pkg;
/