Using PL/SQL
The Oracle9iAS processes requests from users (Web browsers) and replies to these users with an HTML document. This document can be built either using a variety of methods, such as Java (see Chapter 10, “Using Java to Build Web Sites”), a CGI program, or using PL/SQL, as well as may other options.
The incoming URL has a virtual mapping to the PL/SQL cartridge in the Web server. Figure 8.9 in Chapter 8, “Building Web Sites with Oracle9i,” illustrates how the Web server knows to use the PL/SQL interface to process incoming requests from Web browsers. Listing 9.1 shows the simplest of PL/SQL Web sites, the classic “Hello World” message. Notice that this PL/SQL procedure is created in the normal manner with the CREATE OR REPLACE syntax. Two types of PL/SQL built-in packages handle the HTML code. The HTP package contains PL/SQL procedures that generate HTML output, whereas the HTF package contains PL/SQL functions that return HTML code as the return value. The HTP. syntax tells PL/SQL that this is an HTML command and is passed through to the HTML output. The htp.htmlOpen syntax tells PL/SQL that this is the beginning of the HTML document, and htp.htmlClose tells PL/SQL that this is the end of the HTML document. The same is true of htp.bodyOpen and htp.bodyClose. The htp.p command, on the other hand, is used to print or place items in the HTML document. In this example, htp.p is used to display the HelloWorld message. Figure 9.1 shows how to access this procedure through a Web browser, as well as display the output of the PL/SQL procedure. Notice the URL used to access the HelloWorld PL/SQL procedure.
Figure 9.1. HelloWorld PL/SQL procedure.
Listing 9.1. HelloWorld PL/SQL Procedure Syntax
CREATE OR REPLACE PROCEDURE helloworld
AS
BEGIN
htp.htmlOpen;
htp.bodyOpen;
htp.p('HelloWorld'),
htp.bodyClose;
htp.htmlClose;
END;
/
|
Notice that Listing 9.2 shows the actual HTML code generated by the HelloWorld PL/SQL procedure. Also notice how the htp.htmlOpen syntax in Listing 9.1 generated the <HTML> document tag in Listing 9.2.
Listing 9.2. HTML Output Created by the HelloWorld PL/SQL Procedure
<HTML> <BODY> HelloWorld </BODY> </HTML> |
Listing 9.3 shows how the htp.p command can be used to pass HTML tags to the HTML output. In Listing 9.1, htp.htmlOpen was used to generate the <HTML> tag, whereas Listing 9.3 illustrates how htp.p can be used to pass the text <HTML> to the HTML output.
Listing 9.3. HelloWorld PL/SQL Procedure Using Just htp.p
CREATE OR REPLACE PROCEDURE helloworld
AS
BEGIN
htp.p('<HTML>'),
htp.p('<BODY>'),
htp.p('HelloWorld'),
htp.p('</HTML>'),
htp.p('</BODY>'),
END;
/
|
The owa_util.showpage package can be used to display the HTML output in SQL*Plus. Figure 9.2 illustrates how to execute the HelloWorld procedure in SQL*Plus and then immediately use the owa_util.showpage package to display the HTML output generated by the HelloWorld procedure.
Displaying Pictures with PL/SQL and HTML
Creating simple PL/SQL procedures that display common things, such as banner information and default items, on a Web page is easy. This gives the Web page a consistent look and enables PL/SQL to be coded once and reused many times.
Listing 9.4 builds on Listing 9.3 by adding the code to create the banner Web page information, as illustrated by Figure 9.3. Notice the htp.tableOpen and the htp.tableRowOpen syntax in lines 9 and 10. Within the table row, also notice that two references to htp.tableData exist (lines 14 and 22). Each of these tableData references is creating a column in the HTML table. This gives the HTML output the capability to display text and images in a very specific and consistent position. The first column gets the metro_motors.jpg image file (lines 14 through 21). The htf.img function is used to return the location of the picture (in this case the Metro Motors jpeg file) stored in the virtual location ows-img. Refer to Figure 8.4 in Chapter 8 and notice that ows-img maps to the computer directory D: OAS4ows4.0adminimg. The metro_motors.jpg must be in this physical location for the htf.img function to find it, and the htf.img function generates an <IMG> tag that tells HTML where an image is stored in the virtual path. The second column receives the text Metro Motors Web Site (lines 22–25).
Figure 9.3. Metro Motors Web site banner page.
Listing 9.4. HelloWorld PL/SQL Procedure Displaying a Banner (helloworld2.sql)
1: CREATE OR REPLACE PROCEDURE helloworld
2: AS
3: BEGIN
4: htp.htmlopen;
5: htp.bodyOpen;
6: -- -------------------------------------------------
7: -- start of our heading / banner
8: -- -------------------------------------------------
9: htp.tableOpen;
10: htp.tableRowOpen
11: (
12: cvalign => 'CENTER'
13: );
14: htp.tableData
15: (
16: cvalue => htf.img
17: (
18: curl => '/ows-img/metro_motors.jpg'
19: , cattributes => 'WIDTH=100'
20: )
21: );
22: htp.tableData
23: (
24: cvalue => htf.big('Metro Motors Web Site')
25: );
26: htp.tableRowClose;
27: htp.tableClose;
28: -- -------------------------------------------------
29: -- End of our heading / banner
30: -- -------------------------------------------------
31: htp.p('HelloWorld'),
32: htp.bodyClose;
33: htp.htmlClose;
34: END;
35: /
|
NOTE
If your browser does not show the graphic, check your Web server virtual mappings to ensure that the jpg image file is in the directory for which the virtual directory is configured (illustrated in Figure 8.4 in Chapter 8).
Now, creating a procedure called display_banner and calling it whenever we create a new Web page is easy and enables the banner information to be coded once and then reused throughout the Web application. If a change to the banner information needs to be made, one procedure is changed and the change is then automatically reflected throughout the application upon its next Web browser access.
To do this, review the display_banner procedure in Listing 9.5, and you will see that it is very similar to the code in Listing 9.4. Notice that lines 7–25 in Listing 9.5 are very similar to lines 9–27 in Listing 9.4. The only real difference is at lines 3 and 22—the p_caption variable replaces the hard-coded Metro Motors banner text so this procedure can be used for any banner by simply inserting ?p_caption=<some text> on the URL line.
Listing 9.5. DISPLAY_BANNER PL/SQL Procedure (display_banner.sql)
1: CREATE OR REPLACE PROCEDURE display_banner 2: ( 3: p_caption IN VARCHAR2 DEFAULT 'Metro Motors Web Site' 4: ) 5: AS 6: BEGIN 7: htp.tableOpen; 8: htp.tableRowOpen 9: ( 10: cvalign => 'CENTER' 11: ); 12: htp.tableData 13: ( 14: cvalue => htf.img 15: ( 16: curl => '/ows-img/metro_motors.jpg' 17: , cattributes => 'WIDTH=100' 18: ) 19: ); 20: htp.tableData 21: ( 22: cvalue => htf.big(p_caption) 23: ); 24: htp.tableRowClose; 25: htp.tableClose; 26: END; 27: / |
Note that we added an input parameter p_caption to the procedure. This enables us to specify a caption other than just Metro Motors Web Site.
Now we can change our HelloWorld procedure to call the display_banner procedure, as shown in Listing 9.6.
Listing 9.6. HelloWorld PL/SQL Procedure Using display_banner Procedure (helloworld3.sql)
CREATE OR REPLACE PROCEDURE helloworld
AS
BEGIN
htp.htmlopen;
htp.bodyOpen;
-- -------------------------------------------------
-- start of our heading / banner
-- -------------------------------------------------
display_banner
(
p_caption => 'Metro Motors Web Site'
);
-- -------------------------------------------------
-- End of our heading / banner
-- -------------------------------------------------
htp.p('HelloWorld'),
htp.bodyClose;
htp.htmlClose;
END;
/
|
Both versions of this procedure, outlined in Listings 9.4 and 9.6, produce the same identical output as illustrated in Figure 9.3. If you were to look at the HTML output as illustrated in Figure 9.2, you would notice that the HTML output is also identical. The display_banner procedure creates the same HTML output, and it is now easy to use in other Web pages related to this application to give the identical visual attributes without having to add the code to each additional Web page.
Passing Parameters from PL/SQL to HTML
The final example in this section can accept a parameter from the URL and also accepts data from a field. Listing 9.7 illustrates the HelloWorld PL/SQL procedure with the additional code necessary to create a frame for data entry, as well as to process a parameter passed to the procedure. Lines 19–24 process any information in variable p_name. This value is populated if a parameter is passed from the URL (see Figure 9.4); otherwise, the generic message HelloWorld is displayed (see Figure 9.5).
Figure 9.4. Parameter missing Web error.
Figure 9.5. HelloWorld procedure prompting for input.
Lines 26–30 in Listing 9.7 create an HTML form tag. This form is further defined by lines 31–43, where a submit button is defined (lines 33–36) and an entry box is defined (lines 38–43).
The HTML form tag has two parameters. curl defines the procedure to call when the form is submitted, whereas ctarget is the Web page where the results of the procedure should be displayed. This field is intentionally set to blank so the results are displayed on the same Web page.
The submit button contains only one value, cvalue, which will contain any text to place inside the button.
The entry field has three parameters. cname is the variable name where the contents of the entry field will be placed after the submit button is clicked. csize is the maximum length of the entered value to be placed in cname, and cmaxlength is the total width of the entry box being displayed.
Listing 9.7. HelloWorld PL/SQL Procedure Accepting a Parameter/Prompting for a Parameter (helloworld4_num.sql)
1: CREATE OR REPLACE PROCEDURE helloworld
2: (
3: p_name IN VARCHAR2 DEFAULT NULL
4: )
5: AS
6: BEGIN
7: htp.htmlopen;
8: htp.bodyOpen;
9: -- -------------------------------------------------
10: -- start of our heading / banner
11: -- -------------------------------------------------
12: display_banner
13: (
14: p_caption => 'Metro Motors Web Site'
15: );
16: -- -------------------------------------------------
17: -- End of our heading / banner
18: -- -------------------------------------------------
19: IF p_name IS NULL THEN
20: htp.p('HelloWorld'),
21: ELSE
22: htp.p('Hello '||p_name||'. The time here is '
23: ||TO_CHAR(SYSDATE,'HH24:MI:SS'));
24: END IF; -- p_name IS NULL
25:
26: htp.formOpen
27: (
28: curl => 'helloworld'
29: , ctarget => ''
30: );
31: htp.hr;
32:
33: htp.formSubmit
34: (
35: cvalue => 'Say Hello to '
36: );
37:
38: htp.formText
39: (
40: cname => 'p_name'
41: , csize => 30
42: , cmaxlength => 20
43: );
44:
45: htp.formclose;
46: htp.bodyClose;
47: htp.htmlClose;
48: END;
49: /
|
Line 3 of Listing 9.7 identifies the p_name parameter, defines it as a variable length character field, and gives it the default value NULL. The reason for the default value is that this procedure might not have a parameter passed to it. If the parameter did not have a default value, we would be forced to always provide one on the URL line:
http://yourdomain.com/virtual_path_for_plsqlcart/helloworld?p_name=Dave
If this parameter was not passed, the missing parameter error would be returned to the Web browser, as illustrated by Figure 9.4.
Figure 9.5 shows the HelloWorld procedure with the just-added enterable field. Enter the value Dave in the text entry field and click the Say Hello to button. The Web page illustrated in Figure 9.6 will appear.
Figure 9.6. HelloWorld procedure displaying results.
