17.2 How do I…Return the results of a query to a web document?
Problem
I want to use Oracle web technology to display query results to users. By displaying the results of database queries within web documents, I can provide an easy way to generate reports and analysis information to our users. Many of the people who want this type of information do not need large applications and already have a web browser. How do I return the results of a query to a web page?
Technique
A web document can be generated dynamically by stored procedures you create. The results of functions, PL/SQL operations, and queries can be presented within web documents. The stored procedure is invoked through the PL/SQL Agent. The PL/SQL Agent executes the corresponding stored procedure and returns the resulting HTML to the browser. In the stored procedure, the results of the query executed is formatted into HTML by using functions and procedures in the HTP package to display the web document in the browser. The HTP package contains a set of procedures that print text and HTML tags to the browser. The HTF package contains functions that correspond to each of the HTP procedures. The functions return VARCHAR2 results that can be used within the PL/SQL code. The PL/SQL Web Toolkit makes it easy to display results of queries within web documents.
Steps
1. Run SQL*Plus and connect as the WAITE account. CHP17_1.SQL, shown in Figure 17.6, creates the sample table used in this How-To.
Run the file to create the sample table and data.
SQL> START CHP17_1
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
2. Run the CHP17_2.SQL file in SQL*Plus, as shown in Figure 17.7. The procedure contained in the file queries the DEPT17 table created in Step 1 and displays the results of the query using the PL/SQL Web Toolkit packages.
Line 1 presents the keywords required to create a stored procedure and names the procedure. The cursor declared in lines 2 through 7 declares a cursor that is executed within the procedure body, which lines 8 through 21 contain. The HTP.HTMLOPEN procedure in line 9 and the HTP.HTMLCLOSE procedure in line 20 begin and end the document, respectively. Line 10 uses the HTP.HTITLE procedure to create the title tags, <TITLE> and </TITLE>, and heading tags, <H1> and </H1>, establishing a title and a heading for the document. The parameter passed to the procedure is used as both the title and the heading. Table 17.1 contains the HTP package procedures in the PL/SQL Web Toolkit used for formatting heading and structure related tags. <p>Table 17.1 Heading and structure related procedures
Procedure Description htp.htmlOpen Prints the <HTML> tag indicating the beginning of document. htp.htmlClose Prints the </HTML> tag indicating the end of document. htp.headOpen Prints the <HEAD> tag indicating the beginning of document head. htp.headClose Prints the </HEAD> tag indicating the end of document head. htp.bodyOpen Prints the <BODY> tag indicating the beginning of document body. htp.bodyClose Prints the </BODY> tag indicating the end of document body. htp.title Prints the title specified with the <TITLE> and </TITLE> tags. htp.htitle Creates both title tags and heading tags. htp.base Prints a tag that records the URL of the current document. htp.meta Prints a tag that embeds document meta-information. Lines 14 and 17 begin and end an unordered list by using the HTP.ULISTOPEN and HTP.ULISTCLOSE procedures. Table 17.2 shows the procedures related to lists in the HTP package.
Procedure Description htp.listHeader Creates a header for any type of list. htp.listItem Generates a list item for an ordered or unordered list. htp.ulistOpen Opens an unordered list. htp.ulistClose Closes an unordered list. htp.olistOpen Opens an ordered list. htp.olistClose Closes an ordered list. htp.dlistOpen Opens a definition list. htp.dlistClose Closes a definition list. htp.dlistDef Creates a definition in a definition list. htp.dlistTerm Creates a term in a definition list. htp.menulistOpen Creates a list more compact than an unordered list. htp.menulistClose Closes the menu list. htp.dirlistOpen Creates a directory list. htp.dirlistClose Closes a directory list. Rows fetched from the cursor are formatted and displayed within the document by the FOR loop in lines 15 through 18. Column values of each record returned by the query are printed to the document using the HTP.PRINT procedure. The print procedures in the HTP package needed to generate dynamic web content are listed in Table 17.3.
Table 17.3 Print procedures
The HTP.PRINTS procedure must be used if data may contain the four special characters [<], [>], [&], and [“], which have a special meaning when HTML is interpreted. Thus, text containing these characters has to be displayed as entity references as shown in Table 17.4. Another function to filter data for special characters is called htf.escape_sc, which is similar to the htp.prints procedure. Another important HTML entity reference is the non-breaking space represented by   used to align empty table cells.
Procedure Description htp.print Prints a text string to the browser. htp.p Same as HTP.PRINT. htp.prn Same as HTP.PRINT but does not put a new line at the end of the string. htp.prints Prints a text string to the browser and replaces all occurrences of <, >, “, and &. Characters with the corresponding entity are referenced so they can be displayed literally in the browser. htp.ps Same as HTP.PRINTS. Table 17.4 HTML text escaping
Character Entity Reference < <
< >
& &
“ "
3. In your browser, open the URL http://localhost/owa_waite/plsql/ dept_lst17 to display the document created in the previous step. Figure 17.8 shows the page in the Netscape Navigator.
The three records contained in the sample table created in Step 1 are displayed in the document. The header created by the HTP.HTITLE procedure is displayed as a top-level heading.
4. Select View, Page Source from the Navigator menu to view the source of the document. This demonstrates how the Oracle procedures are translated to HTML tags and static text. Figure 17.9 shows the source created for the document.
The source code of the document is standard HTML code, as if it was read from a static file.
How It Works
A cursor within PL/SQL is used to execute a query and return its results to the PL/SQL code. The results of a query can be displayed within a web document by processing a cursor and displaying its contents using the HTP.PRINT procedure or other HTP procedures. Step 1 creates a sample table with data queried in the web document. Step 2 develops a stored procedure that creates and processes a cursor to display the results of a query within a web document. Step 3 executes the procedure from within a browser to display the query results. Step 4 presents the HTML code generated from the results of the query.
Comments
The technique presented in this How-To is critical to developing business applications that use the Oracle database. Because web documents are created in stored procedures, you should refer to Chapter 10, “PL/SQL,” for more infor-mation on creating stored procedures.