17.5 How do I…Use HTML tables in my web documents?

Problem

I want to use HTML tables to display information that should appear in row/column format. When I use a browser capable of displaying tables, I notice they greatly enhance the look of web documents. How do I use HTML tables in my page?

Technique

Tables are created within an HTML document by generating table-related tags in the document. The <TABLE> tag begins a table and can be generated by the HTP.TABLEOPEN procedure. Table 17.6 shows the PL/SQL Web Toolkit procedures related to the creation of tables.

Table 17.6 Table-related procedures

Procedure Description
htp.tableopen Begins the creation of a table
htp.tableclose Ends the table specification
htp.tablecaption Creates a caption for the table
htp.tablerowopen Starts the processing of row information
htp.tablerowclose Ends the processing of row information
htp.tableheader Creates column headers for the table
htp.tabledata Creates a column data element
owa_util.cellsprint Prints an HTML table from the output of a query
owa_util.tablePrint Prints an Oracle table as a preformatted or an HTML table

Each row of the table is created beginning with the HTP.TABLEROWOPEN procedure, creating elements with the HTP.TABLEDATA procedure, and ending with the HTP.TABLEROWCLOSE procedure. A caption can be created for the table by using the HTP.TABLECAPTION procedure. Column headings can be created just like a row, or the HTP.TABLEHEADER procedure can be used. If the HTP.TABLEHEADER procedure is used, the column headings look slightly bolder.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP17_10.SQL, shown in Figure 17.24, creates the sample table used in this How-To.

The SALES17 table contains regional sales information for a sample company. Four sample records to be queried into an HTML table in a later step are created. Run the file to create the sample table and its data.

SQL> START CHP17_10

Table created.

1 row created.

1 row created.

1 row created.

1 row created

2. Run the CHP17_11.SQL file in SQL*Plus, as shown in Figure 17.25. The procedure contained in the file queries the sample table created in Step 1 and creates an HTML table using the records fetched.

Line 1 specifies the keywords required to create the stored procedure and name it. Lines 2 through 5 create a cursor that queries the sample table created in Step 1. The cursor is executed within the procedure body and the rows displayed in a table.

Lines 6 through 26 contain the procedure body. Lines 7 and 25 use the HTP.HTMLOPEN and HTP.HTMLCLOSE procedures to begin and end the document, respectively. Line 8 begins the creation of the table using the HTP.TABLEOPEN procedure. The HTP.TABLECAPTION procedure is used to generate HTML tags for the table caption.

Lines 10 through 15 call the HTP.TABLEHEADER procedure to create a row containing column headings. Lines 16 through 23 use a FOR loop to print each record returned by the query. A row is created for each record returned by the query by using the HTP.TABLEROWOPEN, HTP.TABLEDATA, and HTP.TABLEROWCLOSE procedures. The table is closed in line 24 with the HTP.TABLECLOSE procedure.

3. Open the http://localhost/owa_waite/plsql/sales_tbl17 URL in your browser. The URL invokes the stored procedure and the query is executed on the sample table to dynamically create an HTML table. The table is displayed in Figure 17.26 within Netscape Navigator.

When the table is generated in the document, it contains five rows and four columns. The first row contains the column headings, and the next four rows display data from the sample table.

4. Run the CHP17_12.SQL file in SQL*Plus, as shown in Figure 17.27. A much easier way to generate HTML tables from Oracle tables is by using the OWA_UTIL.TABLEPRINT function.

The OWA_UTIL.TABLEPRINT function returns a Boolean value TRUE or FALSE as to whether there are more rows available beyond the maximum rows requested, which is NULL by default if omitted in the call. As it is a function call, the IGNORE Boolean variable in line 2 simply serves as a placeholder for the value returned. HTML tags for the complete table are generated in a single call to the powerful OWA_UTIL.TABLEPRINT function as in lines 5 and 6. The only difference is the third parameter that specifies whether the table has to be generated as an HTML table or a pre-formatted table. By default, an HTML table is generated. Note that the first parameter is the name of the source Oracle table.

5. Open the URL http://localhost/owa_waite/plsql/sales_easy17 in your browser. The URL invokes the stored procedure created in the previous step. The two tables are displayed within Netscape Navigator as shown in Figure 17.28.

6. Run the CHP17_13.SQL file in SQL*Plus, as shown in Figure 17.29. A much easier way to paginate cells with column values from an Oracle view by using the OWA_UTIL.CELLSPRINT function.

The OWA_UTIL.CELLSPRINT procedure generates an HTML table using the output of a SQL query on a data dictionary view as in lines 7 to 9. Each column value from a record fetched are mapped to cells of an HTML table. In lines 6 and 10, the HTML code for opening and closing the HTML table is specified explicitly. The first parameter to the procedure is the query, the second one is the maximum rows to display in a table, the third parameter specifies whether or not to format numbers, the fourth parameter specifies how many records to skip before fetching records to display in the table, and the last parameter is an out parameter indicating if there are more rows remaining in the table to be displayed. The last two parameters enable you to scroll through the result set. This is achieved by saving the last row seen in a hidden form field as in lines 13 and 17. Lines 14 and 20 create the corresponding buttons to scroll through the previous and next set of records

7. Open the http://localhost/owa_waite/plsql/cells17 URL in your browser, which invokes the stored procedure created in the previous step. The scrolling operation is displayed within Netscape Navigator as shown in Figure 17.30.

How It Works

HTML tables are created by the <TABLE> tag and terminated by the </TABLE> tag. Between the two tags, other tags create the format of the table. In order to put a border around the table and between the columns and rows, the BORDER attribute is supplied with the <TABLE> tag. The HTP.TABLEOPEN procedure generates the <TABLE> tag, and the HTP.TABLECLOSE procedure generates the </TABLE> tag. Within a table, the <TR> and </TR> tags enclose the rows contained in the table. They can be generated with the HTP.TABLEROWOPEN and HTP.TABLEROWCLOSE procedures. Table data is contained within the <TD> and </TD> tags. The HTP.TABLEDATA procedure generates both these tags containing a string. The <CAPTION> and </CAPTION> tags contain a caption for the table. These tags can be generated by the HTP.TABLECAPTION procedure. The OWA_UTIL.TABLEPRINT function provides a neat way of generating tags for HTML tables and pre-formatted tables. The OWA_UTIL.CELLSPRINT procedure works beautifully when you need to scroll through the result set of a query.

Step 1 creates a sample table, which is the source of data in a later step. Step 2 creates an HTML table that receives its data from a database query. The sample table created in Step 1 is queried by a cursor in the stored procedure. When the query is executed, each row of it is created as a row in the HTML table. Step 3 displays the table in a browser. Step 4 demonstrates the power of the OWA_UTIL.TABLEPRINT function to generate HTML tables. Step 5 displays the generated HTML and pre-formatted tables in a browser. Steps 6 and 7 demonstrate the use of the OWA_UTIL.CELLSPRINT procedure.

Comments

HTML tables are an appealing way to display row and column information. In your applications, you may find yourself often displaying the results of queries using HTML tables. HTML tables are a good method for presenting data from queries, because a query contains rows and columns.