Using PL/SQL Pages
For developers familiar with PL/SQL, adding PL/SQL to existing HTML pages is possibly the quickest way to create great looking Web pages that are integrated with the database. You can use your in-house graphics department to create the look and feel of the Web pages required and then use PL/SQL to integrate the HTML Web pages with the Oracle9i database (creating PL/SQL Pages or PSP).
For example, standard HTML generators can create Web pages that look like page1.html (see Figure 11.3) and page2.html (see Figure 11.4). These are just base Web pages without any database interaction.
Figure 11.3. Basic Web page to be used to display autos.
Figure 11.4. Basic Web page to be used to buy autos.
The previous pages can be easily created with a dozen different HTML generator products and these Web pages are static. They will appear the same with each access.
PL/SQL pages are simply static html pages with some special PL/SQL tags (PSP), becoming dynamic Web pages. This marked up psp file is then loaded into the Oracle 9i database and the dynamic content is made available to the world.
Listing 11.3 shows the page1.html, the static Web page illustrated in Figure 11.3.
Listing 11.3. Page1.HTML Code
1: <html> 2: <head> 3: <title>Metro Motors</title> 4: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 5: </head> 6: <body bgcolor="#CCCCCC" text="#990000"> 7: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 8: <b><font face="Geneva, Arial, Helvetica, san-serif"> 9: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 10: <table width="100%" border="0"> 11: <tr> 12: <td rowspan="3" width="46%"> 13: <img src="/mmimage/photo1.gif" width="300" height="250"/></td> 14: <td width="54%" height="83"> 15: <font face="Geneva, Arial, Helvetica, san-serif"><b>Description:</b> 16: Auto Description Here</font></td> 17: </tr> 18: <tr> 19: <td width="54%" height="39"> 20: <font face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 21: $999,990.00</font></td> 22: </tr> 23: <tr> 24: <td align="center" width="54%"> 25: <a href="page1?id=0"> 26: <img src="/mmimage/prev.gif" width="100" height="30" border="0" alt="Previous"/> 27: </a> 28: <a href="page1?id=2"> 29: <img src="/mmimage/next.gif" width="100" height="30" border="0" alt="Next"/> 30: </a> 31: <a href="page2?id=1"> 32: <img src="/mmimage/buy.gif" width="100" height="30" 33: border="0" alt="Buy This Car"/> 34: </a> 35: </td> 36: </tr> 37: </table> 38: </body> 39: </html> |
Begin the transformation by renaming the Here is the original page1.html page to page1.psp. Listing 11.4 shows the PL/SQL Page.
Listing 11.4. Page1.psp Code
1: <%@ page language="PL/SQL"%> 2: <%@ plsql procedure="page1" %> 3: <%@ plsql parameter="id" type="NUMBER" default="1" %> 4: <%! l_id st_cars_for_sale.inv_id%TYPE :=id;%> 5: <%! l_sale_photo_location st_cars_for_sale.sale_photo_location%TYPE;%> 6: <%! l_auto_description st_cars_for_sale.auto_description%TYPE;%> 7: <%! l_amount VARCHAR2(15);%> 8: <% 9: get_car 10: ( 11: px_id => l_id 12: , x_description => l_auto_description 13: , x_image => l_sale_photo_location 14: , x_amount => l_amount 15: ); 16: %> 17: <html> 18: <head> 19: <title>Metro Motors</title> 20: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 21: </head> 22: 23: <body bgcolor="#CCCCCC" text="#990000"> 24: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 25: <b><font face="Geneva, Arial, Helvetica, san-serif"> 26: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 27: <table width="100%" border="0"> 28: <tr> 29: <td rowspan="3" width="46%"> 30: <img src="/mmimage<%=l_sale_photo_location%>" width="300" height="250"/></td> 31: <td width="54%" height="83"> 32: <font face="Geneva, Arial, Helvetica, san-serif"><b>Description:</b> 33: <%=l_auto_description%></font></td> 34: </tr> 35: <tr> 36: <td width="54%" height="39"> 37: <font face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 38: <%=l_amount%></font></td> 39: </tr> 40: <tr> 41: <td align=center width="54%"> 42: <a href="page1?id=<%=l_id-1%>"> 43: <img src="/mmimage/prev.gif" width="100" height="30" border="0" alt="Previous"/> 44: </a> 45: <a href="page1?id=<%=l_id+1%>"> 46: <img src="/mmimage/next.gif" width="100" height="30" border="0" alt="Next"/> 47: </a> 48: <a href="page2?id=<%=l_id%>"> 49: <img src="/mmimage/buy.gif" width="100" height="30" 50: border="0" alt="Buy This Car"/> 51: </a> 52: </td> 53: </tr> 54: </table> 55: <p> </p> 56: </body> 57: </html> |
The first 16 lines of Listing 11.4 are where all the dynamic action occurs. Line 1 identifies this page as a PL/SQL language file. Line 2 specifies what PL/SQL procedure to create for this page. Ultimately PSP pages get stored in the database as PL/SQL procedures, so this is the name that it will be stored as. If this line is omitted then the name of the file is used by default.
Line 3 indicates that this page will have an optional parameter passed to it. This page will work exactly like the example in Chapters 9 and 10 where the first time the Web page is called, there is not an inventory id passed to it, so the default value of 1 is used. The syntax default=“1” clause makes the parameter optional. Lines 4 through 7 are procedure-level variable declarations. Variables are declared here just as they are in PL/SQL and are enclosed in <%! %> tags.
Lines 8 through 16 are enclosed in a PL/SQL code block tag <% %>. This code is direct PL/SQL. Here we are calling a stored procedure called get_car (the concept was created in Chapters 9 and 10 but adapted to be a non-Web procedure, see Appendix C, “Web Sites and Product Codes,” for the code listing) that takes the inventory id parameter in and returns the information about this car back out into the local variables (l_%). This get_car (see Appendix B, “Complete Sales Tracking Installation Scripts,” for the procedure listing) simply opens up a cursor against the table object st_cars_for_sale table and finds an exact match if it can. If no car matches the inventory id passed, then it returns the next closest inventory id and related information. The id of the car selected and all the pertinent information is then returned.
This get_car logic could have been coded right in this page but the idea is to minimize mixing code with content, allowing simplicity in converting the Web pages and providing code sharing across the entire application.
The next line that changed is line 30. Here the hard-coded image of the car for sale is replaced with the results from the get_car procedure. The tag <%= %> is an output tag. Think of it as the left-hand side of a variable assignment statement. When you now see the syntax <%=l_sale_photo_ location%>, it will interpret this to the value of this location.
Lines 33 and 38 do the exact same thing for the description of the car and the suggested selling price (items from the get_car procedure again).
Lines 42, 45, and 48 show that more can be done than just displaying the contents of a variable. Math, string concatenation, and more (anything that can be the right-hand side of a PL/SQL assignment) can be inside a <%= %> tag combination. For example, the previous inventory id can be accessed with <%=l_id-1%> and the next inventory id with <%l_id+1%>. Line 48 is the link to making an offer. It calls page2 to handle accepting an offer, passing the inventory id as a parameter to it.
This PL/SQL page is now complete and it can now be loaded into the database. The command line tool loadpsp provides this function, see Figure 11.5.
Figure 11.5. Loading PL/SQL pages into Oracle.
The -replace option allows you to overwrite the existing procedure with the new one you are passing in. The tool allows you to pass more than one page at a time as well. This allows compile time to link together pages to make one page. This technique of code sharing would be useful in a full-blown development framework where a standard header or footer is always wanted to include with every page, at compile time. This header or footer file is placed inside the PSP page wherever the <%@ include file="includefile.inc" %> tag is used.
NOTE
If you later changed your includefile.inc you would have to re-load every page that uses it with the loadpsp program. To achieve runtime dynamic linking you would just use the <% %> PL/SQL code tags and execute a stored procedure that uses some of the available owa/htp procedures to include whatever content you like.
Listing 11.5 shows the Web page for page2.html, the make-an-offer page. Listing 11.6 is the modified page2.psp code.
Listing 11.5. Page2 HTML Code
1: <html> 2: <head> 3: <title>Metro Motors - Make An Offer</title> 4: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 5: </head> 6: 7: <body bgcolor="#CCCCCC" text="#990000"> 8: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 9: <b><font face="Geneva, Arial, Helvetica, san-serif"> 10: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 11: <form name="form2" method="post" action="page2"> 12: <input type="hidden" name="id" value="1"/> 13: <table width="100%" border="0"> 14: <tr> 15: <td rowspan="4" width="46%"><a href="page2.html"> 16: <img src="/mmimage/photo1.gif" width="300" height="250" 17: border="0" alt="Image Not Available"/> 18: </a> 19: </td> 20: <td width="54%" height="83"><font face="Geneva, Arial, 21: Helvetica, san-serif"> 22: <b>Description:</b> 23: Auto Description Here</font></td> 24: </tr> 25: <tr> 26: <td width="54%" height="39"><font 27: face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 28: $999,990.00</font></td> 29: </tr> 30: <tr> 31: <td> 32: <p align="CENTER"><b><font 33: face="Geneva, Arial, Helvetica, san-serif" COLOR="Fuchsia"> 34: <font size="3"> 35: RESULT GOES HERE</font></font></b> 36: </p> 37: <table width="100%" border="0"> 38: <tr> 39: <td width="33%"> 40: <div align="right"> 41: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 42: Name:</b></font></div> 43: </td> 44: <td width="67%"> 45: <input type="text" name="buyer" 46: maxlength="50" value="BUYER"/> 47: </td> 48: </tr> 49: <tr> 50: <td width="33%"> 51: <div align="right"> 52: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 53: Phone:</b></font></div> 54: </td> 55: <td width="67%"> 56: <input type="text" name="phone" 57: maxlength="15" value="PHONE"/> 58: </td> 59: </tr> 60: <tr> 61: <td width="33%"> 62: <div align="right"> 63: <font face="Geneva, Arial, Helvetica, san-serif"> 64: <b>Offer:</b></font></div> 65: </td> 66: <td width="67%"> 67: <input type="text" name="offer" 68: maxlength="15" value="OFFER"/> 69: </td> 70: </tr> 71: </table> 72: </td> 73: </tr> 74: <tr> 75: <td align="center" width="54%"> 76: <input type="image" border="0" 77: src="/mmimage/offer.gif" width="100" height="30" alt="Place Offer" /> 78: <a href="page1?id=1"> 79: <img src="/mmimage/browse.gif" width="100" 80: height="30" border="0" alt="Browse Cars"/></a> 81: </td> 82: </tr> 83: </table> 84: </form> 85: <p> </p> 86: </body> 87: </html> |
Listing 11.6 shows the page2 as a PL/SQL PSP.
Listing 11.6. Page2.PSP Code
1: <%@ page language="PL/SQL"%> 2: <%@ plsql procedure="page2" %> 3: <%@ plsql parameter="id" %> 4: <%@ plsql parameter="buyer" default="NULL"%> 5: <%@ plsql parameter="phone" default="NULL"%> 6: <%@ plsql parameter="offer" default="NULL"%> 7: <%@ plsql parameter="x" default="NULL"%> 8: <%@ plsql parameter="y" default="NULL"%> 9: <%! l_id st_cars_for_sale.inv_id%TYPE:=id;%> 10: <%! l_sale_photo_location st_cars_for_sale.sale_photo_location%TYPE;%> 11: <%! l_auto_description st_cars_for_sale.auto_description%TYPE;%> 12: <%! l_amount VARCHAR2(15);%> 13: <%! l_buyer st_cars_for_sale.buyer_name%TYPE := buyer;%> 14: <%! l_phone st_cars_for_sale.buyer_phone%TYPE := phone;%> 15: <%! l_offer VARCHAR2(50) := offer;%> 16: <%! l_success BOOLEAN;%> 17: <%! l_result VARCHAR2(1000);%> 18: <% 19: get_car 20: ( 21: px_id => l_id 22: , x_description => l_auto_description 23: , x_image => l_sale_photo_location 24: , x_amount => l_amount 25: ); 26: IF l_offer IS NOT NULL THEN 27: make_offer 28: ( 29: p_id => l_id 30: , p_buyer => l_buyer 31: , p_phone => l_phone 32: , p_offer => l_offer 33: , x_success => l_success 34: , x_result => l_result 35: ); 36: IF l_success THEN 37: l_buyer := NULL; 38: l_phone := NULL; 39: l_offer := NULL; 40: END IF; 41: END IF; 42: %> 43: <html> 44: <head> 45: <title>Metro Motors - Make An Offer</title> 46: <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/> 47: </head> 48: 49: <body bgcolor="#CCCCCC" text="#990000"> 50: <p><img src="/mmimage/logo.gif" width="150" height="100" align="middle"/> 51: <b><font face="Geneva, Arial, Helvetica, san-serif"> 52: <font size="4">Metro Motors Web Site 265-4774</font></font></b></p> 53: <form name="form2" method="post" action="page2"> 54: <input type="hidden" name="id" value="<%=l_id%>"> 55: <table width="100%" border="0"> 56: <tr> 57: <td rowspan="4" width="46%"><a href="page2.html"> 58: <img src="/mmimage<%=l_sale_photo_location%>" width="300" height="250" 59: border="0" alt="Image Not Available"/> 60: </a> 61: </td> 62: <td width="54%" height="83"><font face="Geneva, Arial, 63: Helvetica, san-serif"> 64: <b>Description:</b> 65: <%=l_auto_description%></font></td> 66: </tr> 67: <tr> 68: <td width="54%" height="39"><font 69: face="Geneva, Arial, Helvetica, san-serif"><b>Suggested Price:</b> 70: <%=l_amount%></font></td> 71: </tr> 72: <tr> 73: <td> 74 <p align="CENTER"><b><font 75: face="Geneva, Arial, Helvetica, san-serif" COLOR="Fuchsia"> 76: <font size="3"> 77: <%=l_result%></font></font></b> 78: </p> 79: <table width="100%" border="0"> 80: <tr> 81: <td width="33%"> 82: <div align="right"> 83: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 84: Name:</b></font></div> 85: </td> 86: <td width="67%"> 87: <input type="text" name="buyer" 88: maxlength="50" value="<%=l_buyer%>"/> 89: </td> 90: </tr> 91: <tr> 92: <td width="33%"> 93: <div align="right"> 94: <font face="Geneva, Arial, Helvetica, san-serif"><b>Buyer 95: Phone:</b></font></div> 96: </td> 97: <td width="67%"> 98: <input type="text" name="phone" 99: maxlength="15" value="<%=l_phone%>"/> 100: </td> 101: </tr> 102: <tr> 103: <td width="33%"> 104: <div align="right"> 105: <font face="Geneva, Arial, Helvetica, san-serif"> 106: <b>Offer:</b></font></div> 107: </td> 108: <td width="67%"> 109: <input type="text" name="offer" 110: maxlength="15" value="<%=l_offer%>"/> 111: </td> 112: </tr> 113: </table> 114: </td> 115: </tr> 116: <tr> 117: <td align=center width="54%"> 118: <input type="image" border="0" 119: src="/mmimage/offer.gif" width="100" height="30" alt="Place Offer" /> 120: <a href="page1?id=<%=l_id%>"> 121: <img src="/mmimage/browse.gif" width="100" 122: height="30" border="0" alt="Browse Cars"/></a> 123: </td> 124: </tr> 125: </table> 126: </form> 127: <p> </p> 128: </body> 129: </html> |
Lines 1–2 indicate that the page is a PL/SQL page and that the procedure is to be named page2. Because this file is named page2.psp, declaring that the page is to be called page2 is redundant but a good practice anyway. Lines 3–8 are the parameters to the procedure. They are the HTML variables that will be passed from the HTML Web page. Notice that the id parameter (line 3) is NOT declared with a default value. This means that whenever this page is requested it must be requested with a parameter, in this case an inventory id. If the inventory id parameter is not passed then the default error page will be displayed.
TIP
The default error page can be overridden by declaring the name of a PSP to call in the event of an error as the following: <%@ page language="PL/SQL" errorPage= "customerror.psp"%>.
The buyer, phone, and offer parameters are input fields on the form to fill in the name of the buyer, their contact phone, and the amount of the offer. At first glance, parameters 7 and 8 don't seem to make much sense. Lines 117–123 show that an image is being used as the submit button. When using an image as a submission button Oracle defines the image as an image map and so the coordinates of the mouse inside the image are passed to the form. Because this image is simply a label, the mouse coordinates are irrelevant, so they can be ignored, but they are necessary to define as variables so the form will process correctly when the button is pressed.
Lines 9–17 define the local variables that are used in the program. Some of the variables are defined as static types for example, VARCHAR2(15) and other types based off the column definitions. Basing your variables on database columns improves readability and avoids database runtime errors due to column sizes changing. Notice that some of the variables (id, buyer, phone, offer) are initialized with the value passed in from the parameter. Local variables are defined within the <%! %> tag.
TIP
The <% %> tags enclose executable blocks of code.
Line 19 is the same call, get_car, that was used in page1 line 9. Line 26 checks to see if there is an offer amount or not. If there is an offer then the make_offer procedure will be called (see Appendix C for the make_offer procedure listing). The make_offer procedure returns a boolean variable into l_success and a resulting message in l_result (positive or negative). Line 36 uses the l_success boolean variable. If it was successful then the local l_buyer, l_phone, and l_offer variables are cleared so that they do not show up on the screen (lines 36 through 40).
Line 53 sets up an HTML form and declares that the parameters are passed to the page as post parameters instead of get parameters. So far, only get parameters have been used. When the user clicks on our submit button (the place offer button) all input values are posted to page2 for processing.
TIP
Line 54 sets up a hidden input field. The inventory id is not to be displayed but is important to the processing of the information. It can easily be hidden in this manner. The value of this field is set by using the <%= %> java server pages tag.
Lines 58, 65, and 70 show the same features of page1. The hard-coded values were replaced with dynamic results from the <%= %> tags. These are the same values that give the information about the car for sale.
Line 77 echoes out the l_result variable. If an offer was attempted then some status message will be displayed. If there was no offer attempted (in other words, no value passed into the l_offer parameter) then no message is displayed. Lines 86 through 89 create an input box for the name of the buyer. Line 88 sets the default value of the text field. Lines 97 through 100 set up an input box for the buyer's phone number. The default value for the phone number is set with the <%=l_phone%> tag. Lines 108 through 111 create the text box to accept the buyers offer amount. The default value is set in the <%= %> tag.
Lines 118 and 119 create a submit button. For the submit button, an image is used. This image is actually turned into an image map and when the image is clicked, the click is translated into an x, y coordinate that is then passed to the form as the x and y parameters. Line 120 sets up a link back to the car browsing page (page1).
Use the same loadpsp command (used with page1) to load the page into the database. Tools like TOAD (see Figure 11.6-using TOAD is covered in Chapter 16, “Using TOAD in the Development Arena”) can then be used to view and make additional changes to the page.
Figure 11.6. Using TOAD with PL/SQL pages.
Our DAD setup is now complete. We are ready to browse our page.
You can now access the new procedure with a URL something like this: http:// localhost/pls/simpledad/page1. You should see the Web page, with database access as in Figure 11.7. If you received an error or you are encountering a configuration error, check with the section earlier in this chapter about setting up the Apache Listener and Database Access Descriptors.
Figure 11.7. Buy This Car—procedure page1.
Because this procedure is now inside of the Oracle database, the .psp extension is not needed—it is not part of the procedure name inside the database. On the URL line, the /pls/ tells the Apache Web server to use the modplsql module to process this request. The /simpledad/ indicates which DAD to use, and the last part is the procedure being requested.
The next and previous buttons walk the user through the car inventory loaded in the ST_CARS_FOR_SALE table. The “Buy This Car” button accesses the page2 procedure, see Figure 11.8. Notice in the URL that the procedure page2 is being accessed and being passed the id of 2. As in Chapters 9 and 10, when an offer is made, it updates the ST_CARS_FOR_SALE table, see Figure 11.9.
Figure 11.8. Buy This Car—procedure page2.
Figure 11.9. Verify update with SQL*Plus.
If you were to view the source (in Web browser View, Source), you would not see any of the PSP tags—the HTML looks like any other static page.
PL/SQL pages are simple but powerful. This chapter covered a simple but effective example. PSP made it easy for an Oracle developer to quickly get Oracle information out to the Internet.
TIP
PSP pages have the ability to pull code in at compile time from external files. This can be accomplished with the <% include file="..."%> tag. Developers can also have dynamic script inclusion by calling procedures, packages, and functions from within the PSP.
The Oracle corporation has also provided a comprehensive set of utilities that can be found in the OWA and UTL packages (see the PL/SQL Web Toolkit). These procedures and functions allow PL/SQL developers to send mail, dump tables, manage cookies, and perform other powerful Web functions via PL/SQL. The learning curve is extremely short and because it is all running inside an Oracle database the solution can scale well.