Previous Table of Contents Next


Once a PL/SQL developer understands how to display data in HTML, it is a relatively simple thing to integrate information from the database into a display. Listing 27.4 is an example of a PL/SQL procedure that queries the database and displays the results in an HTML file.

Listing 27.4. Pl/sql procedure with html and database data merged.

CREATE OR REPLACE PROCEDURE list_donors
(region_code_in VARCHAR2)
IS
  CURSOR c1 IS
    SELECT do.donor_id donor_id
         , do.lastname || ‘, ‘ || do.firstname full_name
         , do.state state
         , TO_CHAR(do.amount,’$999,999,999.99’) amount
      FROM donors do
     WHERE do.region_code = region_code_in;
   c1_var c1%ROWTYPE;
   --
   cookie_id_var INTEGER;
   --
 BEGIN
   --
   -- Most of my applications start with a call
   -- to a routine that checks the user’s cookie.
   -- This is a call to the check_cookie procedure
   -- in a package I created called util$.
   --
   util$.check_cookie(cookie_id_var);
   --
   -- If the cookie has a problem, it will be
   -- dealt with in the check_cookie procedure.
   -- If control reaches this point, then the
   -- user has been authenticated. In addition,
   -- if I need to know the user’s identity
   -- for this procedure, the cookie can help
   -- identify her.
   --
   htp.htmlOpen;
   htp.headOpen;
   htp.title(‘DEMO: Show a list of all donors’);
   htp.headClose;
   htp.bodyOpen;
   htp.header(‘1’,‘List of All Donors’,‘center’);
   htp.line;
   --
   htp.centerOpen;
   htp.tableOpen;
   --
   htp.tableRowOpen;
   htp.tableData(‘Donor Name’);
   htp.tableData(‘State’);
   htp.tableData(‘Amount’,‘right’);
   htp.tableRowClose;
   --
   OPEN c1;
   LOOP
     FETCH c1 INTO c1_var;
     EXIT WHEN c1%NOTFOUND;
     --
     htp.tableRowOpen;
     htp.tableData(htf.anchor2(detail_donor?donor_id_in=’ ||
                                  TO_CHAR(c1_var.donor_id),
                               c1_var.full_name));
     htp.tableData(c1_var.state);
     htp.tableData(c1_var.amount,‘right’);
     htp.tableRowClose;
     --
   END LOOP;
   CLOSE c1;
   --
   htp.tableClose;
   htp.centerClose;
   --
   htp.bodyClose;
   htp.htmlClose;
   --
 EXCEPTION
   WHEN OTHERS THEN
     --
     -- It’s a good idea to at least have a “WHEN OTHERS”
     -- exception handler. Otherwise, if something goes
     -- wrong, you’ll just get a general HTTP header
     -- response from the server, instead of the Oracle error
     -- message. To see the Oracle error message, you either
     -- have to look through the web servers logs, or
     -- create a procedure (ours is “show_error”) that
     -- captures the error message and code, and displays
     -- them on a web screen, in much the same fashion
     -- that this procedure writes to a web screen.
     --
     util$.show_error(‘list_donors’,SQLERRM,SQLCODE);
 END;
 /

Listing 27.4, when executed, will create a stored procedure called list_donors. This procedure, when stored in our database and accessed via a browser, displays as Figure 27.5.


Figure 27.5.  list_donors output browser.

By clicking View -> Page Source in the browser’s pull-down menu, we can display the HTML shown in Listing 27.5.

Listing 27.5. list_donors html source.

<HTML>
<HEAD>
<TITLE>DEMO: Show a list of all donors</TITLE>
</HEAD>
<BODY>
<H1 ALIGN=”center”>List of All Donors</H1>
<HR>
<CENTER>
<TABLE >
<TR>
<TD>Donor Name</TD>
<TD>State</TD>
<TD ALIGN=”right”>Amount</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=2”>JOHNSON, ALICE</A></TD>
<TD>VA</TD>
<TD ALIGN=”right”>     $200.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=4”>JONES, HAROLD</A></TD>
<TD>DE</TD>
<TD ALIGN=”right”>     $150.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=8”>PARKER, ALAN</A></TD>
<TD>MA</TD>
<TD ALIGN=”right”>     $170.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=7”>ROBERTS, JOANNA</A></TD>
<TD>CT</TD>
<TD ALIGN=”right”>     $180.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=6”>SIMPSON, JOHN</A></TD>
<TD>NJ</TD>
<TD ALIGN=”right”>     $80.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=1”>SMITH, JOE</A></TD>
<TD>MD</TD>
<TD ALIGN=”right”>     $100.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=3”>WATERS, JAMES</A></TD>
<TD>MD</TD>
<TD ALIGN=”right”>     $350.00</TD>
</TR>
<TR>
<TD><A HREF=”detail_donor?donor_id_in=5”>WILLIAMS, MELISSA</A></TD>
<TD>NY</TD>
<TD ALIGN=”right”>     $75.00</TD>
</TR>
</TABLE>
</CENTER>
</BODY>
</HTML>

Notice how we were able to create this page: by issuing fixed HTML print commands, using a loop to display the the rows of database data, and merging a portion of the HTML print commands with the FETCH to populate the variables that display in the HTML table.


Previous Table of Contents Next