| 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.
){kind=link}
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 |