Release 3.00
Jan 18, 2004
|
Sybernet / Supplied Procedures Reference
Release 3.00 Jan 18, 2004 |
|
This procedure allows you to create an HTML table in real-time. HTML tables are easy to create, but this procedure will do it for you automatically by simply passing your SELECT statement to this procedure. Because your select statement needs to be parsed, it is more efficient for you to build your own table or you can cheat and set DEBUG to TRUE and copy the anonymous block to your code.
Ideally, you should alias each column name so that the table heading looks like you want it to. If your column list contains expressions, then an alias is not only desired, but also required. You may specify * in your column list, but then you must be satisfied with the colum name as the default heading.
Most of the obvious options may be passed to this procedure. This includes the table alignment and back-ground color. If you need more control over how the table looks (like specifying the font color of your cells), then you will want to use Cascading Style Sheets. If column data should not span multiple lines, then you can format the column yourself using the replace funtion.
Procedure SP_HTML_TABLE
(
VALUE VARCHAR2
, TITLE VARCHAR2
, WIDTH VARCHAR2
, BORDER VARCHAR2
, BORDERCOLOR VARCHAR2
, CELLPADDING VARCHAR2
, CELLSPACING VARCHAR2
, BGCOLOR VARCHAR2
, ALIGN VARCHAR2
, NLS_DATE_FORMAT VARCHAR2
, THBGCOLOR VARCHAR2
, TDBGCOLOR VARCHAR2
, VALIGN VARCHAR2
, LPP NUMBER
, LPS NUMBER
, CAPTION VARCHAR2
, SEPARATOR VARCHAR2
, CONTENTTYPE VARCHAR2
, CONTEXTTYPE VARCHAR2
, NEXT VARCHAR2
, PREV VARCHAR2
, DEBUG PL/SQL BOOLEAN
, DEFER_PROCESSING NUMBER
, SELF_REFERENCE NUMBER
, SELECTED_INDEX NUMBER
, URI VARCHAR2
) ;
| Parameter | Description |
|---|---|
value |
This is the SELECT statement that is passed to Oracle and evaluated by this procedure. |
title |
Specifies the title of this page (see self_reference).
|
width |
Specifies the table's width. |
border |
Specifies the table's border size. |
bordercolor |
Specifies the table's border color. |
cellpadding |
Specifies the cell padding. |
cellspacing |
Specifies the cell spacing. |
bgcolor |
Specifies the background color of your table. |
align |
Specifies how the table should be aligned. |
nls_date_format |
Specifies how date columns are formatted in the table. This option has no affect on dates that are to be calculated. You can format calculated dates yourself in the column list. |
thbgcolor |
Specifies the background color of your table's heading. |
tdbgcolor |
Specifies the background color of your table's data. |
valign |
Specifies the vertical alignment of each table data. |
lpp |
Specifies the number of lines per page. To create one large table lpp should be -1 or unspecified. Do not pass NULL. |
lps |
Specifies the number of lines per screen. Use this if you wish to control
the number of rows displayed on a screen at one time. Specifying a
non-negative value causes this procedure to include both a Prev and Next
link that allows your user to scroll backward and forward through this table.
If LPS is less than LPP, then LPS takes precedence over how the screen is actually rendered. If LPS is greater than LPP, then you may end up with 2 or more tables per screen. Ideally, LPS should equal LPP when both are desired. |
caption |
Specifies a caption for each table. |
separator |
Specifies the string that separates each table. |
debug |
When true, the anonymous block created by this procedure is displayed instead of evaluated. |
contenttype |
Contenttype may be one of text/html, text/plain, or application/excel. Contenttype determines how your
table data is formatted. You can format your output as HTML, plain text, or tab delimited data suitable
for Excel.
When specifying contenttype application/excel, caption, separator, lpp, and lps are ignored. |
contexttype |
Contexttype may be one of HTML, PLAIN, or EXECL. In conjuction with contenttype this option determines how your table data is formatted. Either or both may override the other. If you are using SP_HTML_FILTER, then the contexttype value passed by that may be passed to this procedures. |
next |
Specifies the text or image that is displayed that allows your user to scroll the current page forward by one page. This and prev apply only when LPS is non-zero. The default is an image. |
prev |
Specifies the text or image that is displayed that allows your user to scroll the current page backwards by one page. This and next apply only when LPS is non-zero. The default is an image. |
defer_processing |
This option determines how the Next and Prev buttons are built. The default (2) disables the Prev button
when you are on the first page and disables the Next button when you are on the last page.
Here is the problem. When you are on page one, what do you want the Prev button to do? It can be disabled (the default) or point to the last page in your select; this is what happens when defer_processing is passed as 0. To do this and to display the actual page number when your user does a mouseover, sp_html_table must calculate the last page number. If you result set is large, this might take a while. When defer_processing is 1, your user still has the ability to click the Prev button from page one, but the mouseover describes this as Page 0. Only when they click this link is the page actually calculated and displayed. When defer_processing is 0 or 1, the Next button points to Page 1 when the last page is displayed. In an ideal world, defer_processing would always be 0 because your result set is always small. In determining what this value should be ask yourself this question. If I am specifying LPS to be non-zero because I want the user to scroll forward and backward one page at a time, do I really expect them to scroll through more than 10 or 20 pages? If I do not, then my select should not return any more than that. On the other hand, if I'm being lazy and just selected all rows from a very large table, then the default (2) is the desired value. |
self_reference |
While used internally, this option determines if sp_html_table was invoked externally or internally.
When invoked externally by your application, self_reference is 0 and means you've already created the
HTML headers and trailers (whether you have or not).
When invoked internally by sp_html_table, self_reference is 1 and means sp_html_table needs to create
the HTML headers (including your title) and trailers. sp_html_table uses http.sp_html_preferences to build the body tag and
so should you.
|
selected_index |
Used internally so that sp_html_table knows the next zero-relative row number to display. |
uri |
A URI that returns the user to your menu screen. If omitted, your procedure is called with BUTTON set to CENTER. If not omitted, this URI should countain a fully qualified procedure name followed by any parameters required to resume your procedure. Parameter values should be URLEncoded. The example below illustrates how to create this URI, but since this is invoked from an anonymous block, it is completely bogus. |
The following example illustrates how to call sp_html_table:
HTTP.SP_HTML_TABLE
(
value => 'SELECT * FROM HTTP.HTTP_COLORS'
) ;
The following example illustrates how to call sp_html_table:
HTTP.SP_HTML_TABLE
(
value => 'SELECT NAME "Name", ( ''<font color='' || COLOR || ''>'' || COLOR || ''</font>'' ) "Color" FROM HTTP.HTTP_COLORS'
, width => '50%'
, border => '2'
, bordercolor => 'black'
, cellpadding => '0'
, cellspacing => '0'
, bgcolor => 'white'
, align => 'center'
, nls_date_format => 'MM/DD/YY'
, THbgColor => 'lightgrey'
, TDbgColor => 'white'
, lpp => 10
, lps => 10
, caption => '<h1>HTTP.HTTP_COLORS</h1>'
, separator => '<br><hr size=1 width="100%"><br>'
, valign => 'top'
, debug => false
) ;
The following example illustrates how to create a hyper-text link that calls sp_html_table. To simplify this illustration the value and caption parameters are separate functions. caption() returns our caption, a hidden form screen that will invoke sp_html_table, and a javascript function that calls this form. value() returns our select statement with an invocation of our javascript function for each row_id in the table:
declare
sort varchar(30) := 'TITLE';
/******************************************************************************
**
** uri()
**
*******************************************************************************/
function uri return varchar2
is
begin
return http.myself.name
|| '?BUTTON=' || 'CENTER'
|| ',SORT=' || http.translate.urlencode(sort);
end uri;
/******************************************************************************
**
** caption()
**
*******************************************************************************/
function caption return varchar2
is
begin
return
'
<h1 align=left>HTTP.CRON</h1>
<form name=url method=post action="Sybernet.cgi" target="cron_parameters">
<input type=hidden name=procedure value="HTTP.SP_HTML_TABLE">
<input type=hidden name=value>
<input type=hidden name=border value=1>
<input type=hidden name=cellpadding value=0>
<input type=hidden name=cellspacing value=0>
<input type=hidden name=width value=''50%''>
<input type=hidden name=align value=center>
<input type=hidden name=bgcolor value=white>
<input type=hidden name=thbgcolor value=lightblue>
<input type=hidden name=self_reference value=1>
<input type=hidden name=title value="CRON_PARAMETERS">
<input type=hidden name=caption value="<H1>HTTP.CRON_PARAMETERS</H1>">
</form>
<script>
function url(row_id)
{
window.document.url.value.value="SELECT * FROM CRON_PARAMETERS WHERE ROW_ID = " + row_id;
window.document.url.submit();
return;
}
</script>
' ;
end caption;
/******************************************************************************
**
** value()
**
*******************************************************************************/
function value return varchar2
is
begin
return
'
SELECT ( ''<a href="javascript:url('' || ROW_ID || '');">'' || ROW_ID || ''</a>'' ) "ROW_ID"
, PROCNAME
, TITLE
, DESCRIPTION
, EMAIL
, PLATFORM
, STARTTIME
FROM CRON
ORDER BY ' || SORT;
end value;
/******************************************************************************
**
** main()
**
*******************************************************************************/
begin
http.writeln('<html>');
http.sp_html_preferences;
http.sp_html_table
(
value => value
, width => '"100%"'
, border => '1'
, bordercolor => 'black'
, cellpadding => '0'
, cellspacing => '0'
, bgcolor => 'white'
, align => 'center'
, nls_date_format => 'MM/DD/YY'
, THbgColor => 'lightblue'
, TDbgColor => 'white'
, lpp => 30
, lps => 30
, caption => caption
, valign => 'top'
, debug => false
, defer_processing => 0
, uri => uri
) ;
http.writeln('</body>');
http.writeln('</html>');
end;