Chapter 1 Overview
This chapter introduces the components of the Oracle WebServer, and explains how they work together.
Chapter 2 Introduction to HTML
This chapter provides basic information about HTML. The chapter also provides step by step instructions for creating an HTML file to be viewed on a Web browser.
Chapter 3 The Oracle Web Listener
This chapter explains how to use and administer the Oracle Web Listener. The chapter shows how the Oracle Web Server handles requests for static and dynamic documents, and how it returns documents to the client.
Chapter 4 Introduction to the Oracle7 Server and to PL/SQL
This chapter provides a brief overview of how to use the Oracle7 Server, and how to use PL/SQL, a language for developing applications for an Oracle database.
Chapter 5 Oracle Web Agent
This chapter describes the Oracle Web Agent, the main differentiating factor between the Oracle WebServer and other Web servers with database backends. The chapter also describes how to set up and manage the Oracle Web Agent.
Chapter 6 Oracle WebServer Developer's Toolkit
This chapter explains how to use special utilities that generate HTML tags within PL/SQL procedures, thereby easing the development of dynamic HTML pages with PL/SQL.
Chapter 7 The Oracle WebServer Administration Utility
This chapter explains how to use the Administration Utility, which is a set of Web pages that make it easy for you to create and configure Oracle Web Listeners and Oracle Web Agent services.
Appendix A Oracle WebServer Messages
This appendix describes the messages generated by the components ofthe Oracle WebServer. Foreach message that is caused by an error, it offers a cause and a suggested action.
Note: If you are reading this manual on-line on the Web, you will notice a number of hypertext links to help you navigate through the document. However, be aware that HTML does not support a return to the origin of a link within a document. If you use a link to move to a different section of the document, you do not return to the anchor, but to the previous target that you reached through a link.
| Feature | Explanation |
| monospace text | Text to be enterd by the user. Type text exactly as shown. Text typed on a PC is not case-sensitive, unless noted otherwise; text typed on a UNIX system is case sensitive. Monospace is also used when referring to text in syntax statements or example code. |
| italics | Italics represent a variable. Substitute an appropriate value. Italics are also used for emphasis, to draw attention to the first use of a technical term, and for names of documents. |
| [ ] | Brackets enclose optional items or indicate a function key. Do not type the brackets. |
| | | A vertical bar represents an "or" option among several options. You must enter one, and only one, of the options separated by the vertical bar. Do not enter the vertical bars themselves. |
| punctuation | Punctuation other than brackets and vertical bars must be entered in commands exactly as shown. |
| UPPERCASE | UPPERCASE characters within the text represent command names and HTML tags and attributes. |
If you are unfamiliar with HyperText Markup Language, see Chapter 2, "Introduction to HTML".
Administrators should read Chapter 3, "Oracle Web Listener," Chapter 5, "Oracle Web Agent," and Chapter 7 "The Oracle WebServer Administration Utility" to understand how these components work together, and how to administer them.
Programmers will be particularly interested in Chapter 5 "Oracle Web Agent," as well as Chapter 6, "Oracle WebServer Developer's Toolkit," which explains how to generate dynamic HTML documents in PL/SQL. It lists all the functions and procedures provided by the Oracle WebServer Developer's Toolkit to simplify the task of generating dynamic documents.
If you are unfamiliar with the Oracle7 Server and PL/SQL, read the introduction to these two subjects in Chapter 4, "Introduction to the Oracle7 Server and to PL/SQL".
Oracle Internet Products Documentation Manager
Oracle Corporation
500 Oracle Parkway
Redwood City, CA 94065
U.S.A.
FAX: 415-506-7200
Business data is stored on the Oracle7 Server. It is formatted into Web documents within the server and then transmitted to Web clients. All data is stored only once, eliminating the need to "snapshot" data periodically for use on the Web.
The components that make up the Oracle WebServer work together to make the delivery of both static and dynamic pages possible.
The Oracle Web Listener can handle a large number of simultaneous requests, and has advanced features to use more efficiently system resources than other HTTP servers available on the market. These features are described in more detail in Chapter 3, "Oracle Web Listener".
When the Oracle Web Listener receives a request from a client, it first determines whether that request is for a static document or a dynamic document. If the request is for a static document, the Web Listener sends the file and the associated type information directly to the client. If the request is for a dynamic document, it is created "on the fly" by a program invoked by the Web Listener, in compliance with the Common Gateway Interface (CGI). CGI is an interface that enables HTTP servers to run a program and use the output of that program in a document that is sent to the user. Most HTTP servers support this interface, and the Oracle Web Listener uses it to invoke the Oracle Web Agent when a database procedure is requested. In addition, any other executable program may be invoked through CGI.
For further information about the Web Listener, see Chapter 3.
For more information on the Oracle Web Agent, please see Chapter 5, "Oracle Web Agent".
When the Oracle Web Agent logs into the Oracle7 Server, it starts a PL/SQL procedure that has been created by the user to generate an HTML page as its output. The procedures and functions in the Developer's Kit generate the actual HTML tags, so that the user can be focused on the content of the application, rather than the specifics of HTML.
Chapter 6 includes specific information on the routines included in the Oracle WebServer Developer's Toolkit. Chapter 4 includes an introduction to programming in PL/SQL.
For a discussion of the Oracle7 Server, see Chapter 4 in this manual and the documentation listed in the Preface of this book.
The chapter covers the following topics:
Note: This chapter contains examples of formatted elements, such as underlined text. Some of these examples may not appear correctly on all on-line viewing systems.
Most documents have common elements, such as a title, paragraphs, or lists. Using HTML tags you can label these elements as you are writing. HTML tags provide the browser with a minimum of presentation information, while keeping the integrity of information in the document. All the reader needs is a formatting tool, a Web browser, which interprets the HTML tags and produces an on-screen display that approximates the intent of the document creator.
With most methods of documentation, the writer of a document has strict control over the look and feel of a document. With HTML, the reader (along with the capabilities of the Web browser) has control over the look and feel of a document. HTML allows you to mark titles or paragraphs with HTML instructions or tags, and then leaves the interpretation of these tags up to the browser. For example, one browser may indent the beginning of each paragraph, and another may leave only a blank line. The user of a particular browser may also have some control over the specific fonts used.
HTML tags can be divided into two main categories:
HTML editors such as SoftQuad's "HoTMetaL" allow the creation of HTML documents graphically in "what you see is what you get" (WYSIWYG) mode. In addition, many traditional word processing packages have add-ons or integrated HTML output capabilities.
To create dynamic pages that retrieve information from an Oracle7 Server, you can generate HTML with the PL/SQL utility packages provided by the Oracle WebServer Developer's Toolkit. See Chapter 5, "Oracle Web Agent," and Chapter 6, "Oracle WebServer Developer's Toolkit,"for more information.
An example is the title tag which surrounds the text that is designated as the document's title:
<TITLE>All the Hockey Greats</TITLE>
Tags are usually paired as follows:
<TITLE> and </TITLE>
The ending tag looks like the beginning tag except that a forward slash precedes the text within the bracket. In this example, the tag <TITLE> tells the Web browser to use a title format, and the </TITLE> tells the browser that the title heading is complete.
A few tags, such as <P>, which is a paragraph delimiter, do not need an end tag, but most do.
HTML is not case sensitive; therefore, the previous tags could look like this:
<title>All the Hockey Greats</title>
The convention used in this document is to capitalize all HTML format tags.
Note: Extra spaces, tabs, or returns that you have added by hand are highly discouraged, and will be lost. HTML only interprets tabs, extra spaces, and returns enclosed by the <PRE> </PRE> tags. See "Preformatted Tag" for more information on this HTML option.
<HTML>...the content of the document... </HTML>
Head Tag
The head tag can be used right after the HTML declaration, or not at all in your document. This tag represents the prologue to the rest of the file. Avoid putting any text into the document head tag. The head tag is placed immediately before and after the <TITLE> tag, as shown in the following example:
<HTML> <HEAD> <TITLE>All the Hockey Greats</TITLE> </HEAD>
Note: Technically, the start and end tags for <HTML>, <HEAD>, and <BODY> are not needed. However, they are recommended because the head and body structure tags allow a browser to determine certain properties of a document, such as the <TITLE>, without having to parse, or go through the whole document.
Title Tag
Most browsers display the contents of the <TITLE> tag in the title bar of the window containing the document, and in the bookmark file of the browser if it supports one. The title, surrounded by <TITLE> and </TITLE> tags, is placed between the Head tags, as shown above. The title of a document does not appear in the contents of the document window, however. You must separately indicate it as a heading inside the body of the document if you want it to appear there.
Body Tag
The body of the document should be marked off with the <BODY> and </BODY> tags. This is the part of the document that is displayed as the page of text and graphics on your Web browser.
Heading Levels
When writing an HTML document, organize the text by heading levels to reflect its structure and organization. The first heading would be level 1, the next sub heading level 2, and so on. Most browsers recognize up to six heading levels, with six distinct styles. Heading levels above 6 are indistinguishable from one another.
The largest heading is a level 1 heading. The syntax of the head 1 is as follows:
<H1>Hockey Greats on Offense</H1>
Other headings can be created as follows:
<Hx>Text here</Hx>
where x is a number between 1 and 6 specifying the heading level. For example, if your next heading level is a level 3, the syntax would look like the following:
<H3>Hockey Defense </H3>
Paragraph Tag
Unlike most word processors, HTML usually ignores carriage returns. Word wrapping can occur at any point in your source file. Therefore, paragraphs must be separated with the <P> tag. If you do not separate your paragraphs with the <P> tag, your document will look like one long paragraph.
Preformatted Tag
The preformatted tag, <PRE>, allows you to present text formatted specifically to a screen. The preformatted text ends at the closing </PRE> tag. Within the preformatted text:
Let's incorporate some of the previous examples to show what the document looks like with a title, a couple of heading level tags, and a few paragraphs:
<HTML> <HEAD> <TITLE>All the Hockey Greats</TITLE> </HEAD> <BODY> <H1>All the Hockey Greats Before 1970</H1> <H2>The Original Six Teams</H2> This section deals with all of the hockey legends before the expansion.<P> The game was very different for these players.<P> There is no way New Jersey would have won the Stanley Cup. New Jersey just would not have had the talent to do it.<P> Chicago would still be on top.<P> All would be well.<P> </BODY> </HTML>
The result would display something like this:
All The Hockey Greats Before 1970
The Original Six Teams
This section deals with all of the hockey legends before the expansion.
The game was very different for these players.
There is no way New Jersey would have won the Stanley Cup. New Jersy just would not have had the talent to do it.
Chicago would still be on top.
All would be well.
Note: The title, "All the Hockey Greats," would not show within the document itself. On most browsers it would be displayed in the title bar.
Forced Line Breaks
The <BR> tag forces a line to break . The best example of the use of this tag is for formatting addresses, or some other sequence of lines where you don't want the browser to add extra spacing. For example:
Sandy's Super Sundaes<BR> 123 Main Street<BR> Anytown, USA<BR>
BlockQuote
The <BLOCKQUOTE> tag is used to contain text quoted from another source. The quote will be indented approximately 8 spaces. For example:
My favorite hockey saying is<P> <BLOCKQUOTE> Today is a great day for hockey. </BLOCKQUOTE> But I'm not sure if Bob Johnson really said it like that.</P>
This would appear something like the following:
My favorite hockey saying is
Today is a great day for hockey.
But I'm not sure if Bob Johnson really said it like that.
| Opening | Closing | Definition |
| <HTML> | </HTML> | An entire HTML document |
| <HEAD> | </HEAD> | The prologue of the document |
| <TITLE> | </TITLE> | Title of the document |
| <BODY> | </BODY> | Content of the document |
| <H1> | </H2> | First level heading |
| <H2> | </H2> | Second level heading |
| <H3> | </H3> | Third level heading |
| <H4> | </H4> | Fourth level heading |
| <H5> | </H5> | Fifth level heading |
| <H6> | </H6> | Sixth level heading |
| <P> | Paragraph | |
| <PRE> | </PRE> | Preformatted text |
| <BR> | Forced line break | |
| <BLOCKQUOTE> | </BLOCKQUOTE> | Text quoted from another source |
| ordered | These lists have numbered items. |
| unordered | These lists have bullets to mark each item. |
| definition | These lists alternate a term with its definition. |
An ordered list begins with <OL> and ends with </OL>. The individual list items are started with the <LI> tag. The following is an example of an ordered list:
<OL> <LI>Gordie Howe <LI>Rocket Richard <LI>Howie Morenz </OL>
An unordered list begins with <UL> and ends with </UL>. The following is an example of an unordered list:
<UL> <LI>Gordie Howe <LI>Rocket Richard <LI>Howie Morenz </UL>
<HTML> <HEAD> <TITLE>All the Hockey Greats</TITLE> </HEAD> <BODY> <H1>Hockey Greats before Expansion</H1> <H2>The Original Six Teams</H2> This section deals with all of the hockey legends before the expansion.<P> <UL> <LI>Gordie Howe <LI>Rocket Richard <LI>Howie Morenz <UL> <LI>great player <LI>good stickhandler </UL> <LI>Bobby Orr </UL> </BODY> </HTML>
Here's what the example would look like:
Hockey Greats before Expansion
The Original Six Teams
This section deals with all of the hockey legends before the expansion.
<DL> <DT>Slapshot: <DD>A shot used to drill the goalie at speeds up to 100 mph. <DT>Wristshot: <DD>A shot used to scare the goalie after the slapshot. </DL>
The ouput looks like this:
Slapshot: A shot used to drill the goalie at speeds up to 100 mph.
Wristshot: A shot used to scare the goalie after the slapshot.
Anchors have a standard format that allows any Web browser to interpret a link and perform the proper function (called a method) for that type of link. Links can refer to other documents, specific locations within the same document, or can perform operations, such as retrieving a file using FTP for display by the browser. URLs can refer to a specific location by an absolute pathname, or can be relative to the current document, which is more convenient when managing a large site.
Note: You can use hypertext links to navigate through a document or to move from document to document. However, HTML does not support returning you to the anchor point of a link within a document. If you use a hypertext link within a document, and then use the Back button, you do not return to the anchor, but to the previous point that you reached through a link.
The general format of a URL is:
method://machine-name/path/foo.html
The following example would fetch the document index.html from the server www.acme.com using the HTTP protocol.
http://www.acme.com/index.html
A Uniform Resource Locator (URL) has the following format:
method://servername:port/pathname#anchor
The components of the URL are as follows:
| method | is the name of the operation that is performed to interpret this URL. The most commo n methods are: |
| file Read a local file from disk. The filename is interpreted on the user's machine, so thi s can be used to display any file that resides on the user's disk. | |
| For example: file:/home/jjones/jjones.html displays the file jjones.html from t he directory /home/jjones on the local machine. | |
| http Access a page over the network by way of the HTTP protocol. (This is the most common method, usually used to get an HTML document.) | |
| For example: http://www.acme.com/ accesses Acme's home page. | |
| mailto Activate a mail session to the specified username and host. | |
| For example: mailto:jjones@us.acme.com mails a message to jjones if the browser supports mail creation. Note that the mailto method does not require double forward slashes after the colon. | |
| ftp Retrieve a file using anonymous FTP from a server. | |
| For example, ftp://hostname/directory/filename | |
| servername | is optional and indicates the full hostname of a machine connected to the network. F or example, www.oracle.com is the fully qualified hostname of Oracle's web server. If a servername is n ot specified, the URL is a relative link, and it is assumed that the file is on the same server that was used to display the current page. An IP address may be used instead of a hostname, although it is not recommende d to build content with embedded IP addresses. |
| port | is the TCP port number that the web server is running on. The default is 80 if :port is not specified. This parameter is not used in most URLs. |
| pathname | is the relative or absolute pathname of the document being accessed by this URL. Web server s can be configured to interpret certain pathnames differently. For instance, CGI applications work by configuri ng the HTTP server to recognize that files within certain directories should be executed instead of returned to the browser. |
| For example: http://www.acme.com/index.html | |
| In this example, an HTTP connection is made to index.html, which is the name of the file to be accessed on the server www.acme.com using port 80 (the default). The file could have a full UNIX-st yle pathname to indicate a document contained in a lower level directory. If there is no directory component in the pathname, the document must be located in the server's document root directory which is configured by the se rver administrator. If the pathname part of the URL is missing, many servers provide a directory listing of the document root directory or access a specific 'top level' file (usually index.html). | |
| #anchor | The named anchor points to a specific location within an HTML page. In addition to specifyin g a document name, specifying #anchor will cause most browsers to move the top of the display to the point refer red to by the anchor. These anchor names are inserted into documents with the NAME tag as explained under "Linki ng to Sections on Pages"later in this chapter. |
<A HREF="URL">text_to_be_displayed_highlighted
</A>
The <A HREF="URL"> tag opens the anchor link and the </A> tag closes it. All the text between those tags is displayed highlighted in some way by a web browser. A common technique is to display it underlined and in blue or some other user-selected contrasting color.
The URL part of the tag refers to the text of the URL reference as defined in the previous section. The URL text does not appear on the user's screen, it is only used when the user activates the link, usually by clicking on it with the mouse.
An example HTML segment:
For interesting products see <A HREF="http://www.acme.com">Acme's home page.</A>
This line would produce on the user's screen:
For interesting products see Acme's home page.
There are two ways to use graphics from within an HTML document. The first is by embedding them within the document itself, so the user's screen will display the graphics within the context of the other elements of your document such as explanatory text. This is the most common technique used by HTML designers and is called an "inline image". The syntax for specifying this is:
<IMG SRC="URL" ALT="text" ALIGN=["top"|"middle"|"bottom"|"texttop"] ISMAP>
The elements in this syntax statement are as follows:
| URL | is the same syntax as any other URL, as explained above. This is the way the browser accesses the actual image data file, which should be in a format supported by the browser. Currently GIF and JPEG formats are supported by most browsers. Specifying the URL is required. |
| ALT="text" | will cause the string text to be displayed if the browser is incapable of displaying images, or if image display is turned off. This is a way of 'labelling' the image the user would be seeing if image display was turned on. ALT is an optional keyword; if it is missing, no text will be displayed if images are turned off on the browser. Most browsers put some sort of icon on the screen to indicate an image would normally be there. Using the ALT tag is recommended so that your page is compatible with text-only browsers such as Lynx. |
| ALIGN | is used with one of the keywords to tell the browser where to place the next block of text. This allows a certain amount of creativity in the layout of your page. If this is not specified, most browsers put the image on the left side of the screen and fill in following text to the right of it. |
| ISMAP | tells the server that this image is a bitmap and allows the user to click on a location on the image to cause a URL to be accessed directly. Image maps are an advanced HTML feature and require server side configuration to function. |
<IMG SRC="http://www.oracle.com/logo.gif" ALT="Oracle Logo">
To create a link, follow these steps:
1. Create a named anchor marker in each section title that you might want to jump to. The HTML syntax is as follows:
<A NAME="named_anchor"> Text_to_link_to</A>
<H2><A NAME="intro">Introduction to Hockey</A></H2>
2. Create the link by entering:
<A HREF="#named_anchor">Text</A>
<A HREF="#intro">Gordie Howe</A>
The "#" symbol instructs your web browser to look through the HTML document for a named anchor called "intro".
When the user clicks on "Gordie Howe," the browser displays the heading "Introduction to Hockey" at the top of the screen.
Note: As specified earlier in the URL syntax, a link to a section can appear in the same document or in another document. The example here describes a link to a specific section of the same document.
1. After you have edited your HTML file in your favorite text editor, save the file.
2. If you are currently displaying the document in a browser, you will need to reload it to see the changes. Select File -->Reload on the browser window. (In some browsers, reload will be under another category)
The browser will read in the new file information and display the file with the changes you have made.
| Style | Element or Tag | Result |
| Bold | <B>I want this text bold</B> | I want this text bold |
| Italics | <I>I want this text italics</I> | I want this text italics |
| Underlined | <U>I want this underlined</U> | I want this underlined |
| Mono spaced(typewriter) | <TT>I want this text typed</TT> | I want this text typed |
<I>Hockey</I> is <B>Life</B>.<P>
becomes a paragraph that looks like this:
Hockey is Life.
The style tags surround the words they affect, in conjunction with other tags such as headings.
Be careful how many style tags you use on one page. If you use too many the text may become difficult to read.
Address Tag
The <ADDRESS> tag is used to specify the author of a particular HTML document, and a way to contact the author ( such as an email address). The syntax is as follows:
<ADDRESS> address_of_author </ADDRESS>
Escape Sequences
The following ASCII characters have special meaning within HTML and cannot be used in regular text:
| For | Use |
| < | < |
| > | > |
| & | & |
| " | " |
Cells can be merged across rows or columns.
Table: <TABLE>...</TABLE>
This is the main wrapper for all the other table tags. Other table tags can be ignored if they aren't wrapped inside of the <TABLE></TABLE> tags. By default, tables have no borders. Borders are added if the border attribute is specified. See the next section "Basic Table Attributes".
Table Row: <TR> </TR>
The number of rows in a table is specified by how many <TR> tags are contained within it. <TR> can have both the Align and Valign attributes, which if specified become the default alignments for all cells in this row. See the next section, "Basic Table Attributes".
Table Data: <TD> </TD>
This specifies a standard table data cell. Table data cells must only appear within table rows. Each row need not have the same number of cells specified, because short rows will be padded with blank cells on the right. A cell can contain any of the HTML tags normally present in the body of an HTML document. The default alignment of table data is ALIGN=left and VALIGN=middle. These alignments can be overridden by any alignments specified in the containing <TR> tag. Row alignments are overridden by any attributes assigned to a cell.
By default, lines inside of table cells can be broken up to fit within the overall cell width. Use the nowrap attribute described in the section, "Basic Table Attributes," to prevent line breaking for that cell.
Table Header: <TH> </TH>
The table header cells are identical to data cells in all respects, except that header cells are in a bold font and have a default ALIGN=center.
Caption: <CAPTION>... </CAPTION>
This optional tag represents the caption for a table. The <CAPTION> tags should appear inside the <TABLE></TABLE> tags but not inside table rows or cells. The default alignment for the <CAPTION> tag is ALIGN=top but can be explicitly set to ALIGN=bottom. Like table cells, any document body HTML tags can appear in a caption. Captions are always horizontally centered with respect to the table, and they may have their lines broken to fit within the width of the table.
BORDER: This attribute appears in the Table tag. If present, borders are drawn around all table cells. If absent, there are no borders. By default space is left for borders, so a table has the same width with or without the border attribute.
ALIGN: If the ALIGN attribute appears inside a <CAPTION> </CAPTION> tag, it controls whether the caption appears above or below the table. It can have the values top or bottom. The default attribute is ALIGN=top.
When appearing inside a <TR>, <TH>, or <TD> tag, ALIGN controls whether text inside the table cell(s) is aligned to the left side of the cell, the right side of the cell, or centered within the cell. Values are left, center, and right.
VALIGN: The VALIGN attribute appears inside a <TR>, <TH>, or <TD> tag. This attribute controls whether text inside the table cell(s) is aligned to the top of the cell, the bottom of the cell, or vertically centered within the cell. It can also specify that all the cells in the row should be vertically aligned to the same baseline. Values are top, middle, bottom, and baseline.
NOWRAP: If the NOWRAP attribute appears in any table cell, the lines within this cell cannot be broken to fit the width of the cell. Be cautious in use of this attribute as it can result in excessively wide cells.
COLSPAN: The COLSPAN attribute can appear in any table cell and specifies how many columns of the table a specified cell should span. The default COLSPAN for any cell is 1.
ROWSPAN: The ROWSPAN attribute can appear in any table cell and specifies how many rows of the table this cell should span. The default ROWSPAN for any cell is 1. A span that extends into rows that were not specified with a <TR> tag will be truncated.
COLSPEC: The COLSPEC attribute can be used when needed to exert control over column widths, either by setting explicit widths or by specifying relative widths. Specify the table width explicitly or as a fraction of the current margins.
<TABLE BORDER> <CAPTION ALIGN=bottom> Table #1 </CAPTION> <TR><TD ROWSPAN=2> </TD> <TH COLSPAN=2>Average</TH></TR> <TR><TH>Height</TH><TH>Weight</TH></TR> <TR><TD>Males</TD><TD ALIGN=center> 69 </TD> <TD ALIGN=center>150 </TD></TR> <TR><TD>Females</TD><TD ALIGN=center> 64 </TD> <TD ALIGN=center>130 </TD></TR> </TABLE>
The table will look like this:
| Average | ||
|---|---|---|
| Height | Weight | |
| Males | 69 | 150 |
| Females | 64 | 130 |
When a form is interpreted by a Web browser, a special graphical user interface (GUI) screen is created with text entry fields, buttons, checkboxes, pull-down menus, and scrolling lists. When the Web user fills out the Form and presses a button indicating the form should be submitted, the information on the form is sent to an HTTP server for processing by a CGI (Common Gateway Interface) program. For more information on CGI, see Chapter 3, "The Oracle Web Listener".
When you write a form, each of your input items has an <INPUT> tag. When the user places data in these items in the form, that information is encoded into the form data and is known as the "value".
All form elements have Name and Value attributes. Data are sent as NAME=VALUE pairs, separated by ampersands (&), where name is given in the NAME attribute, and value is given in the VALUE attribute, or replaced by the user.
This section illustrates the basic use of HTML forms. Forms can be used for simple table searches or complex queries to relational databases.
The syntax is as follows:
<FORM METHOD="get|post" ACTION="URL">Form_elements_and_other_HTML </FORM>
METHOD
The request method used to supply the data to the program. There are two request methods that can be used to access your forms. Depending on which request method you use, you will receive the encoded results of the form in a different way.
ACTION specifies the URL being requested from the form. This URL will almost always point to a CGI script to decode the form results. If you are referring to a script on the same server as the form, you can use a relative URL.
The <TEXTAREA> tag is used to allow a user to enter more than one line of text in a form. The following is an example of the TEXTAREA tag:
<TEXTAREA NAME="address" ROWS=14 COLS=60> Chicago Blackhawks 1800 Madison Ave Chicago, Il 60612 </TEXTAREA>
The attributes included within the <TEXTAREA> tag are used to initialize the field's value. The </TEXTAREA> tag is always required even if the field is initially blank.
The following are attributes of <TEXTAREA> and determine the visible dimensions of the field in characters:
| NAME | user defined name |
| ROWS | height in characters of TEXTAREA |
| COLS | width in characters of TEXTAREA |
INPUT
The <INPUT> tag allows you to input a single word or line of text, with a default width of 20 characters. It is usually preceded with some descriptive text.
The following are attributes of <INPUT>:
The SELECT tag allows the user to select a value from a fixed list. This is usually presented as a pull down menu.
The SELECT tag has one or more options between the start<SELECT> and end</SELECT> tag. By default the first option is displayed in the menu. The following is an example of a <SELECT> tag:
<FORM> <SELECT NAME=group> <OPTION> Gretzky <OPTION> Messier <OPTION> Coffey </SELECT> </FORM>
SELECT SINGLE
The SELECT SINGLE tag is the same as the SELECT tag, but options are displayed in a window with three items shown at once. If there are more than three options, the window will have a scroll bar. The SIZE tag within the SELECT tag specifies how many options will be shown in the window. The following is an example of a <SELECT SINGLE>tag:
<FORM> <SELECT SINGLE NAME=group SIZE=3> <OPTION> Gretzky <OPTION> Messier <OPTION> Coffey <OPTION> Kurri </SELECT> </FORM>
In this example, the first three names would appear in the window, and a scroll bar would scroll to the last name.
SELECT MULTIPLE
The SELECT MULTIPLE tag is the same as the SELECT SINGLE tag, but the user can select more than one option in the window. The SIZE tag specifies how many lines appear in the window, and the MULTIPLE tag specifies how many options can be selected.
The following is an example of SELECT MULTIPLE:
<FORM> <SELECT MULTIPLE NAME=group SiZE=3 MULTIPLE=2> <OPTION> Gretzky <OPTION> Messier <OPTION> Coffey <OPTION> Kurri </SELECT> </FORM>
Note: On some browsers, it may be necessary to hold down the CONTROL or SHIFT key to select multiple items.
If multiple items are selected, they each get passed to the server with the same name. The decoding script has to be able to recognize multiple values associated with the same name.
The following is an example of an HTML document that you can type word for word, or modify as you wish:
<HTML> <HEAD> <TITLE>Chicago Blackhawks: A Love Story</TITLE> </HEAD> <BODY>Chicago Blackhawks: A Love Story <P>This is a story about my beloved Blackhawks. Year after painful year they amass teams that could potentially win a Stanley Cup. But year after insidiously painful year, they manage to lose in the first or second round of the playoffs. If you have any suggestions as to how to reconcile this evil, many fans would be grateful. Still we live on to love the Blackhawks. <H2>Some Players We Have Loved</H2> <UL> <LI><A HREF="Hull.html">Bobby Hull</A> <LI><A HREF="Makita.html">Stan Makita</A> <LI><A HREF="Espo.html">Tony Esposito</A> <LI><A HREF="Jr.html">Jeremy Roenick</A> </UL> <H2>Some Players We Love to Hate</H2> <OL> <LI><A HREF="Clark.html">Wendall Clark</A> <LI><A HREF="Gilmour.html">Doug Gilmour</A> <LI><A HREF="Domi.html">Tie Domi</A> <LI><A HREF="cheapshot.html">Ulf Samuellson</A> </OL> <H2>List of Love Letters to Our Team</H2> <UL> <LI><A HREF="http://www.love2hawks.com/">Love Letters</A> <LI><A HREF="http://www.fromNHL.com/">Letters from NHL</A> </UL> <HR><A HREF="Write">Write</A>to me. Click below to send me comments. <BR> <A HREF="mailto:oldstadium@madison.com"> <I>crazed4hawks, oldstadium@madison.com</I> </A> </BODY> </HTML>
http://www.ncsa.uiuc.edu/demoweb/html-primer.html http://union.ncsa.uiuc.edu/HyperNews/get/www/html.html http://fire.clarkson.edu/doc/html/htut.html http://ugweb.cs.ualberta.ca/~gerald/guild/html.html
From a user's perspective, the Web is a collection of documents or pages which contain text, images, and hypertext links to other pages. By pointing, clicking, and traversing the links, the user has instant access to a distributed collection of information. The Oracle Web Listener merges the techniques of information retrieval and hypertext to create a powerful global information system.
The Oracle Web Listener works on a simple client-server model. Clients send requests to the Oracle Web Listener. The listener interprets a request by reading the URL, finds (or generates) the information requested, and returns that information to the client in the form requested.
You can run several Oracle Web Listeners on the same machine by giving each a dedicated port. Having multiple Web Listeners is a good way to balance load among various applications.
The language that the Oracle Web Listener uses to communicate with its clients is HyperText Transfer Protocol (HTTP). The Web Listener implements version 1.0 of the HTTP protocol as described in the IETF HTTP Working Group (http://www.ics.uci.edu/pub/ietf/http/). All Web clients must be able to speak the HTTP protocol in order to send and receive hypermedia documents.
HTTP is an application level protocol with the lightness and speed necessary for distributed, collaborative, hypermedia information systems. It is a generic stateless, object-oriented protocol which can be used for many systems, through extension of its request methods (commands).
In the current version of the Oracle Web Listener, the list of files to be cached in memory is determined by the WebServer Administrator and configured manually. The administrator should periodically analyze the Listener logs to determine which files should be cached and to change the configuration accordingly.
The specific mapping of externally visible (virtual) directories to local physical directories in the file system is configurable by the WebServer administrator.
For example, the URL
http://www.acme.com/products/info.html
might be configured to be actually located on the file system of system www.oracle.com as:
/disk1/products/info.html
If the products directory is moved to /disk3 in a reorganization, the administrator reconfigures the virtual directory products to point to the new location /disk3/products in the Web Listener configuration file, and the Web client is completely unaware of the change. The Web Listener needs to be reloaded to recognize the change. For more information on reloading the Web Listener, see "Web Listener Control Utility" later in this chapter.
The mapping of specific file extensions to language and data types is controlled by the Web Listener configuration file.
For more information about the Web Listener configuration file, see "Configuration Parameters," later in this chapter.
When a Web client requests an object, it can specify what types of data it can receive and express a preference for specific types. The Web Listener can use the information to send the appropriately formatted data to the client if more than one format is stored on the Web Listener. This process is called HTTP type negotiation and is fully supported by the Oracle Web Listener.
For example, a document might contain an inline image, which could be transferred in either GIF or JPEG format to a Web client.
The Oracle Web Listener also supports a similar concept for negotiation of the appropriate national language to display a document for a specific user. The document could be stored in multiple national languages (for instance English and Canadian French), and the appropriate copy of the document would be sent if the client expressed a preference. This makes the maintenance of a multilingual Web site a great deal easier, since there is no need to maintain duplicate URLs and separate directory trees for different languages.
When a request comes in that the Web Listener recognizes as a request to execute a CGI application, a separate process is created to perform the operation. The Web Listener maintains communication with the process to transmit input from the Web client to it, and to retrieve the HTML output it may generate for display on the client's screen.
The Web Listener administrator can configure specific directories to contain CGI applications to be run instead of files to be transmitted. Either of these can be done using the Web Listener configuration file. For more information on the Web Listener configuration file, see "Configuration Parameters," later in this chapter.
The technique of CGI application execution is one of the key features of the Web. It allows the integration of many information sources. The Oracle Web Agent is one CGI application that can be accessed using this technique.
Once the Listener has determined that a URL represents a CGI application, it interprets the URL to extract path information and arguments to be passed to the CGI application on startup.
URLs to CGI programs are split into three different parts:
virtual_path extra_path_information?query_string
| virtual path | similar to a path you would use to access a regular document or image. That is, it points the server to the file that contains the CGI program you want executed. |
| extra path information | additional information you can embed in the URL after the program name. Extra path information is optional. It can be used to convey constant information to your scripts independent of the client's intervention. It can also be used to access the server's virtual-to-physical path translation mechanism. |
| query string | another optional part of the URL. It can either be explicitly given in the hypertext anchor, it can come from a user typing into a search dialog box for an HTML document with the ISINDEX tag, or it can come from HTML forms. |
If the data is coming from an HTML form, then the location of this data varies depending on the method attribute specified with the FORM tag in your HTML document. If the GET method is used, this information comes from a QUERY_STRING variable. If the POST method is used, this information is sent to your program using standard input.
The data provided will be in the form:
name1=value1&name2=value2.....&nameN=valueN
If there are any equal signs (=) or ampersands (&) in the encoded data, they are encoded using the above encoding rules. To decode the data appropriately, the NAME=VALUE pairs should be split (eliminating the ampersands), then each pair should be split into a name and a value, and then the URL decoding should be applied to each portion of the pair.
When a form is submitted you can often use the order in which the form items appear to determine the order in which your CGI program receives the NAME=VALUE pairs. However you should not depend on this behavior. The various form elements have their own ways to determine what value will be associated with the name they are given. All of the textual input areas use the user's typed input as the value. Radio buttons use the value of the enabled button. If checkboxes are unchecked, they will use either an empty string, or their name will not appear in the encoded form data at all. Hidden form elements can be used to send constant or state information to your script.
The Oracle Web Listener includes native processing of imagemaps within the Web Listener. Many other HTTP servers require a separate process to be created to interpret an imagemap. By doing this internally, the Oracle Web Listener improves performance and lowers system resource utilization when this common feature of HTML is being used.
The DNS process can be time consuming, since another machine where the naming system is already running may be contacted.
The Oracle Web Listener provides three options for controlling DNS resolution of network addresses:
When DNS resolution does occur, the results are cached. Since there are often several connections from the same client in close succession, this helps to minimize the performance penalty if DNS resolution is required.
When a client requests a file protected by IP-based security, its address is compared against the specified addresses in the order given, and as soon as a match is found, the client is allowed or denied access as specified. Files protected by IP-based restriction may not be accessed by clients other than those listed in the configuration parameters.
Note that Digest Authentication is a safer technique to determine who a user really is, since a credential (the password) must be presented to gain access. Network address based restriction techniques are very convenient in that they avoid password management problems, but are also less secure, since a clever attacker may falsify network addresses.
This technique uses symbolic hostnames rather than network addresses. Hence is easier to administer since network addresses may change if the network architecture is changed. This technique still suffers from the problem of clever attackers falsifying their network addresses and assuming the identity of a host other than their own.
Note: Be sure that the user ID under which the Web Listener runs has privileges to read and write to the log file directory.
The syntax you use to invoke WLCTL to perform various operations is as follows:
> wlctl [start|stop|reload] [port_number] ORACLE_HOME
If ORACLE_HOME is set in your environment and is the ORACLE_HOME where the Oracle WebServer is installed, it need not be included on the command line.
The following examples assume that the Oracle WebServer is located in /u01/oracle:
>wlctl start 8888
>wlctl start 8888 /u01/oracle
wlctl stop 8888
wlctl stop 8888 /u01/oracle
>wlctl reload 8888
>wlctl reload 8888 /u01/oracle
The stop and reload functions require that the Listener PID file for the Web Listener you are stopping or reloading resides in the location pointed to by the Web Listener configuration file.
Oracle WebServer provides an HTML-based Administration Utility which may be accessed with any forms-capable Web browser. This utility eliminates the need for the WebServer administrator to edit the Web Listener configuration file manually in most cases, and includes explanatory help text on the individual parameters. The Administration Utility is described in Chapter 7, "The Oracle WebServer Administration Utility". This section documents the parameters in the configuration file for completeness, in the event an administrator wishes to edit the file manually.
The Web Listener configuration file is divided into sections, which start with a section name in brackets--for example, [NetInfo]. Individual configuration parameters are set by name = value pairs, with the configuration parameter on the left of the equal sign and the value on the right as specified in the table below.
For example, consider the following portion of a configuration file:
; ; www.acme.com configuration file ; [NetInfo] HostName = www.acme.com HostAddress = ANY PortNumber = 80
This portion of the file sets HostName to www.acme.com, HostAddress to ANY and PortNumber to 80.
Table 3 - 1 contains the parameters, grouped by logical function.
| Section Parameter Name | Default Value | Description |
| NetInfo HostName | none | hostname Web Listener is on, fully qualified host name, eg. www.acme.com The Listener uses the primary Internet address if no host name is specified. |
| HostAddress | ANY | Internet address on which the Web Listener on a multi-homed host accepts connection. ANY indicates connections accepted on all interfaces (IP addresses). Leave this set to ANY unless you have a specific need to change it. |
| PortNumber | 80 | TCP/IP port number on which the Web Listener accepts requests. Note that port numbers less than 1024 require the Web Listener to run as root on many UNIX systems. 80 is the usual default for Web HTTP servers. |
| MaxConnectCount | 50 | Maximum number of simultaneous connections from users. The Web Listener ignores requests over this limit. Note there is a compile-time per-platform limit which may not by exceeded, no matter what value is specified here. |
| DNSResolution | NEVER | Controls DNS address to name resolution. If set to ALWAYS, the Web Listener always translates address to names. If set to LAZY, the Web Listener resolves names on demand only. If set to LAZY_WITH_CGI, the Web Listener also resolves on demand for CGI applications. If set to NEVER the Web Listener never resolves, even on demand (this provides the best performance). |
| SERVERPID | none | Name of file in which to store the Web Listener's process identifier (PID). |
| Section Parameter Name | Default Value | Description |
| [Log] LogDir | none | Name of directory to contain log files. This directory must exist before the Web Listener starts. |
| LogInfoFile | none | Name of file in which to store routine log information. |
| LogErrorFile | none | Name of file in which to log errors. |
Note: Be sure that the user ID under which the Web Listener runs has privileges to read and write to the log file directory.
Directory_Name R|N|C Virtual_Path_Name
; ; directory mapping section of Web Listener configuration file ; [DirMaps] c:\html R /
For instance, in the above example, the directory c:\html and all subdirectories below it are mapped to the root directory (/) on the Web. So an access to the URL:
http://listenername/index.html
would actually read the following file on the Web Listener's disk.
c:\html\index.html
If a file is not listed here it is not cached. When a non-cached file is requested by a client, the server maintains the resources to access that file only as long as the client refers to the file. The server will release the resources required to access a file requested by a client once there are no outstanding client references to the file.
There are three options for specifying files in the file cache.
; ; File Cache Definitions ; [FileCache] /index.html /marketing/a* /products
Note that the filenames specified are virtual path names (the names that appear in URLs on the Web), not necessarily the physical names of the files on disk if the Directory Mapping feature has been used.
For example:
; ; encoding definitions ; List for each combination of encodings ; one or more unique extensions ; [Encodings] compress Z gzip gz
Note that files that do not map to any type are by default considered MIME type application/octet-stream which denotes a binary file.
Each line in this section consists of:
MIME-type list of file extensions mapping to that type
For example:
[MIMEType] text/html htm html image/jpeg jpg jpeg image/gif gif
This example tells the Web Listener that files with extensions:
Each line consists of:
language_ID character_set_ID list_of_file-extensions
For example:
[LangExt] en iso-8859-1 eng en unicode-1-1 engU uc fr-CA iso-8859-1 frc
This example maps all files with the extension eng to English, ISO 8859-1 character set. Files with extension engU or uc are mapped to English, in the unicode character set. Files with extension frc are mapped to French Canadian language, ISO-8859-1 character sets.
The names of the character sets are as specified in RFC 1521, the language identifiers are defined in RFC 1766.
In order to use language mapping capability within the Web Listener, URLs should be specified without trailing extensions. The Web Listener adds the appropriate language/character set extension as required if the client supports natural language negotiation.
For instance:
http://www.oracle.com/index
results in access to the file:
/index.frc.html
if the client had configured French Canadian as the preferred language.
A list of languages may be configured on the client, so that several languages may be specified and if any of them is present, the file will be sent according to the first match. Note that if a file has no language extension before its type extension, that file is assumed to be language neutral. The default character set is ISO-8859-1.
To summarize: if the files /index.frc.html and /index.html existed, users who specified French Canadian as a language preference would see the first version of the file, and all other users would see the second version.
There are several sections in the file, one for each type of security.
The concept in Basic and Digest authentication is the same. Users are specified as username/password combinations, followed by specifying which users are in what groups. Finally, groups are assigned to security realms which are the parameters assigned to specific files to protect them.
An example:
[Security]
Digest {
(Users)
jjones: glorkz
kmaco: plugh
ajames: foo
(Groups)
mktg: ajames
dev: kmaco
all: jjones kmaco ajames
(Realms)
restricted: all
secret: dev mktg
}
This segment defines three users, (jjones, kmaco, ajames), with passwords (glorkz, plugh, foo) respectively. The group all has everyone in it, the groups mktg and dev have a single individual in each. The security realm restricted is accessible to all three people, but only the members of groups dev and mktg can access the secret realm.
In this situation, any file that was defined in the restricted realm would be accessible to the three users specified here (but no one else who used the same Web Listener), and any file in the secret realm would be accessible only to users ajames and kmaco.
Basic authentication is configured identically to Digest authentication, with the module name Digest above replaced with Basic.
Digest authentication is recommended wherever possible, to eliminate the possibility of passwords being intercepted on the network.
It is possible to include a host or a group of hosts by specifying the plus sign ( +) before the identifier for that host, and exclude it by including a minus sign (-) before the identifier. In addition, the asterisk wildcard (*) can be used to match groups of hosts.
For example:
[Security]
IP {
st: +144.25.16.*
+144.25.20.*
dc: +130.35.*.*
-130.35.1.1
}
This would configure everything on the networks 144.25.16 and 144.25.20 to be in group st, and everything on network 130.35 except host 130.35.1.1 to be in group dc. If the client's address doesn't match any of these, access will be denied to any file specified to require st or dc security access.
The same technique is used with hostnames in the case of domain name restriction.
The following example configures all machines in the domain oracle.com to be in group oracle except machine www.oracle.com:
[Security]
Domain {
oracle: +*.oracle.com
-www.oracle.com
}
Once the security groups have been defined, you can assign specific protections to individual files.
For example:
[Protection] /secret/ IP(st) | Basic(secret) /company/* Domain(acme)
This example protects everything under the directory /secret using either IP based restriction from the st group or by anyone who can log into the secret realm using Basic authentication. All the files in /company are protected so that only people in the acme domain may see them.
| Section Parameter Name | Default Value | Description |
| Server UserDir | none | Directory under a user's home directory searched when the URL /~username/ is received. This allows users to store their own home pages in their home directories (UNIX only). |
| InitialFile | initial | The file that the Listener looks for when a URL ending in a directory rather than a file is sent to the Listener. This is the 'index' file for the Web Listener. |
| UserDirInitialFile | initial.html | Default filename when the /~username/ construction is used in a URL with no file specified. |
| DefaultMIMEType | application/octet-stream | Default MIME type used by the Web Listener when the filename extension requested in aURL is not recognized. |
| DefaultCharset | iso-8859-1 | Default character set if none is inferred from the file extension |
| PreferredLanguage | en | Language a Web Listener will prefer given a choice of languages when searching for a file. Default is English (en). |
| ImageMap | none | Set this to 'map'. This is the extension the Web Listener expects imagemaps to have. |
| ServiceTimeout | none | On windows NT, the time the NT control panel will wait for a start, stop, or continue to complete before failure. Not used on any other platform. |
Here is a simple table, such as you might find in an Oracle database:
| CNUM | FNAME | LNAME | ADDRESS |
| 4005 | Julia | Peel | 197 Myrtle Court, Brisbane, CA |
| 4007 | Terry | Subchak | 2121 Oriole Way, Boston, MA |
| 4008 | Emilio | Lopez | 31D San Bruno Ave. SF, CA |
| 4011 | Kerry | Lim | 455 32nd St. #45, Brinton, KY |
Each row of this table describes one person, and each column has one type of information about that person. Note the column cnum. This is simply a number we generate to distinguish the customers from one another, as names are not necessarily unique. As you will see shortly when we discuss SQL, you refer to data in a relational database by its content, not by such things as where it is stored. Therefore, every table must have an identifying group of one or more columns whose values, taken as a set, are always different for every row of the table. This group, in this case the single column cnum, is called the primary key of the table. Locally generated numbers, as in this example, are a common and easy way to create primary keys.
1. You could fit all the phone numbers for a given person into a single column in a single row, in which case it would be difficult to access the phone numbers independently.
2. You could create a column for each type of phone number, in which case you would have to redesign your table each time a new type arose. This also could create an unwieldy number of mostly empty columns.
3. You could enter a new row for each phone number, in which case each such row would consist of redundant information except for the new phone number. This approach would be error-prone and waste space.
The good solution is simply to create a second table, like this:
| CNUM | PHONE | TYPE |
| 4005 | 375-296-8226 | home |
| 4005 | 375-855-3778 | beeper |
| 4008 | 488-255-9011 | home |
| 4011 | 577-936-8554 | home |
| 4008 | 488-633-8591 | work |
Notice that in this table cnum is not the primary key; it identifies the customer and therefore is the same for each phone number associated with a given customer. What, then, is the primary key? The combination of cnum and phone. If we list the same number for the same person twice, we really have made a duplicate entry and should eliminate one anyway.
The cnum column does have a special function, however, because it defines the relationship between Customers_Phone and Customers by associating each phone number with a customer. We say that it references the cnum column in Customers. A group of one or more columns, such as this, that references another group is known as a foreign key. The group of columns a foreign key references is called its parent key or its referenced key. Each foreign key value references a specific row in the table containing the parent key. Clearly, then, all sets of values in the foreign key have to be present once and only once in the parent key (although they may be present any number of times in the foreign key itself, as above) for the reference to be both meaningful and unambiguous. For that reason, the parent key must be either a primary key (the usual case) or another group of columns that is unique, which is known as a unique key.
Oracle can make sure that all primary and unique keys stay unique and that all foreign key references are valid; this is called maintaining referential integrity. For more information on foreign and parent keys, see Chapter 7 of the Oracle7 Server Concepts Manual and "CONSTRAINT clause" in Chapter 4 of the Oracle7 SQL Reference.
Although Oracle tables may look similar to, and often be presented as, HTML tables, there is a subtle but important difference. In HTML, tables are a way of presenting data to the user. The data itself may not actually have a tabular structure or any structure at all. For example, since not all browsers can interpret graphics, you could represent a bar graph in HTML as a table.
In the Oracle RDBMS, tables are how all data is structured and stored. Once the data from the tables is retrieved by other processes, however, those processes can reformat it so that it is not presented to the users as tables at all. For example, a very simple two-column table might consist of graphics (Oracle can store graphics, sound, video, or any binary value in a table using the datatype LONG RAW) in one column and a name for each graphic in the other. An application could use the names to request the graphics it wants from the database and present the graphics to the user, never revealing that they came from a table.
The name under which you connect to Oracle7--your Oracle username-- is associated with a number of privileges, which are the rights to perform various actions. Privileges can be granted and revoked dynamically, and sometimes you can grant privileges you have received to other users as well. Some privileges apply to specific database objects--these are called object privileges. Others are more general in nature--these are called system privileges. For more information on privileges, see "GRANT" and "REVOKE" in the Chapter 4 of the Oracle7 Server SQL Reference.
Roles are groups of privileges that are granted and revoked as single units. They simplify complex privilege assignments. They also differ from simple privileges in that they are not always in force. You can be granted a role, but have it disabled, in which case you cannot use the privileges the role contains until you enable it. For more information on roles, see "CREATE ROLE", "ALTER ROLE", and "GRANT" in the Oracle7 Server SQL Reference.
User actions can also be constrained by profiles. These control such things as how many simultaneous connections you can have and how long they can last. Profiles are beyond the scope of this manual. For more information on profiles, see "CREATE PROFILE" in the Oracle7 Server SQL Reference.
The software that enables distributed databases to communicate with one another is SQL*Net, an Oracle product that enables communication between various servers that possibly are on different platforms and using different network protocols. For more information on SQL*Net, see Understanding SQL*Net.
SELECT * FROM Customers WHERE LNAME = 'Peel';
This produces the following:
CNUM FNAME LNAME ADDRESS 4005 Julia Peel 197 Myrtle Court, Brisbane, CA
Oracle interprets the statement as follows:. Any number of spaces and/or line breaks are equivalent to one space or line break. These are delimiters, and the extra spaces and line breaks are for readability: all are equivalent "white space". Likewise, case is not significant, except in literals like the string you are searching for ('Peel').
SELECT is a keyword telling the database that this is a query. All SQL statements begin with keywords. The asterisk means to retrieve all columns; alternatively, you could have listed the desired columns by name, separated by commas. The FROM Customers clause identifies the table from which you want to draw the data.
WHERE LNAME = 'Peel' is a predicate. When a SQL statement contains a predicate, Oracle tests the predicate against each row of the table and performs the action (in this case, SELECT) on all rows that make the predicate TRUE. This is an example of set-at-a-time operation. The predicate is optional, but in its absence the operation is performed on the entire table, so that, in this case, the entire table would have been retrieved. The semi-colon is the statement terminator.
In most respects, NULL has the same effect as FALSE. The major exception is that, while NOT FALSE = TRUE, NOT NULL = NULL. In other words, if you know that an expression is FALSE, and you negate (take the opposite of) it, then you know that it is TRUE. If you do not know whether it is TRUE or FALSE, and you negate it, you still do not know. In certain cases, three-valued logic can create problems with your programming logic if you have not accounted for it. You can treat nulls specially in SQL with the IS NULL predicate, as explained in Chapter 3 of the Oracle7 Server SQL Reference
CREATE TABLE Customers (CNUM integer NOT NULL PRIMARY KEY, FNAME char(15) NOT NULL, LNAME char(15) NOT NULL, ADDRESS varchar2 );
After the keywords CREATE TABLE come the table's name and a parenthesized list of its columns with a definition of each. Integer, char, and varchar2 are datatypes: all of the data in a given column is always of the same type (char means a fixed and varchar2 a varying length string). For more information on SQL datatypes, see Chapter 2 of the Oracle7 Server SQL Reference.
NOT NULL and PRIMARY KEY are constraints on the columns they follow. They restrict the values you can enter in those columns. Specifically, NOT NULL forbids you from entering nulls in the column. PRIMARY KEY prevents you from entering duplicate values into the column and makes the column eligible to be the parent for some foreign key. For more information, see "CREATE TABLE" and "CONSTRAINT clause" in Chapter 4 of the Oracle7 Server SQL Reference.
scott.Customers.LNAME
You can simplify references like this by using synonyms, which are aliases for tables or other database objects. Synonyms can be private, meaning that they are part of your schema and you control their usage, or public, meaning that all users can access them. For example, you can create a synonym "Cust" for scott.Customers as follows:
CREATE SYNONYM Cust FOR scott.Customers;
This would be a private synonym, which is the default. Now you could rewrite the example above like this:
Cust.LNAME
You still have to refer to the column directly. Synonyms can only be for tables, not table components like columns.
For more information on synonyms, see "CREATE SYNONYM" in Chapter 4 of the Oracle7 Server SQL Reference. For more information on SQL naming conventions, see Chapter 2 of the Oracle7 Server SQL Reference. For more on schemas, see "CREATE SCHEMA" in Chapter 4 of the Oracle7 Server SQL Reference.
INSERT INTO Customers (cnum, FNAME, LNAME) VALUES (2004, 'Harry', 'Brighton');
This statement inserts a row with a value for every column but ADDRESS. Since you did not, in your CREATE TABLE statement, place a NOT NULL constraint on the ADDRESS column, and since you did not give that column a value here, Oracle sets this column to null. If you are inserting a value into every column of the table, and you have the values ordered as the columns are in the table, you can omit the column list. You optionally can put a SELECT statement in place of the VALUES clause of the INSERT statement to retrieve data from elsewhere in the database and duplicate it here. For more information on the INSERT and the SELECT statements, see "INSERT" and "SELECT", respectively, in Chapter 4 of the Oracle7 Server SQL Reference.
UPDATE Customers SET ADDRESS = null WHERE LNAME = 'Subchak';
This sets to null all addresses for customers named 'Subchak'. The SET clause of an UPDATE command can refer to current column values. "Current" in this case means the values in the column before any changes were made by this statement. For more information on the UPDATE statement, see "UPDATE" in Chapter 4 of the Oracle7 Server SQL Reference.
DELETE FROM Customers WHERE LNAME = 'Subchak';
You can only delete entire rows not individual values. To do the latter, use UPDATE to set the values to null. Be careful with DELETE that you do not omit the predicate; this empties the table. For more information on DELETE, see "DELETE" in Chapter 4 of the Oracle7 Server SQL Reference.
SELECT a.CNUM, LNAME, FNAME, PHONE, TYPE FROM Customers a, Customer_Phone b WHERE a.CNUM = b.CNUM;
In the above, a and b are range variables, also called correlation variables. They are simply alternate names for the tables whose names they follow in the FROM clause, so that a = Customers and b = Customers_Phone. You can see that here you need the range variables to distinguish Customers.CNUM from Customers_Phone.CNUM in the SELECT and WHERE clauses. Even when not needed, range variables are often convenient.
Here is the output of the natural join:
CNUM LNAME FNAME PHONE TYPE 4005 Peel Julia 375-296-8226 home 4005 Peel Julia 375-855-3778 beeper 4008 Lopez Emilio 488-255-9011 home 4008 Lopez Emilio 488-633-8591 work 4011 Lim Kerry 577-936-8554 home
This output represents every combination of rows from the two tables where both rows have the same CNUM value.
SELECT a.CNUM, LNAME, FNAME, PHONE, TYPE FROM Customers a, Customer_Phone b WHERE a.CNUM = b.CNUM (+);
This is the output of the above:
CNUM LNAME FNAME PHONE TYPE 4005 Peel Julia 375-296-8226 home 4005 Peel Julia 375-855-3778 beeper 4007 Subchak Terry NULL NULL 4008 Lopez Emilio 488-255-9011 home 4008 Lopez Emilio 488-633-8591 work 4011 Lim Kerry 577-936-8554 home
Notice that the only difference in the query is the addition of (+) to the WHERE clause. This follows the table for which nulls are to be inserted. The output from the query, then, includes at least one row for each row of the table that did not have (+) appended in the predicate.
You can also use SELECT statements to produce values for processing within queries (these are called subqueries), and you can perform standard set operations (UNION, INTERSECTION) on SELECT statement output. For more information on the SELECT statement, subqueries, and joins, see "SELECT" in Chapter 4 of the Oracle7 Server SQL Reference.
| To Find Out About | Look Under |
| aggregate data (totals, counts, averages, and so on) | SQL Functions in Chapter 3 of the Oracle7 Server SQL Reference. |
| changing user passwords | ALTER USER |
| connecting to the database | CONNECT |
| constraints | CONSTRAINT clause; CREATE TABLE; ENABLE clause |
| controlling user access to objects and user actions | GRANT; REVOKE; CREATE ROLE; SET ROLE; see also Chapters 17 and 18 in the Oracle7 Server Concepts Manual |
| creating databases | CREATE DATABASE |
| creating users | CREATE USER |
| functions that change simple values | SQL Functions in Chapter 3 of the Oracle7 Server SQL Reference. |
| linking databases at different locations | CREATE DATABASE LINK; see also "Distributed Databases" in the Oracle7 Server Concepts Manual. |
| making changes to the data permanent | COMMIT; SET TRANSACTION; SAVEPOINT |
| making SQL statements execute more quickly | CREATE INDEX; see also "Indexes" in the Oracle7 Server Concepts Manual. |
| monitoring database usage | AUDIT |
| reversing (undoing) changes to the data | ROLLBACK; SET TRANSACTION; SAVEPOINT |
PL/SQL, then, is an application-development language that is a superset of SQL, supplementing it with standard programming-language features that include the following:
Unlike SQL, PL/SQL is not an industry standard, but is an exclusive product of Oracle Corporation.
Note: For the sake of efficiency, PL/SQL code is compiled prior to runtime. It cannot refer at compile time to objects that do not yet exist, and, for that reason, the one part of SQL that PL/SQL does not include is DDL (Data Definition Language)--the statements, such as CREATE TABLE, that create the database and the objects it contains. However, you can work around this by using the package DBMS_SQL, included with the server, to generate the DDL code itself dynamically at runtime. For more information, see "Using DDL and Dynamic SQL" in the PL/SQL User's Guide and Reference.
A block has three parts:
In many cases, you can convert from one datatype to another, either explicitly or automatically. The possible conversions and the procedure involved are explained in the PL/SQL User's Guide and Reference under "Datatype Conversion".
You can also define a variable so that it inherits its datatype from a database column or from another variable or constant, as explained in the next section.
cnum INTEGER(5) NOT NULL;
This declares a five-digit integer called cnum that will not accept nulls. The use of case above serves to distinguish keywords from identifiers; PL/SQL is not case-sensitive. NOT NULL is the only SQL constraint that you can use as a PL/SQL attribute.
Note: PL/SQL initializes all variables to null. Therefore, a NOT NULL variable, such as the above, produces an error if referenced before it is assigned a value.
Optionally, you can assign an initial value to the variable when you declare it by following the datatype specification with an assignment, as follows:
cnum INTEGER(5) := 254;
This sets cnum to the initial value of 254. Alternatively, you can use the keyword DEFAULT in place of the assignment operator := to achieve the same effect. For more information on setting defaults, see "Declarations" in the PL/SQL User's Guide and Reference .
Inheriting Datatypes To have the variable inherit the datatype of a database column or of another variable, use the %TYPE attribute in place of a declared datatype, as follows:
snum cnum%TYPE;
This means that snum inherits the datatype of cnum. You can inherit datatypes from database columns in the same way, by using the notation tablename.columname in place of the variable name. Normally, you do this if the variable in question is to place values in or retrieve them from the column. The advantages are that you need not know the exact datatype the column uses and that you need not change your code if the datatype of that column changes. If you do not own the table containing the column, precede the tablename with the schemaname, as described under "Naming Conventions" elsewhere in this chapter. For more information on %TYPE assignments, see "Declarations" in the PL/SQL User's Guide and Reference.
interest CONSTANT REAL(5,2) := 759.32;
SUBTYPE shortnum IS INTEGER(3);
This defines SHORTNUM as a 3-digit version of INTEGER. For more information see "User-Defined Subtypes" in the PL/SQL User's Guide and Reference.
The area of a program within which an object can be used is called the object's scope. An object's scope is distinct from its visibility. The former is the area of the program that can reference the object; the latter is the, generally smaller, portion that can reference it without qualification.
An ambiguous reference can arise because objects or subprograms contained in different blocks can have the same names, even if they have overlapping scopes. When this happens, the reference by default means the object most local in scope--in other words, the first one PL/SQL finds by starting in the current block and working out to the enclosing ones. Qualification is the method used to override this. It is similar to the system of qualification used for database objects, as explained under "Naming Conventions" elsewhere in the chapter. To qualify an object's name, precede it with the name of the subprogram where it is declared, followed by a dot, as follows:
relocate.transmit(245, destination);
This invokes a procedure called transmit declared in some subprogram called relocate. The subprogram relocate must be global to the block from which it is called.
PL/SQL Tables These are somewhat similar to database tables, except that they always consist of two columns: a column of values and a primary key. This also makes them similar to one-dimensional arrays, with the primary key functioning as the array index. Like SQL tables, PL/SQL tables have no fixed allocation of rows, but grow dynamically. One of their main uses is to enable you to pass entire columns of values as parameters to subprograms. With a set of such parameters, you can pass an entire table. The primary key is always of type BINARY_INTEGER, and the values can be of any scalar type.
You declare objects of type TABLE in two stages:
1. You declare a subtype using the following syntax:
TYPE type_name IS TABLE OF datatype_spec [ NOT NULL ] INDEX BY BINARY INTEGER;
Where datatype_spec means the following:
datatype | variablename%TYPE | tablename.columname%TYPE
In other words, you can either specify the type of values directly or use the %TYPE attribute (explained under "Declaring Variables", elsewhere in this chapter) to inherit the datatype from an existing variable or database column.
2. You assign objects to this subtype in the usual way. You cannot assign initial values to tables, so the first reference to the table in the EXECUTABLE section must provide it at least one value.
When you reference PL/SQL tables, you use an array-like syntax of the form:
column_value(primary_key_value)
In other words, the third row (value) of a table called "Employees" would be referenced as follows:
Employees(3)
You can use these as ordinary expressions. For example, to assign a value to a table row, use the following syntax:
Employees(3) := 'Marsha';
For more information, see "PL/SQL Tables" in the PL/SQL User's Guide and Reference.
Records As in many languages, these are data structures that contain one or more fields. Each record of a given type contains the same group of fields with different values. Each field has a datatype, which can be RECORD. In other words, you can nest records, creating data structures of arbitrary complexity. As with tables, you declare records by first declaring a subtype, using the following syntax:
TYPE record_type IS RECORD (fieldname datatype[, fieldname datatype]...);
The second line of the above indicates a parenthesized, comma-separated, list of fieldnames followed by datatype specifications. The datatype specifications can be direct or be inherited using the %TYPE attribute, as shown for TABLE and as explained under "Declaring Variables", elsewhere in this chapter.
You can also define a record type that automatically mirrors the structure of a database table or of a cursor, so that each record of the type corresponds to a row, and each field in the record corresponds to a column. To do this, use the %ROWTYPE attribute with a table or cursor name in the same way you would the %TYPE attribute with a variable, or column. The fields of the record inherit the column names and datatypes from the cursor or table. For more information, see "Records" and "%ROWTYPE Attribute" in the PL/SQL User's Guide and Reference.
Cursors A cursor is a data structure that holds the results of a query (a SELECT statement) for processing by other statements. Since the output of any query has the structure of a table, you can think of a cursor as a temporary table whose content is the output of the query.
When you declare a cursor, you associate it with the desired query. When you want to use that cursor, you open it, executing the associated query and filling the cursor with its results. You then fetch each row of the query's output in turn for processing by other statements in the program. You can also use a cursor to update a table's contents. To do this, use a FOR UPDATE clause to lock the rows in the table. See "Using FOR UPDATE" in the PL/SQL User's Guide and Reference for more information. Sometimes, you may need to use cursor variables, which are not associated with a query until runtime. This is a form of dynamic SQL.
For more information on cursor variables, see "Using Dynamic SQL" in the Oracle7 Server Application Developers Guide and "Cursor Variables" in the PL/SQL User's Guide and Reference.
For more information on cursors in general, see "Cursors" in the PL/SQL User's Guide and Reference. See also "DECLARE CURSOR," "OPEN", and "FETCH" in the Oracle7 Server SQL Reference.
You can simplify some cursor operations by using cursor FOR loops. For more information on these, see "Using Cursor FOR Loops" in the PL/SQL User's Guide and Reference.
PROCEDURE procedure_name (parameter_name datatype,
parameter_name datatype...) IS
{local declarations}
BEGIN {executable code}
EXCEPTION
END;
Note: For subprograms, the keyword DECLARE is omitted before the local declarations. Place local declarations before the keyword BEGIN, as shown.
The names you give the parameters in the declaration are the names that the procedure itself uses to refer to them. These are called the formal parameters. When the procedure is invoked, different variables or constants may be used to pass values to or from the formal parameters; these are called the actual parameters.
When calling the procedure, you can use each parameter for input of a value to the procedure, output of a value from it, or both. These correspond to the three parameter modes: IN, OUT, and IN/OUT. For more information, see "Parameter Modes" in the PL/SQL User's Guide and Reference.
When you call the procedure, you can match the actual to the formal parameters either implicitly, by passing them in the same order they are given in the declaration, or explicitly, by naming the formal followed by the actual parameter as shown:
transmit(destination => address);
This invokes a procedure called transmit, assigning the value of address as the actual parameter for the formal parameter destination. This implies that the parameter destination is used within the transmit procedure and that the parameter address is used outside of it. Usually, it is good programming practice to use different names for matching formal and actual parameters. For more information on this, see "Positional and Named Notation" in the PL/SQL User's Guide and Reference.
Functions are the same, except for the addition of a return value, specified as follows:
FUNCTION function_name (parameter_name, parameter_name datatype...)
RETURN datatype IS
{local declarations}
BEGIN {executable code}
EXCEPTION {local exception handlers}
END;
Again, line breaks are only for readability. A RETURN statement in the executable section actually determines what the return value is. This consists of the keyword RETURN followed by an expression. When the function executes the RETURN statement, it terminates and passes the value of that expression to whichever statement called it in the containing block.
You can also use the RETURN statement without an expression in a procedure to force the procedure to exit.
For more information on procedures and functions, see "Declaring Subprograms" in the PL/SQL User's Guide and Reference.
a := 45;
Character strings should be set off with single quotes (') as in all expressions. An example follows:
FNAME := 'Clair';
There are other examples of assignments in other parts of this chapter.
You can nest flow control statements within one another to any level of complexity.
The IF statement has the following forms:
1 IF <condition> THEN <statement-list>; END IF;
If the condition following IF is TRUE, PL/SQL executes the statements in the list following THEN. A semicolon terminates this list. END IF (not ENDIF) is mandatory and terminates the entire IF statement. Here is an example:
IF balance > 500 THEN send_bill(customer); END IF;
We are assuming that send_bill is a procedure taking a single parameter.
2 IF <condition> THEN <statement-list>; ELSE <statement-list>; END IF;
This is the same as the preceding statement, except that, if that condition is FALSE or NULL, PL/SQL executes the statement list following ELSE instead of that following THEN.
3 IF <condition> THEN <statement-list>; ELSIF <condition> THEN <statement-list>; ELSIF <condition> THEN <statement-list>;..... ELSE <statement-list>; END IF;
You can include any number of ELSIF (not ELSEIF) conditions. Each is tested only if the IF condition and all preceding ELSIF conditions are FALSE or NULL. As soon as PL/SQL finds an IF or ELSIF condition that is TRUE, it executes the associated THEN statement list and skips ahead to END IF. The ELSE clause is optional, but, if included, must come last. It is executed if all preceding IF and ELSIF conditions are FALSE or NULL.
NULL Statements If you do not want an action to be taken for a given condition, you can use the NULL statement, which is not to be confused with database nulls, Boolean NULLs, or the SQL predicate IS NULL. The syntax of this statement is simply:
NULL;
The statement performs no action, but fulfills the syntax requirement that a statement list must follow every THEN keyword. In some cases, you can also use it to increase the readability of your code. For more information on the NULL statement, see "NULL Statement" in the PL/SQL User's Guide and Reference.
credit := 0; LOOP IF c = 5 THEN EXIT; END IF; credit := credit + 1; END LOOP;
This loop keeps incrementing credit until it reaches 5 and then exits. An alternative to placing an exit statement inside an IF statement is to use the EXIT-WHEN syntax, as follows:
EXIT WHEN credit = 5;
This is equivalent to the earlier IF statement.
Note: The EXIT statement cannot be the last statement in a PL/SQL block. If you want to exit a PL/SQL block before its normal end is reached, use the RETURN statement. For more information, see "RETURN Statement" in the PL/SQL User's Guide and Reference.
FOR credit IN 1..5 LOOP interest := interest * 1.2; END LOOP;
The numbers used to specify the range (in this case, 1 and 5) can be variables, so you can let the number of iterations of the loop be determined at runtime if you wish.
credit := 1; WHILE credit <= 5 LOOP interest := interest * 1.2; credit := credit + 1; END LOOP;
Unlike some languages, PL/SQL has no structure, such as REPEAT-UNTIL, that forces a LOOP to execute at least once. You can create this effect, however, using either basic or WHILE loops and setting a variable to a value that will trigger the loop, as in the above example. For more information on loops, see "Iterative Control" in the PL/SQL User's Guide and Reference.
<<this_is_a_label>>
You only use the brackets at the target itself, not in the GOTO statement that references it, so a GOTO statement transferring execution to the above label would be:
GOTO this_is_a_label;
Note: An EXIT statement can also take a label, if that label indicates the beginning of a loop enclosing the EXIT statement. You can use this to exit several nested loops at once. See "Loop Labels" in the PL/SQL User's Guide and Reference for more information.
A GOTO statement is subject to the following restrictions:
There are two basic kinds of exceptions: predefined and user-defined. The predefined exceptions are provided by PL/SQL in a package called STANDARD. They correspond to various runtime problems that are known to arise often--for example, dividing by zero or running out of memory. These are listed in the PL/SQL User's Guide and Reference under "Predefined Exceptions".
The Oracle Server can distinguish between and track many more kinds of errors than the limited set that STANDARD predefines. Each of Oracle's hundreds of messages are identified with a number, and STANDARD has simply provided labels for a few of the common ones. You can deal with the other messages in either or both of two ways:
customer_deceased EXCEPTION;
In other words, an identifier you choose followed by the keyword EXCEPTION. Notice that all this declaration has done is provide a name. The program still has no idea when this exception should be raised. In fact, there is at this point no way of telling if this is to be a user-defined exception or simply a label for an Oracle message.
PRAGMA EXCEPTION_INIT (exception_name, Oracle_error_number);
A PRAGMA is a instruction for the compiler, and EXCEPTION_INIT is the type of PRAGMA. This tells the compiler to associate the given exception name with the given Oracle error number. This is the same number to which SQLCODE is set when the error occurs. The advantage of this over defining your own error condition is that you pass the responsibility for determining when the error has occurred and raising the exception to Oracle. You can find the numeric codes and explanations for Oracle messages in Oracle7 Server Messages.
IF cnum < 0 THEN RAISE customer_deceased;
You can also use the RAISE statement to force the raising of predefined exceptions. For more information, see "Error Handling" in the PL/SQL User's Guide and Reference.
This is the syntax of an exception handler:
WHEN exception_condition THEN statement_list;
The exception is the identifier for the raised condition. If desired, you can specify multiple exceptions for the same handler, separated by the keyword OR. The exception can be either one the package STANDARD provided or one you declared. The statement list does what is appropriate to handle the error--writing information about it to a file, for example--and arranges to exit the block gracefully if possible. Although exceptions do not necessarily force program termination, they do force the program to exit the current block. You cannot override this with a GOTO statement. You can use a GOTO within an exception handler, but only if its destination is some enclosing block.
Note: If you have an error prone statement and want execution to continue following this statement, even when an exception occurs, put the statement, including the appropriate exception handlers, in its own block, so that the current block becomes the enclosing block.
Note: If an exception occurs in the DECLARE section or the EXCEPTION section itself, local exception handlers cannot address it; execution passes automatically to the EXCEPTION section of the enclosing block.
The syntax for these statements is slightly different than that used to declare subprograms in PL/SQL, as the following example shows:
CREATE PROCEDURE fire_employee (empno INTEGER) IS BEGIN DELETE FROM Employees WHERE enum = empno; END;
As you can see, the main difference is the addition of the keyword CREATE. You also have the option of replacing the keyword IS with AS, which does not affect the meaning. To replace an existing procedure of the same name with this procedure (as you frequently may need to do during development and testing), you can use CREATE OR REPLACE instead of simply CREATE. This destroys the old version, if any, without warning.
GRANT EXECUTE ON show_product TO PUBLIC;
Of course, the public normally does not execute such a procedure directly. This statement enables you to use the procedure in your PL/SQL code that is to be publicly executable. If multiple users access the same procedure simultaneously, each gets his own instance. This means that the setting of variables and other activities by different users do not affect one another.
For more information on privileges and roles, see "GRANT" in Chapter 4 of the Oracle7 Server SQL Reference. There are three versions of GRANT listed--one each for object privileges, system privileges, and roles.
For more information on storing procedures and functions in the database, see "Storing Procedures and Functions" in the Oracle7 Server Application Developers Guide and see "CREATE FUNCTION" and "CREATE PROCEDURE" in the Oracle7 Server SQL Reference.
Packages cannot be nested, but they can call one another's public subprograms and reference one another's public objects.
There is an exception, however. When one package calls another, execution of the second has a dependency on the first. If the first is invalidated, for example because its creator loses a privilege that the package requires, the second, while not necessarily invalidated, becomes deinstantiated. That is to say, all its objects are reinitialized.
Note: In PL/SQL, stored procedures and packages are automatically recompiled if changes to the database mandate it. For example, a change to the datatype of a column can automatically cascade to a variable referencing that column if the former is declared with the %TYPE attribute, but that change requires that the PL/SQL procedure declaring that variable be recompiled. So long as the PL/SL code as written is still valid, the recompilation occurs automatically and invisibly to the user.
Note: Before you can create a package, the special user SYS must run the SQL script DBMSSTDX.SQL. The exact name and location of this script may vary according to your operating system. Contact your database administrator if you are not sure this script has been run.
Creating the Package Specification The syntax of the CREATE PACKAGE statement is as follows:
CREATE [OR REPLACE] PACKAGE package_name IS
{PL/SQL declarations}
END;
The optional OR REPLACE clause operates just as it does for stored procedures, as explained elsewhere in this chapter. The PL/SQL declarations are as outlined under DECLARE SECTION elsewhere in this chapter, except that the keyword DECLARE is not used and that the subprogram and cursor declarations are incomplete. For subprograms, you provide only the name, parameters, and, in the case of functions, the datatype of the return value. For cursors, provide the name and a new item called the return type. This approach hides the implementation of these objects from the public while making the objects themselves accessible.
The syntax for declaring a cursor with a return type is as follows:
CURSOR c1 IS RETURN return_type;
The return type is always some sort of record type that provides a description of the cursor's output. The structure of this record is to mirror the structure of the cursor's rows. You can specify it using any of the following:
Creating the Package Body To create the package body, use the CREATE PACKAGE BODY statement. The syntax is as follows:
CREATE [OR REPLACE] PACKAGE BODY package_name IS
{PL/SQL declarations}
END;
Since a package as such does not do anything, the PL/SQL code still consists only of a DECLARE section with the keyword DECLARE omitted. It is the subprograms within the package that contain the executable code. Variables, constants, types, and cursors declared directly (in other words, not within a subprogram) in the declare section have a global scope within the package body. Variables, constants, and types already declared in the package specification are public and should not be declared again here.
Public cursors and subprograms, however, must be declared again here, as their declarations in the specification is incomplete. This time the declarations must include the PL/SQL code (in the case of subprograms) or the query (in the case of cursors) that is to be executed. For subprograms, the parameter list must match that given in the package specification word for word (except for differences in white space). This means, for example, that you cannot specify a datatype directly in the specification and use the %TYPE attribute to specify it in the body.
You can create an initialization section at the end of the package body. This is a body of executable code--chiefly assignments--enclosed with the keywords BEGIN and END. Use this to initialize constants and variables that are global to the package, since otherwise they could be initialized only within subprograms, and you have no control of the order in which subprograms are called by outside applications. This initialization is performed only once per session.
For more information, see CREATE PACKAGE BODY in the Oracle7 Server SQL Reference, "Packages" in the PL/SQL User's Guide and Reference, and "Using Procedures and Packages" in the Oracle7 Server Application Developers Guide.
The reason this is permitted is so you can overload subprograms. Overloading permits you to have several versions of a procedure that are conceptually similar but behave differently with different parameters. This is one of the properties of object-oriented programming. For more information on overloading, see "Overloading" in the PL/SQL User's Guide and Reference.
You create triggers as you do stored procedures and packages, by using your text editor to write scripts that create them and then using SQL*Plus or Server Manager to run these scripts. A trigger is like a package in that:
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
DELETE | INSERT | UPDATE [OF column_list]
ON table_name
[ FOR EACH ROW [ WHEN predicate ] ]
{PL/SQL block};
In the above, square brackets ([ ]) enclose optional elements. Vertical bars ( | ) indicate that what precedes may be replaced by what follows.
In other words, you must specify the following:
CREATE TRIGGER give_bonus AFTER UPDATE OF sales ON salespeople FOR EACH ROW WHEN sales > 8000.00 BEGIN UPDATE salescommissions SET bonus = bonus + 150.00; END;
This creates a row trigger called give_bonus. Every time the sales column of the salespeople table is updated, the trigger checks to see if it is over 8000.00. If so, it executes the PL/SQL block, consisting in this case of a single SQL statement that increments the bonus column in the salescommissions table by 150.00.
If these names are not suitable, you can define others using the REFERENCING clause of the CREATE TRIGGER statement, which is omitted from the syntax diagram above for the sake of simplicity. For more information on this clause, see CREATE TRIGGER in the Oracle7 Server SQL Reference.
Note: if a trigger raises an unhandled exception, its execution fails and the statement that triggered it is rolled back if necessary. This enables you to use triggers to define complex constraints. If the effects of the trigger have caused a change in the value of package body variables, however, this change is not reversed. You should try to design your packages to spot this eventuality. For more information, see "Using Database Triggers" in the Oracle7 Server Application Developers Guide.
ALTER TRIGGER trigger_name DISABLE;
Later you can enable the trigger again by issuing the same statement with ENABLE in place of DISABLE. The ALTER TRIGGER statement does not alter the trigger in any other way. To do that you must replace the trigger with a new version using CREATE OR REPLACE TRIGGER. For more information on enabling triggers, see ALTER TRIGGER in the Oracle7 Server SQL Reference.
For more information on triggers generally, see "Using Database Triggers" in the Oracle7 Server Application Developer's Guide and CREATE TRIGGER and DROP TRIGGER in the Oracle7 Server SQL Reference.
The Oracle Web Agent provides an easy-to-use environment for building applications in PL/SQL. These applications are then stored in the Oracle7 database. A programmer who has chosen PL/SQL as the language in which to implement a CGI application need only worry about implementing the logic specific to the application itself, because the Oracle Web Agent provides utilities which take care of the repetitive tasks associated with deploying a CGI application.
The HTML output generated by a PL/SQL procedure is sent from the Oracle7 Server to the Oracle Web Agent. The output is then passed on to the Oracle Web Listener. The Web Listener sends the generated HTML document back to the Web client.
The HTML administrative forms that simplify the task of configuring the Oracle Web Agent are documented in Chapter 7, "Oracle WebServer Administration Utility."
To facilitate the development of PL/SQL for the Web Agent, a WebServer Developer's Toolkit is provided. This toolkit includes the following packages:
For detailed information about the Oracle WebServer Developer's Toolkit, see Chapter 6. For information on the WebServer Administration Utility for administering the Web Agent, see Chapter 7, "Oracle WebServer Administration Utility."
This is a step by step description of how the Oracle Web Agent works:
1. The user submits a URL from the browser.
Company A would like data stored in its Oracle7 database to be accessible to the public via an HTTP server. However, different departments within Company A have access to different parts of the database, which are partitioned by way of userids. Thus, the Human Resources department and the Accounts Payable department use different userid/password combinations to log on to the database. If both want to write CGI applications using the Oracle Web Agent to access their data, the Web Agent needs to use the correct userid/password when it logs on to the database. In this scenario, the owa.cfg file, which contains the configuration information, will contain two services, an HR service, and an AP service. Each service will have an associated userid/password, an ORACLE_HOME parameter, and an ORACLE_SID parameter. When a request from a Web browser comes in, the HTTP server will extract the service name that is embedded in the URL and find out which parameters to use by reading the owa.cfg file.
To connect to the Oracle7 Server, the Web Agent needs the following information to be specified in the Web Agent service:
The following is an example of a Web Agent service entry in the owa.cfg file, and a corresponding explanation for each parameter:
Developer's T# ( owa_service = es ( owa_user = www_es ) ( owa_password = tiger ) ( oracle_home = /opt/oracle7 ) ( oracle_sid = esprod ) ( owa_err_page = /es_err.html ) ( owa_valid_ports = 8000 8888 ) ( owa_log_dir = /opt/oracle7/ows/log ) ( owa_nls_lang = AMERICAN_AMERICA.US7ASCII ) )
| owa_service | |
| owa_user | |
| owa_password | |
| oracle_home | |
| oracle_sid | |
| owa_err_page | |
owa_valid_ports
The valid Web Listener network ports the Web Agent will service.
| owa_log_dir | |
owa_nls_lang
The NLS_LANG of the Oracle7 database to which the Web Agent connects. If not specified, the Web Agent administration program looks it up when the the service is submitted.
| Variable | Contains |
| REQUEST_METHOD | GET or POST |
| PATH_INFO | the name of PL/SQL procedure to invoke |
| SCRIPT_NAME | contains the service the Web Agent is to use when logging on to Oracle7 |
| QUERY_STRING | parameters to the PL/SQL procedure (for GET method only. POST method parameters are passed via standard input.) |
The following is an example of how a typical URL is parsed by the Web Listener in compliance with the CGI 1.1 specification.
http://www.nhl.com:8080/ows-bin/nhl/owa/hockey.pass?person=Gretzky
1. The substring http://www.nhl.com:8080 in the above URL signals the Web browser to connect to the www.nhl.com host's port 8080 using the HTTP protocol.
2. When the Oracle Web Listener which is running on www.nhl.com receives the request, the substring /ows-bin/nhl/owa signals the Web Listener to execute the Web Agent instead of returning a file to the browser as it normally would have done with a static HTML document. Please see the section on CGI (Common Gateway Interface) in Chapter 3, "The Oracle Web Listener," for more information on how the Web Listener recognizes the difference between a CGI program and a static HTML document.
3. After spawning the Web Agent, the Web Listener passes /ows-bin/nhl/owa to the Web Agent as the environment variable SCRIPT_NAME. The rest of the URL is passed to the Web Agent in this manner: The substring /hockey_pass gets passed to the Web Agent in PATH_INFO and the substring "person=Gretzky" is passed to the Web Agent in QUERY_STRING.
4. The Web Agent parses the SCRIPT_NAME to extract the service name nhl.
6. The Web Agent makes the parameters passed to it in QUERY_STRING available to the PL/SQL procedure it invokes.
Note: The fact that the QUERY_STRING environment variable is used indicates that this is a GET request. You can also pass parameters using the POST method. For an example of how to pass parameters using the POST method, please see the next section, "Passing Parameters to PL/SQL".
These key concepts and tips are:
Depending on the REQUEST_METHOD used, parameters are passed from the Web Browser to the Web Listener to the Web Agent in one of two ways:
It is recommended that you use POST whenever possible. GET is the method used for links and non-form URLs. For HTML forms, one has a choice. Because the GET method uses operating system environment variables, there are limits on the length of the QUERY_STRING.
Passing Parameters Using an HTML Form
The following example is analogous to the one in the previous section, except that it uses an HTML form that employs the POST REQUEST_METHOD.
<FORM METHOD="POST" ACTION="http://www.nhl.com:8080/ows-bin/nhl/owa/hockey.pass"> Please type the name of the person you wish to search for: <INPUT TYPE="text" NAME="person"><P> To submit the query, press this button: <INPUT TYPE="submit" VALUE="Submit Query">. <P> </FORM>
The above form will cause the Oracle Web Listener to behave in the same fashion as the previous example, except that instead of populating the QUERY_STRING environment variable with "person=Gretzky," the Web Listener will write "person=Gretzky" to standard input. This assumes, of course, user typed "Gretzky" in the entry field of the HTML form above.
The PL/SQL procedure that is the recipient of the above parameters follows:
create or replace
procedure hockey_pass (person in varchar2) is
n_assists integer;
begin
select num_assists into n_assists
from hockey_stats
where name=person;
htp.print(person||' has '||to_char(n_assists)||' assists this season');
end;
Ordering Parameters
Generally, the PL/SQL developer does not need to be concerned with the order in which the Oracle Web Agent receives parameters from an HTML form or through a URL. The only case that it will be relevant is when passing multiple values for the same form field. See "Using Multiple HTML Form Fields with the Same Name" later in this section.
Defaulting Parameter Values
If you cannot guarantee that a value will be passed from a Web Browser for a particular PL/SQL procedure parameter, then it is recommended that you use the parameter DEFAULT functionality. For example:
create or replace procedure showvals(a in varchar2 DEFAULT NULL,
b in varchar2 DEFAULT NULL)
is
begin
htp.print('a = '||a||htp.br);
htp.print('b = '||b||htp.br);
end;
If the Web Agent were to receive a request to call procedure showvals where there was no value for "a", the value for "b" was, say "Hello", and the DEFAULT NULL clause was not in the procedure creation, then the request would generate an error with the following message:
OWS-05111: Agent : no procedure matches this call OWA SERVICE: test_service PROCEDURE: showvals PARAMETERS: =========== B: Hello
By "defaulting" the parameters, the above request would properly output:
a = <BR> b = Hello<BR>
which to the end user would look like:
a = b = Hello
Using Multiple HTML Form Fields with the Same Name
There are a number of instances where one would want to have multiple values passed for the same HTML form variable, and hence to the same PL/SQL parameter. To handle this situation, one can use PL/SQL tables to create an array of values.
One case where multiple values are passed with the same HTML form variable name is in the use of the HTML form tag "SELECT". If one sets the SIZE parameter to something greater than 1, then the user will be able to select multiple values for the same form field.
Another case where one has a set of values corresponding to a single form field follows:
-- QUERY_FORM prints an HTML page with all the columns for the
-- specified table. Invoke the procedure from a Web Browser with
-- a URL like:
http://yourhost:port_num/service_name/owa/query_form?the_table=emp
create or replace procedure query_form(the_table in varchar2) is
cursor cols is
select column_name
from user_tab_columns
where table_name = upper(the_table);
begin
htp.htmlOpen;
htp.headOpen;
htp.htitle('Query the '||the_table||' table!');
htp.headClose;
htp.bodyOpen;
-- Use owa_util.get_owa_service path to automatically retrieve
htp.formOpen(owa_util.get_owa_service_path||'do_query');
-- Put in the table as a hidden field to pass on to do_query
htp.formHidden('the_table', the_table);
-- Put in a dummy value, as we cannot DEFAULT NULL a PL/SQL
table.
htp.formHidden('COLS', 'dummy');
for crec in cols loop
-- Create a checkbox for each column. The form field name
-- will be COLS and the value will be the given column name.
-- Will need to use a PL/SQL table to retrieve a set of
-- values like this. Can use the owa_util.ident_arr type
-- since the columns are identifiers.
htp.formCheckbox('COLS',crec.column_name);
htp.print(crec.column_name);
htp.nl;
end loop;
-- Pass a NULL field name for the Submit field; that way, a
-- name/value pair is not sent in. Wouldn't want to do this
-- if there were multiple submit buttons.
htp.formSubmit(NULL, 'Execute Query');
htp.formClose;
htp.bodyClose;
htp.htmlClose;
end;
Invoking this procedure will bring up a page which looks like:
In this example, the user has already selected to query the EMPNO, ENAME, JOB, and SAL columns:
Here is a procedure to process this form submission:
-- DO_QUERY executes the query on the specified columns and
-- tables.The OWA_UTIL.IDENT_ARR datatype is defined as:
-- -- type ident_arr is table of varchar2(30) index by binary_integer
-- create or replace procedure do_query(the_table in varchar2,
cols in owa_util.ident_arr) is
column_list varchar2(32000);
col_counter integer;
ignore boolean;
begin
-- For PL/SQL tables, have to just loop through until you hit
-- no_data_found. Start the counter at 2 since we put in
-- a dummy hidden field.
col_counter := 2;
loop
-- build a comma-delimited list of columns
column_list := column_list||cols(col_counter)||',';
col_counter := col_counter + 1;
end loop;
exception
when no_data_found
then
-- strip out the last trailing comma
column_list := substr(column_list,1,length(column_list)-1);
-- print the table - assumes HTML table support
ignore := owa_util.tablePrint(the_table, 'BORDER',
OWA_UTIL.HTML_TABLE,
column_list);
end;
Then after selecting the "Execute Query" button, the user would see:
It is a good idea to use a hidden place-holder variable as the first value if you cannot guarantee that at least one value will be submitted for the PL/SQL table. The reason is that one cannot DEFAULT a PL/SQL table. And a call to this procedure with just one argument (the_table) would cause the Web Agent to generate an error.
Note that the Web Agent can only pass parameters to PL/SQL tables which have a base type of VARCHAR2. This should not provide a significant limitation, as the PL/SQL type VARCHAR2 is the largest PL/SQL datatype with a maximum length of 32767 bytes. The values can then be explicitly converted to NUMBER, DATE, or LONG within a stored procedure (using TO_NUMBER or TO_DATE - no conversion needed for LONGs).
create or replace package overload is
procedure proc1(charval in varchar2);
procedure proc1(numval in number);
end;
create or replace package body overload is
procedure proc1(charval in varchar2) is
begin
htp.print('The character value is '||charval);
end;
procedure proc1(numval in number);
htp.print('The number value is '||numval);
end;
end;
This functionality can be utilized by the Web Agent, but with the restriction that procedures which are overloaded on datatypes should not have the same variable names. For example:
create or replace package overload is
procedure proc1(val in varchar2);
procedure proc1(val in number);
end;
When the Web Agent attempts to determine which procedure to call, it will not be able to distinguish between the two and will generate an error.
This limitation is imposed by the lack of HTML-form datatypes, but should not provide a significant limitation, as the PL/SQL type VARCHAR2 is the largest PL/SQL datatype with a maximum length of 32767 bytes. The values can then be explicitly converted to NUMBER, DATE, or LONG within a stored procedure (using TO_NUMBER or TO_DATE - no conversion needed for LONGs).
The Web Agent makes available the CGI environment variables shown in Table 5 - 2.
| Variable | Variable Meaning |
| AUTH_TYPE | Method used to validate user |
| GATEWAY_INTERFACE | The revision of the CGI specification to which the server complies |
| HTTP_USER_AGENT | The browser the client is using to send the request |
| PATH_INFO | Extra path information given by the client |
| PATH_TRANSLATED | Translated version of PATH_INFO provided by server for mapping |
| REMOTE_HOST | Hostname making the request if it can be determined |
| REMOTE_ADDR | IP address of the remote host making the request |
| REMOTE_USER | Used to authenticate user |
| REMOTE_IDENT | Set to the remote username retrieved from the server |
| SERVER_PROTOCOL | Name and revision of the information protocol used in the request |
| SERVER_SOFTWARE | Name and version of information server software answering the request |
| SERVER_NAME | The server's hostname, or IP address |
| SERVER_PORT | Port number on which the server is running |
| SCRIPT_NAME | Virtual path to the script being executed, used for self-referencing URL |
These environment variables can be accessed from within PL/SQL using the OWA_UTIL.GET_CGI_ENV function, which is documented in Chapter 6,"The Oracle WebServer Developer's Toolkit".
Application errors are specific to the PL/SQL application. All applications written by a programmer should have their own exception handling in the PL/SQL procedure that produces the appropriate output.
Because the Oracle Web Agent does not read the HTML output to determine its content, exception handling errors are transparent. As far as the Web Agent is concerned, if the PL/SQL code generates HTML output, the operation was successful. The user will see whatever exception handling error is generated by the PL/SQL procedure.
System Errors
System errors are detected by the Oracle Web Agent itself. These are errors that occur when the Web Agent is unable to launch the PL/SQL procedure or when a PL/SQL exception is not handled by the stored procedure, causing the exception to be propagated back to the Web Agent as a system error. This causes a standard HTML error document to be returned to the browser.
For example, if the Oracle Web Agent cannot make a connection to the Oracle7 Server, the PL/SQL procedure cannot run and a system error occurs. The Web Agent then returns a default error message to the browser from the HTTP Server, or returns a customized HTML error page (if one was previously configured as part of the Web Agent service using the OWA_ERR_PAGE parameter).
How the Web Agent Knows What Error Page to Generate
The Web Agent checks for the error page field in the OWA service configuration. If a value is found there, the Web Agent looks for the specified page in the file system. If found, that page is returned to the Web client. If the lookup fails, a default error message is returned to the Web client.
Only one custom error page per service can be returned when the Web Agent encounters a system error.
To create a custom HTML error page, use any text or HTML editor you are comfortable with to write your HTML document.
The following is an example of a user-defined HTML error page for a system error:
<HTML> <HEAD> <TITLE>ERROR</TITLE> </HEAD> <BODY> <HR> <H1>Your Request Failed</H1> <HR> Bob's Big Bulldozer Company could not process your request. Please try again later, or call us at 1-800-TRACTOR. Thanks, Bob </BODY> </HTML>
Once the customized HTML error page is created, the entry for OWA error page in the Web Agent Service configuration file must reflect where the new HTML error page is stored. Use the Web Service Administration page to insert or change the OWA error page entry.
A full path must be specified for the customized HTML error page. If a path is not specified, the Web Agent will search for it in the same directory in which the Web Agent executable is stored.
See the section, "Creating or Modifying the Web Agent Service," in Chapter 7, "Oracle WebServer Administration Utility," for more information.
Remember, there is only one error log for each Web Agent service. However, if the Web Agent cannot resolve a service name, and there is no OWA_DEFAULT_SERVICE configured, then errors will be written to OWA.err in the default log directory.
The following is an example of an error log file, and the corresponding meaning for each line in the file:
Wed Jun 28 08:14:27 1995 /*Timestamp*/ OWS-05100: Agent : unable to connect due to Oracle error 1017 ORA-01017: invalid username/password; logon denied /*Error stack*/ OWA SERVICE: HR /*Web Agent Service*/ PROCEDURE: hrinfo.emp /*PL/SQL procedure called*/ PARAMETERS: /*parameters used by procedure*/ ============ ENAME: RayBorque
At the command line enter the following: (UNIX only)
$ORACLE_HOME/ows/bin/owa -v
The command will return the following (your result may vary):
Oracle Web Agent Release 1.0.0.0.0
One of the main goals of the Oracle Web Agent is to eliminate the PL/SQL programmer's need to be intimately familiar with World Wide Web technology. To this end, the Oracle WebServer includes a Developer's Toolkit made up of several PL/SQL packages that minimize the programmer's need to know HTML syntax. Although the programmer is still required to have a working knowledge of HTML, by using the Toolkit he or she will not need to hard code the exact syntax of HTML tags into PL/SQL procedures. For instance, a programmer still needs to realize that an anchor tag is needed, but he or she doesn't need to know the exact sequence of characters needed to generate an anchor.
The Oracle WebServer Developer's Toolkit includes the following PL/SQL Packages:
htp.print(htf.italic('Title'));
is functionally equivalent to:
htp.italic('Title');
Every HyperText Function (HTF) has a corresponding HyperText Procedure (HTP). Thus, HTF Functions are generally used only when the programmer needs to nest calls, such as:
htp.header(1,htf.italic('Title'));
In this example, 'htf.italic' will generate the following character string:
<I>Title</I>
This string is then passed to the 'htp.header' procedure and the following line will appear in the HTML document being formatted:
<H1><I>Title</I></H1>
connect <user> / <password> drop package HTF; drop package HTP; drop package OWA_UTIL; drop package OWA;
connect <toolkit owner> / <password> grant execute on HTF to <user>; grant execute on HTP to <user>; grant execute on OWA_UTIL to <user>; grant execute on OWA to <user>;
connect <user>/<password> create synonym HTF for <Toolkit owner>.HTF; create synonym HTP for <Toolkit owner>.HTP; create synonym OWA_UTIL for <Toolkit owner>.OWA_UTIL; create synonym OWA for <Toolkit owner>.OWA;
If this is a security issue for your installation, install the OWA_UTIL package separately for each OWA database user.
The scripts to do this are:
The description of each procedure or function is broken down into the following parts:
| Name | Name of procedure or function |
| Syntax | Actual syntax of procedure or function |
| Purpose | What the procedure or function does |
| Parameters | Parameters passed in |
| Generates | What the procedure or function generates |
Note: Many HTML 3.0 tags have a large number of optional attributes that, if made as individual parameters to the HyperText Procedures or Functions would make the calls quite cumbersome. In addition, some browsers support non-standard attributes. Therefore, each HyperText procedure or function that generates an HTML tag has as its last parameter cattributes, an optional parameter. This parameter enables you to pass the exact text of the HTML attributes in the cattributes field.
For example, the syntax for htp.em is:
htp.em (ctext, cattributes);
A call that uses HTML 3.0 attributes might look like the following:
htp.em('This is an example','ID="SGML_ID" LANG="en"');
This line would generate the following:
<EM ID="SGML_ID" LANG="en">This is an example</EM>
| Syntax | htp.print (cbuf | dbuf | nbuf); |
| Purpose | generates a line in an HTML document. |
| Parameters | cbuf in varchar2 or dbuf in date or nbuf in number |
| Generates | Generates a line in an HTML document based on the value passed to it. |
| Syntax | htp.prn (cbuf | dbuf | nbuf); |
| Purpose | Just like htp.print, but doesn't put a new line at the end of the value submitted. |
| Syntax | htp.prints (ctext); |
| Purpose | Generates a line in an HTML document and replaces all occurrences of the following special characters with escape characters. If not replaced, the special characters would be interpreted as HTML control characters, and would produce garbled output. |
| `<` with '<` `>' with >` `"` with `"` `&` with `&` | |
| Parameters | ctext in varchar2 |
| Generates | Generates a line in an HTML document based on the value passed to it. This procedure is the same as htp.print or htp.p but first replaces the special characters listed above with escape characters. |
Note: Although this section shows HyperText Procedures (HTP), all of them are also available as HyperText Functions (HTF).
| Syntax | htp.htmlOpen; |
| Purpose | Prints a tag that indicates the beginning of an HTML document |
| Parameters | none |
| Generates | <HTML> |
| Syntax | htp.htmlClose; |
| Purpose | Prints a tag that indicates the end of an HTML document |
| Parameters | none |
| Generates | </HTML> |
| Syntax | htp.headOpen; |
| Purpose | Prints a tag that indicates the beginning of the HTML document head |
| Parameters | none |
| Generates | <HEAD> |
| Syntax | htp.headClose; |
| Purpose | Prints a tag that indicates the end of the HTML document head |
| Parameters | none |
| Generates | </HEAD> |
| Syntax | htp.bodyOpen (cbackground, cattributes); |
| Purpose | Prints the tag that identifies the beginning of the body of an HTML document, and allows you to specify an image as the background of the document |
| Parameters | cbackground in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <BODY background="cbackground" cattributes> |
| Example | htp.bodyOpen ('/img/background.gif'); This line produces: <BODY background="background.gif"> |
| Syntax | htp.bodyClose; |
| Purpose | Defines the end of the HTML document body |
| Parameters | none |
| Generates | </BODY> |
Note: Although this section shows HyperText Procedures (HTP), all of them are also available as HyperText Functions (HTF).
| Syntax | htp.title (ctitle); |
| Purpose | Prints an HTML tag with the text you pass in as the value of TITLE. Most Web Browsers display the text value enclosed between <TITLE> and </TITLE> at the top of the document viewing window. |
| Parameters | ctitle in varchar2 |
| Generates | <TITLE>ctitle</TITLE> |
| Syntax | htp.htitle (ctitle, nsize, calign, cnowrap, cclear, cattributes); |
| Purpose | Prints the HTML tags for both the title and a top heading using the title you specify. |
| Parameters | ctitle in varchar2 nsize in integer DEFAULT 1 calign in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TITLE>ctitle</TITLE><Hnsize ALIGN="calign" NOWRAP CLEAR="cclear" cattributes>ctitle</Hnsize> |
| Note that htp.htitle produces non-standard HTML. However, most Web Browsers accept it. To produce standard HTML, the <TITLE> tag must be in the HEAD section of the page, and the level heading tag (<Hn>) must be in the BODY section. For example: htp.headOpen; htp.title(...); htp.headClose; htp.bodyOpen; htp.header (1,...)); ... | |
| Syntax | htp.base; |
| Purpose | Prints an HTML tag that records the URL of the document |
| Parameters | none |
| Generates | Inserts absolute pathname of current document. |
| Syntax | htp.isindex (cprompt, curl); |
| Purpose | Creates a single entry field with a prompting text, such as "enter value," then sends that value to the URL of the page or program. |
| Parameters | cprompt in varchar2 DEFAULT NULL curl in varchar2 DEFAULT NULL |
| Generates | <ISINDEX PROMPT="cprompt" HREF="curl"> |
| Syntax | htp.linkRel (crel, curl, ctitle); |
| Purpose | Prints the HTML tag that gives the relationship described by the hypertext link from the anchor to the target. This is only used when the HREF attribute is present. |
| Parameters | crel in varchar2 curl in varchar2 ctitle in varchar2 DEFAULT NULL |
| Generates | <LINK REL="crel" HREF="curl" TITLE="ctitle"> |
| Syntax | htp.linkRev (crev, curl, ctitle); |
| Purpose | Gives the relationship described by the hypertext link from the target to the anchor. This is the opposite of htp.linkRel. |
| Parameters | crev in varchar2 curl in varchar2 ctitle in varchar DEFAULT NULL |
| Generates | <LINK REV="crev" HREF="curl" TITLE="ctitle"> |
| Syntax | htp.meta (chttp_equiv, cname , ccontent); |
| Purpose | Prints an HTML tag t hat identifies and embeds document meta-information that supplies the Web browser with information about the objects returned in HTTP. |
| Parameters | chttp_equiv in varchar2 cname in varchar2 ccontent in varchar2 |
| Generates | <META HTTP-EQUIV="chttp_equiv" NAM
="cname" CONTENT="ccontent"> |
| Example | htp.meta ('Refresh', NULL, 120); This line produces: <META HTTP-EQUIV="Refresh" CONTENT=120> |
| which on some Web browsers will cause the current URL to be reloaded automatically every 120 seconds. | |
Note: Although this section shows HyperText Procedures (HTP), all of them are also available as HyperText Functions (HTF).
| Syntax | htp.line (cclear, csrc, cattributes); |
| Purpose | Prints the HTML tag that generates a line in the HTML document. CSRC enables you to specify a custom image as the source of the line. |
| Parameters | cclear in varchar2 DEFAULT NULL csrc in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <HR CLEAR="cclear" CSRC="csrc" cattributes> |
| Syntax | htp.nl (cclear, cattributes); |
| Purpose | Prints the HTML tag that inserts a new line |
| Parameters | cclear in varchar2 DEFAULT NULL, cattributes in varchar2 DEFAULT NULL, |
| Generates | <BR CLEAR="cclear" cattributes> |
| Syntax | htp.header (nsize, cheader, calign, cclear, cattributes); |
| Purpose | Prints the HTML tag for a heading level, with the value of the heading level assigned in the parameter. Valid levels are 1 through 6. |
| Parameters | nsize in integer cheader in varchar2 calign in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <Hnsize ALIGN="calign" NOWRAP CLEAR="cclear" cattributes>cheader</Hnsize> |
| Example | htp.header (1,'Overview'); produces <H1>Overview</H1> |
| Syntax | htp.anchor (curl, ctext, cname, cattributes); |
| Purpose | Prints the HTML tag for an anchor to be the start or end destination of a hypertext link. This anchor can accept several attributes, but either HREF or NAME is required. HREF specifies where to link to. NAME allows this tag to be a target of a hypertext link. |
| Parameters | curl in varchar2 ctext in varchar2 cname in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <A HREF="curl" NAME="cname" cattributes>ctext</A> |
| Syntax | htp.mailto (caddress, ctext, cname, cattributes); |
| Purpose | Prints the HTML tag for an anchor with 'mailto' concatenated ahead of the mail address argument. |
| Parameters | caddresss in varchar2 ctext in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <A HREF="mailto:caddress" cattributes>ctext</A> |
| Example | htp.mailto('pres@white_house.gov','Send Email to the President'); prints <A HREF="mailto:pres@white_house.gov">Send Email to the President</A> |
| Syntax | htp.img (curl, calign, calt, cismap, cattributes); |
| Purpose | Prints an HTML tag that signals the browser to load an image to be placed into the HTML page. ALT allows you to specify alternate text to be shown while the image is being loaded, or instead of the image if the browser does not support images. The ISMAP attribute indicates that the image is an image map. |
| Parameters | curl in varchar2 DEFAULT NULL calign in varchar2 DEFAULT NULL calt in varchar2 DEFAULT NULL cismap in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <IMG SRC="curl" ALIGN="calign" ALT="calt" ISMAP cattributes> |
| Syntax | htp.para; |
| Purpose | Prints an HTML tag that indicates that the text previous to it should be formatted as a paragraph. |
| Parameters | none |
| Generates | <P> |
| Syntax | htp.paragraph (calign, cnowrap, cclear, cattributes); |
| Purpose | Prints the same HTML tag as htp.para except that parameters pass in exact alignment, leading, wrapping , and attributes. |
| Parameters | calign in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <P ALIGN="calign" NOWRAP CLEAR="cclear" cattributes> |
| Syntax | htp.address (cvalue, cnowrap, td valign=top, cattributes); |
| Purpose | Prints an HTML tag that enables you to specify address, author and signature of document |
| Parameters | cvalue in varchar2 cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <ADDRESS CLEAR="cclear" NOWRAP cattributes>cvalue</ADDRESS> |
| Syntax | htp.comment (ctext); |
| Purpose | Prints an HTML tag that allows you to store comments or lines in HTML pages. These comments are not visible to the end user. |
| Parameters | ctext in varchar2 |
| Generates | <!-- ctext --> |
| Syntax | htp.preOpen (cclear, cwidth, cattributes); |
| Purpose | Prints an HTML tag that indicates the beginning of preformatted text in the body of the HTML page. |
| Parameters | cclear in varchar2 DEFAULT NULL cwidth in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| <PRE CLEAR="cclear" WIDTH="cwidth" cattributes> | |
| Syntax | htp.preClose; |
| Purpose | Prints an HTML tag that ends the preformatted section of text. |
| Parameters | none |
| Generates | </PRE> |
| Syntax | htp.blockquoteOpen (cnowrap, cclear, cattributes); |
| Purpose | Prints an HTML tag that precedes a paragraph of quoted text. |
| Parameters | cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL; |
| Generates | <BLOCKQUOTE CLEAR="cclear" NOWRAP cattributes> |
| Syntax | htp.blockquoteClose; |
| Purpose | Ends the <BLOCKQUOTE> section of quoted text. |
| Parameters | none |
| Generates | </BLOCKQUOTE> |
| Syntax | htp.listHeader (ctext, cattributes); |
| Purpose | Prints an HTML tag at the beginning of the list |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <LH cattributes>ctext</LH> |
| Syntax | htp.listItem (ctext, cclear, cdingbat, csrc, cattributes); |
| Purpose | Prints an HTML tag that formats a listed item. |
| Parameters | ctext in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cdingbat in varchar2 DEFAULT NULL csrc in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <LI CLEAR="cclear" DINGBAT="cdingbat" SRC="csrc" cattributes>ctext |
| Syntax | htp.ulistOpen (cclear, cwrap, cdingbat, csrc, cattributes); |
| Purpose | Prints an HTML tag that is used to open an unordered list that presents listed items separated by white space and marked off by bullets. |
| Parameters | cclear in varchar2 DEFAULT NULL cwrap in varchar2 DEFAULT NULL cdingbat in varchar2 DEFAULT NULL csrc in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <UL CLEAR="cclear" WRAP="cwrap" DINGBAT="cdingbat" SRC="csrc" cattributes> |
| Syntax | htp.ulistClose; |
| Purpose | Prints an HTML tag that ends the unordered list. |
| Parameters | none |
| Generates | </UL> |
| Syntax | htp.olistOpen (cclear, cwrap, cattributes); |
| Purpose | Prints an HTML tag that is used to open an ordered list that presents listed items marked off with numbers. |
| Parameters | cclear in varchar2 DEFAULT NULL cwrap in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <OL CLEAR="cclear" WRAP="cwrap" cattributes> |
| Syntax | htp.olistClose; |
| Purpose | Prints an HTML tag that ends an ordered list. |
| Parameters | none |
| Generates | </OL> |
| Syntax | htp.dlistOpen (cclear, cattributes); |
| Purpose | Prints an HTML tag that starts a definition list |
| Parameters | cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <DL CLEAR="cclear" cattributes> |
| Syntax | htp.dlistClose |
| Purpose | Prints an HTML tag that Ends a definition list |
| Parameters | none |
| Generates | </DL> |
| Syntax | htp.dlistDef (ctext, cclear, cattributes); |
| Purpose | Prints an HTML tag that is used to insert terms, and their corresponding definitions in an indented list format. The htp.dlistTerm must immediately follow this tag. |
| Parameters | ctext in varchar2 DEFAULT NULL clear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <DD CLEAR="cclear" cattributes>ctext |
| Syntax | htp.dlistTerm (ctext, cclear, cattributes); |
| Purpose | Prints an HTML tag used to insert the definition term inside the definition list. This tag must immediately follow the htp.dlistDef. |
| Parameters | ctext in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <DT CLEAR="cclear" cattributes>ctext |
| Syntax | htp.menulistOpen; |
| Purpose | Prints an HTML tag that begins a list that presents one line per item, and appears more compact than an unordered list. The htp.listItem will follow this tag. |
| Parameters | none |
| Generates | <MENU> |
| Syntax | htp.menulistClose; |
| Purpose | Prints an HTML tag that ends a menu list. |
| Paramenters | none |
| Generates | </MENU> |
| Syntax | htp.dirlistOpen; |
| Purpose | Prints an HTML tag that begins a directory list. This presents information in a list of items that contain up to 20 characters. Items in this list are typically arranged in columns, typically 24 characters wide. The <LI> or htp.listItem must appear directly after you use this tag. |
| Parameters | none |
| Generates | <DIR> |
| Syntax | htp.dirlistClose; |
| Purpose | Prints an HTML tag that closes the directory list tag, htp.dirlistOpen. |
| Parameters | none |
| Generates | </DIR> |
Character format tags give hints to the browser as to how a character or character string should appear, but each browser determines its actual appearance. For example, the HTML string <STRONG>Here is some text</STRONG> might appear as bold in some browsers, or might flash instead.
If a specific text attribute is necessary, a physical format tag may be necessary. See the section, "Physical Format Tags," for more information.
Note: All the HyperText Procedures (HTP) shown in this section are also available as HyperText Functions (HTF).
| Syntax | htp.cite (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as a citation. Usually rendered as italics. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <CITE cattributes>ctext</CITE> |
| Syntax | htp.code (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as an example of code output. Usually rendered in monospace format, e.g. Courier. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <CODE cattributes>ctext</CODE> |
| Syntax | htp.emphasis (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as requiring typographic emphasis. This tag is equivalent to htp.em. Usually rendered as italics. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <EM cattributes>ctext</EM> |
| Syntax | htp.keyboard (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as text typed in by the user.,which is usually rendered as monospace. This tag is equivalent to htp.kbd. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <KBD cattributes>ctext</KBD> |
| Syntax | htp.sample (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as a sequence of literal characters that must be typed in the exact sequence in which they appear. Usually rendered as monospace font. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <SAMP cattributes>ctext</SAMP> |
| Syntax | htp.strong (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as needing strong typographic emphasis. Usually rendered as bold. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <STRONG cattributes>ctext</STRONG> |
| Syntax | htp.variable (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround as a variable name, or a variable that might be entered by the user. Usually rendered as italics. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <VAR cattributes>ctext</VAR> |
Note: All the HyperText Procedures (HTP) shown in this section are also available as HyperText Functions (HTF).
| Syntax | htp.bold (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround is to be rendered as boldface. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <B cattributes>ctext</B> |
| Syntax | htp.italic (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround is to be rendered as italics. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <I cattributes>ctext</I> |
| Syntax | htp.teletype (ctext, cattributes); |
| Purpose | Prints a pair of HTML tags that specify the text they surround is to be rendered in a fixed width typewriter font, e.g. Courier. |
| Parameters | ctext in varchar2 cattributes in varchar2 DEFAULT NULL |
| Generates | <TT cattributes>ctext</TT> |
Forms can have the following types of elements:
| Syntax | htp.formOpen (curl, cmethod); |
| Purpose | Prints an HTML tag that starts the form. The curl value is required and indicates the query program or server to which the contents of the Form will be sent. The method is either "GET" or "POST." |
| Parameters | curl in varchar2 cmethod in varchar2 DEFAULT 'POST' |
| Generates | <FORM ACTION="curl" METHOD="cmethod"> |
| Syntax | htp.formClose; |
| Purpose | Prints an HTML tag that closes the <FORM> tag |
| Parameters | none |
| Generates | </FORM> |
| Syntax | htp.formCheckbox (cname, cvalue, cchecked, cattributes); |
| Purpose | Prints an HTML tag that inserts a checkbox which the user can toggle off or on. |
| Parameters | cname in varchar2 cvalue in varchar2 DEFAULT 'on' cchecked in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="checkbox" NAME="cname" VALUE="cvalue" CHECKED cattributes> |
| Syntax | htp.formHidden (cname, cvalue, cattributes); |
| Purpose | Prints an HTML tag that sends the content of a field along with a submitted form. The field is not visible to the end user. |
| Parameters | cname in varchar2 cvalue in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="hidden" NAME="cname" VALUE="cvalue" cattributes> |
| Syntax | htp.formImage (cname, csrc, calign, cattributes); |
| Purpose | Prints an HTML tag that creates an image field that can be clicked on causing the Form to be immediately submitted. The coordinates of the selected point are measured in pixels, and returned (along with other contents of the form) in two name/value pairs. The x-coordinate is submitted under the name of the field with ".x" appended, and the y-coordinate with the ".y" appended. Any value attribute is ignored. The image itself is specified by the SRC attribute. |
| Parameters | cname in varchar2 csrc in varchar2 calign in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL; |
| Generates | <INPUT TYPE="image" NAME="cname" SRC="csrc" ALIGN="calign" cattributes> |
| Syntax | htp.formPassword (cname, csize, cmaxlength, cvalue, cattributes); |
| Purpose | Prints an HTML tag that creates a single line text entry field. Text will not be displayed as it is entered. When the user enters a password, characters are represented by asterisks on single line text entry field. |
| Parameters | cname in varchar2 csize in varchar2 cmaxlength in varchar2 DEFAULT NULL cvalue in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="password" NAME="cname" SIZE="csize" MAXLENGTH="cmaxlength" VALUE="cvalue" cattributes> |
| Syntax | htp.formRadio (cname, cvalue, cchecked, cattributes); |
| Purpose | Prints an HTML tag that inserts a radio button on the HTML Form. Used to create a set of radio buttons, each representing a different value, only one of which will be toggled on by the user. Each radio button field should have the same name. Only the selected radio button will generate a name/value pair in submitted data area. This will require an explicit VALUE attribute. |
| Parameters | cname in varchar2 cvalue in varchar2 cchecked in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="radio" NAME="cname" VALUE="cvalue" CHECKED cattributes> |
| Syntax | htp.formReset (cvalue, cattributes); |
| Purpose | Prints an HTML tag that creates a RESET button that, when selected, resets all the form fields to their initial values. |
| Parameters | cvalue in varchar2 DEFAULT 'Reset' cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="reset" VALUE="cvalue" cattributes> |
| Syntax | htp.formSubmit (cname, cvalue, cattributes); |
| Purpose | Prints an HTML tag that creates a button that, when selected, submits the form. If a SUBMIT button is selected to submit the Form, and that button has a name attribute specified, the submit button then contributes a name/value pair to the submitted data. |
| Parameters | cname in varchar2 cvalue in varchar2 DEFAULT 'Submit' cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="submit" NAME="cname" VALUE="cvalue" cattributes> |
| Syntax | htp.formText (cname, csize, cmaxlength, cvalue, cattributes); |
| Purpose | Prints an HTML tag that creates a field for a single line of text. |
| Parameters | cname in varchar2 csize in varchar2 DEFAULT NULL cmaxlength in varchar2 DEFAULT NULL cvalue in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <INPUT TYPE="text" NAME="cname" SIZE="csize" MAXLENGTH="cmaxlength" VALUE="cvalue" cattributes> |
| Syntax | htp.formSelectOpen (cname, cprompt, nsize, cattributes); |
| Purpose | Prints an HTML tag that begins a Select list of alternatives. Contains the attribute NAME which specifies the name that will be submitted as a name/value pair. |
| Parameters | cname in varchar2 cprompt in varchar2 DEFAULT NULL nsize in integer DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | cprompt <SELECT NAME="cname" SIZE="nsize" cattributes> |
| Example | htp.formSelectOpen('greatest_player'; 'Pick the greatest player:'); htp.formSelectOption('Messier'); htp.formSelectOption('Howe'); htp.formSelectOption('Hull');. htp.formSelectOption('Gretzky');. htp.formSelectClose; generates: Pick the greatest player: <SELECT NAME="great player"> <OPTION>Messier <OPTION>Howe <OPTION>Hull <OPTION>Gretzky </SELECT> |
| Syntax | htp.formSelectOption (cvalue, cselected, cattributes); |
| Purpose | Prints an HTML tag that represents one choice in the Select element. |
| Parameters | cvalue in varchar2 cselected in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <OPTION SELECTED cattributes>cvalue See example for htp.formSelectOpen. |
| Example | See htp.formSelectOpen. |
| Syntax | htp.formSelectClose; |
| Purpose | Prints an HTML tag that ends a Select list of alternatives. |
| Parameters | none |
| Generates | </SELECT> |
| Example | See htp.formSelectOpen. |
| Syntax | htp.formTextarea (cname, nrows, ncolumns, calign, cattributes); |
| Purpose | Prints an HTML tag that creates a text field that has no predefined text in the text area. Used to enable the user to enter several lines of text. |
| Parameters | cname in varchar2 nrows in integer ncolumns in integer calign in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TEXTAREA NAME="cname" ROWS="nrows" COLS="ncolumns" ALIGN="calign" cattributes></TEXTAREA> |
| Syntax | htp.formTextareaOpen (cname, nrows, ncolumns, calign, cattributes); |
| Purpose | Prints an HTML tag that opens a text area where you can insert predefined text that will always appear in the text field. |
| Parameters | cname in varchar2 nrows in integer ncolumns in integer calign in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TEXTAREA NAME="cname" ROWS="nrows" COLS="ncolumns" ALIGN="calign" cattributes> |
| Syntax | htp.formTextareaClose; |
| Purpose | Prints an HTML tag that ends TextArea field |
| Parameters | none |
| Generates | </TEXTAREA> |
Note: All the HyperText Procedures (HTP) shown in this section are also available as HyperText Functions (HTF).
| Syntax | htp.tableOpen (cborder, calign, cnowrap, cclear, cattributes); |
| Purpose | Prints an HTML tag that begins an HTML table. |
| Parameters | cborder in varchar2 DEFAULT NULL calign in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cclear in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL; |
| Generates | <TABLE BORDER NOWRAP ALIGN="calign" CLEAR="cclear" cattributes> |
| Syntax | htp.tableClose; |
| Purpose | Prints an HTML tag that ends an HTML table. |
| Parameters | none |
| Generates | </TABLE> |
| Syntax | htp.tableCaption (ccaption, calign, cattributes); |
| Purpose | Prints an HTML tag that places a caption in the inserted table. |
| Parameters | ccaption in varchar2 DEFAULT NULL calign in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <CAPTION ALIGN="calign" cattributes>ccaption</CAPTION> |
| Syntax | htp.tableRowOpen (calign, cvalign,cdp, cnowrap, cattributes); |
| Purpose | Prints an HTML tag that inserts a row tag into a table. |
| Parameters | calign in varchar2 DEFAULT NULL cvalign in varchar2 DEFAULT NULL cdp in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TR ALIGN="calign" VALIGN="cvalign" DP="cdp" NOWRAP catttributes> |
| Syntax | htp.tableRowClose; |
| Purpose | Prints an HTML tag that ends a row in a table. |
| Parameters | none |
| Generates | </TR> |
| Syntax | htp.tableHeader (cvalue, calign, cdp, cnowrap, crowspan, ccolspan, cattributes); |
| Purpose | Prints an HTML tag that inserts a table header. |
| Parameters | cvalue in varchar2 DEFAULT NULL calign in varchar2 DEFAULT NULL cdp in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL crowspan in varchar2 DEFAULT NULL ccolspan in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TH ALIGN="calign" DP="cdp" ROWSPAN="crowspan" COLSPAN="ccolspan" NOWRAP cattributes>cvalue</TH> |
| Syntax | htp.tableData (cvalue, calign, cdp, cnowrap, cattributes); |
| Purpose | Prints an HTML tag that inserts data into the rows and columns of a selected table. |
| Parameters | cvalue in varchar2 DEFAULT NULL calign in varchar2 DEFAULT NULL cdp in varchar2 DEFAULT NULL cnowrap in varchar2 DEFAULT NULL cattributes in varchar2 DEFAULT NULL |
| Generates | <TD ALIGN="calign" DP="cdp" ROWSPAN="crowspan" COLSPAN="ccolspan" NOWRAP cattributes>cvalue</TD> |
| Syntax | owa_util.signature; |
| Purpose | Prints an HTML line followed by a signature line on the HTML document. |
| Parameters | none |
| Generates | Prints a signature line in the HTML document that might look like the following: "This page was produced by the Oracle Web Agent on August 9, 1995 09:30" |
| Syntax | owa_util.signature (cname); |
| Purpose | Allows the programmer to create a signature line on the bottom of the HTML document that has a hypertext link to view the PL/SQL source for that procedure. |
| Parameters | cname in varchar2 |
| Generates | Prints a signature line on the bottom of the HTML document that has a link to the actual PL/SQL source for that procedure. The link calls the procedure showsource. The line would look like the following: |
| "This page was produced by the Oracle Web Agent on 6/14/95 09:30" View PL/SQL Source | |
| Syntax | owa_util.showsource (cname); |
| Purpose | Prints the source of the specified PL/SQL procedure, function, or package. If a procedure or function which belongs to a package is specified, then the entire package is displayed. |
| Parameters | cname in varchar2 |
| Generates | Generates the source code of the specified PL/SQL procedure. |
| Syntax | owa_util.showpage; |
| Purpose | This procedure allows a user to view the output of a PL/SQL procedure call from SQL*Plus, SQL*DBA, or Oracle Server Manger. Specifically, it allows one to view the output of the HTP procedures. This method is useful for generating pages filled with static data. |
| Note that this procedure uses dbms_output and thus is limited to 255 characters per line and an overall buffer size of 1,000,000 bytes. | |
| Parameters | cname in varchar2; |
| Generates | One can use this procedure to generate static pages in SQL*Plus that can then be accessed as a standard HTML page. For example: |
| SQL>set serveroutput on SQL>spool gretzky.html SQL>execute hockey.pass('Gretzky") SQL>execute owa_util.showpage SQL>exit | |
| This would generate an HTML page which could be accessed from Web clients. | |
| Syntax | owa_util.GET_CGI_ENV(param_name in varchar2); |
| Purpose | Allows programmer to retrieve the value of the specified CGI environment variable in the PL/SQL procedure. |
| Note that param_name is case-insensitive. | |
| Parameters | param_name in varchar2 |
| Returns | Returns value of specified CGI environment variable for PL/SQL procedure. If the value is not set, returns null |
| Syntax | owa_util.PRINT_CGI_ENV; |
| Purpose | Enables programmer to print all of the CGI environment variables made available by the Web Agent to the PL/SQL procedures. This utility is good for testing purposes. |
| Parameters | none |
| Generates | Prints CGI environment variables made available by the Web Agent to the PL/SQL procedures. |
| Syntax | owa_util.MIME_HEADER; |
| Purpose | Enables programmer to change the default MIME header which the Web Agent returns. This MUST come before any htp.print or htp.prn calls in order to signal the Web Agent not to use the default. |
| Parameters | ccontent_type in varchar2 |
| Generates | Content-type: <ccontent_type>\n\n |
| Syntax | owa_util.GET_OWA_SERVICE_PATH; |
| Purpose | Returns the name of the currently active path with its full virtual path, plus the currently active service. For example, a call to get_owa_service_path could return /ows-bin/myservice/owa/. |
| Parameters | none |
| Returns | The service path. The datatype is varchar2. |
| Syntax | owa_util.tablePrint; |
| Purpose | Enables programmers to print Oracle tables as either preformatted or HTML tables, depending upon Web browser capabilities. Note that RAW COLUMNS are supported, however LONG RAW are not. References to LONG RAW columns will print the result 'Not Printable'. In this case, cattributes is the second, rather than the last, parameter. |
| Parameters | ctable in varchar2 cattributes in varchar2 DEFAULT NULL ntable_type in integer DEFAULT HTML_TABLE ccolumns in varchar2 DEFAULT `*` cclauses in varchar2 DEFAULT NULL ccol_aliases in varchar2 DEFAULT NULL nrow_min in number DEFAULT 0 nrow_max in number DEFAULT NULL |
| Note that ntable_type can be either OWA_UTIL.HTML_TABLE or OWA_UTIL.PRE_TABLE. | |
| Generates | Prints out either a preformatted or HTML table. |
| Returns | True or False as to whether there are more rows available beyond the nrow_max requested. |
| Example | |
create or replace procedure showemps is
ignore_more boolean;
begin
ignore_more := owa_util.tablePrint('emp', 'BORDER', OWA_UTIL.PRE_TABLE);
end;
and requesting a URL like this example:
http://myhost:8080/ows-bin/hr/owa/showemps
returns to the client:
<PRE>
------------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
------------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | | 30 |
| 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450 | | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 09-DEC-82 | 3000 | | 20 |
| 7839 | KING | PRESIDENT | | 17-NOV-81 | 5000 | | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 12-JAN-83 | 1100 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950 | | 30 |
| 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | | 20 |
| 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | | 10 |
------------------------------------------------------------------------
</PRE>
To view just the employees in department 10, and only their employee ids, names, and salaries, create the following procedure:
create or replace procedure showemps_10 is
ignore_more boolean;
begin
ignore_more := owa_util.tablePrint
('EMP', 'BORDER', OWA_UTIL.PRE_TABLE,
'empno, ename, sal',
'where deptno=10 order by empno',
'Employee Number, Name, Salary');
end;
A request for a URL like http://myhost:8080/ows-bin/hr/owa/showemps_10 would return the following to the client:
<PRE> -------------------------------------
| Employee Number | Name | Salary |
-------------------------------------
| 7782 | CLARK | 2450 |
| 7839 | KING | 5000 |
| 7934 | MILLER | 1300 |
------------------------------------- </PRE>
For browsers which do support HTML tables, to view the department table in an HTML table, create the following procedure:
create or replace procedure showdept is
ignore_more boolean;
begin
ignore_more := owa_util.tablePrint('dept', 'BORDER');
end;
A request for a URL like http://myhost:8080/ows-bin/hr/owa/showdept would return the following to the client:
<TABLE BORDER> <TR> <TH>DEPTNO</TH> <TH>DNAME</TH> <TH>LOC</TH> </TR> <TR> <TD ALIGN="LEFT">10</TD> <TD ALIGN="LEFT">ACCOUNTING</TD> <TD ALIGN="LEFT">NEW YORK</TD> </TR> <TR> <TD ALIGN="LEFT">20</TD> <TD ALIGN="LEFT">RESEARCH</TD> <TD ALIGN="LEFT">DALLAS</TD> </TR> <TR> <TD ALIGN="LEFT">30</TD> <TD ALIGN="LEFT">SALES</TD> <TD ALIGN="LEFT">CHICAGO</TD> </TR> <TR> <TD ALIGN="LEFT">40</TD> <TD ALIGN="LEFT">OPERATIONS</TD> <TD ALIGN="LEFT">BOSTON</TD> </TR> </TABLE>
which a Web browser can format to look like this:
| DEPTNO | DNAME | LOC |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
Here is an example of customized packages using non-standard <BLINK> and imaginary <SHOUT>tags:
create package nsf as
function blink(cbuf in varchar2) return varchar2;
function shout(cbuf in varchar2) return varchar2;
end;
create package body nsf as
function blink(cbuf in varchar2) return varchar2 is
begin return ('<BLINK>' || cbuf || '</BLINK>');
end;
function shout(cbuf in varchar2) return varchar2 is
begin return ('<SHOUT>' || cbuf || '</SHOUT>');
end;
end;
create package nsp as
procedure blink(cbuf in varchar2);
procedure shout(cbuf in varchar2);
end;
create package body nsp as
procedure blink(cbuf in varchar2) is
begin htp.print(nsf.blink(cbuf)); end;
procedure shout(cbuf in varchar2) is
begin htp.print(nsf.shout(cbuf)); end;
end;
Now you can begin to use these procedures and functions in your own procedure.
create procedure nonstandard as
begin
nsp.blink('Gee this hurts my eyes!');
htp.print('And I might ' || nsf.shout('get mad!'));
end;
For more examples of using the Developer's Toolkit, see the section, "Passing Parameters to PL/SQL" in Chapter 5 ,"Oracle Web Agent".
To navigate to the Administration Utility, click Administration from the Oracle WebServer home page. The utility is divided into the following three areas:
The procedure is as follows:
1. Choose the database to which you want to apply the operation by clicking the radio button beside its name in the list.
2. Choose the operation you wish to perform and the mode by clicking the appropriate button. The modes are explained in the following sections.
For more information on database startup and shutdown, see the Oracle7 Server Administrator's Guide.
Normal Starts up the database normally. This assumes that the database currently is shut down. If there are any errors during startup, they are reported, and the database is left shut down.
Force Shuts down the database in the Abort mode (as explained under Shutdown Modes below), and then starts it up in the Normal mode.
Normal Shuts down the database cleanly, waiting for current session to disconnect normally. New connections are not allowed while shutdown is in progress.
Immediate Attempts to shut the database down immediately and cleanly. Disconnect any existing user sessions, but wait for active calls to complete before disconnecting. Roll back all uncommitted transactions .
Abort Shuts the database down as quickly as possible, killing all active Oracle7 processes and freeing the memory used. This leaves current transactions unresolved. When you start up the database again, instance recovery is performed automatically. This option is recommended only for emergency use.
For more information on Oracle7 databases including startup and shutdown, see the Oracle7 Server Concepts Manual and the Oracle7 Server Administrator's Guide.
To create a new Oracle Web Agent Service, click Create New Service. The Oracle Web Agent Administration Creation form appears.
To modify, copy, or delete an existing Oracle Web Agent Service, click the name of that service in the list provided. The Oracle Web Agent Administration Modification form appears.
OWA Service: The name of the service you want to create. The name is not case sensitive and must be unique.
OWA Database User: The name of the Oracle7 user that will be used to access the database for this service. If OS authentication is used, then the Web Listener must be run with the same effective user as the Oracle7 user. For more information on Oracle7 users, see "CREATE USER" in the Oracle7 Server SQL Reference.
Identified By A radio button that determines whether Oracle7 or the Operating System performs authentication.
OWA User Password: The Oracle7 password for the associated OWA Database User. If OS authentication is be used for the OWA Database User, as determined by the Identified By radio button, you can leave this field blank. Otherwise, the username and password for the OWA Database User are verified by Oracle7 upon submission of a Web Agent service.
If an error occurs when you attempt to submit this form, the password fields are blanked out and must be filled in again. This is to provide the tightest security currently available for your passwords. As a short-cut, you can select the Back button on your Web Browser to return to, and correct, the original submission form.
Confirm Password: This field is used to verify the OWA user password you entered above before recording it.
ORACLE_HOME: The location of the Oracle7 code tree in the file system. This should be the ORACLE_HOME for the database to which this Web Agent service connects, unless the Web Agent service is set up to connect to a remote database (over SQL*Net). In that case, specify the ORACLE_HOME where the Web Agent is installed.
ORACLE_SID: The system ID of the database. You only need this if the Web Agent is to connect to a local database. For more on Oracle7 SIDs, see the Oracle7 Server Administrator's Guide.
SQL*Net V2 Service: The SQL*Net service that is used to indicate which database to connect to. If the database is on the same machine as the Web Listener, then the SQL*Net V2 Service field can be left empty, and an Oracle7 SID specified instead. If both parameters are specified, the SQL*Net V2 Service takes precedence. If SQL*Net V1 is installed on this machine, then a SQL*Net V1 Connect String may also be specified here. For more on the SQL*Net V2 service, see Understanding SQL*Net.
Authorized Ports: To provide security for your Web Agent service, use the Authorized Ports field to list (space-delimited) the valid Web Listener network ports for the Web Agent to service. This field is required, and the network ports must be listed explicitly.
Log File Directory: The directory where the Oracle Web Agent writes its error file. The error file will have the name <service name>.err.
If the Web Agent is unable to resolve a service name, and no service called OWA_DEFAULT_SERVICE exists, then it will write error and logging information to the file owa.err.
The default value for the Log File Directory is the log directory under the OWS directory within ORACLE_HOME.
Note that these log files are different from those used by the Oracle7 Server for database recovery. The latter are explained in the Oracle7 Server Concepts Manual.
HTML Error Page : If the Oracle Web Agent is unable to process a request, it returns this HTML page to the user making the request, and at the same time writes detailed errors and debugging information to the error message file in the "Log File Directory". If this field is left blank, a default error message is returned to the client.
NLS Language : This field specifies the NLS_LANG parameter, which specifies the character set to be used. If this field is left blank, then the Web Agent administration program looks it up upon service submission.
Create OWA Database User : Create an Oracle7 user using the OWA user name and OWA user password provided. If the Identified by ... Operating System radio button is selected, then the user is created as "identified externally", which means that the operating system, rather than Oracle, performs user authentication. In that case, this value is ignored.
Change OWA Database User Password: Change the Oracle7 Database user's existing password to that specified by the OWA User Password. If the Identified by ... Operating System radio button is selected, then the user will be altered to be "identified externally", which means that the operating system, rather than Oracle, performs user authentication.
Install Oracle WebServer Developer's Toolkit PL/SQL Packages Create the Oracle WebServer Developer's Toolkit PL/SQL packages--OWA, HTP, HTF, and OWA_UTIL--under the schema of the OWA Database User.
After filling in the above fields, click the Submit New Service button to create the service. To activate your new service, go to the Oracle WebServer Administration form and click Configure for the Web Listener you want to associate with this agent. Then go to "Directory Mappings" to create a virtual path, with the service name as the last part of the path, to the OWS/bin directory. Virtual paths are discussed under "Directory Mappings", later in this chapter.
At the bottom of the form are the following three buttons:
Copy Service This creates a copy of the service, leaving the original intact. To do this, simply change the service name (the OWA service field) and any other parameters you wish to alter for the new copy and click this button. If you specify no new password, the original password is copied. After clicking this button, you have two services.
Modify Service This changes the existing service. You can change the name or leave it intact as desired. Make the desired changes and click the button. After clicking this button, you have one service.
Delete Service This destroys the service. For this purpose, it does not matter whether you change any parameters. After clicking this button, you have no service. Calls to the deleted service in existing Web pages or PL/SQL blocks do not produce errors, however. Instead, they are reinterpreted as calls to OWA_DEFAULT_SERVICE.
1. Create a virtual path for the hr service by specifying a Web Listener directory mapping. This mapping must use a value of 'C' to indicate an executable. For more information on setting the directory mappings, see Directory Mappings later in this chapter.
2. Create a realm consisting of those users and groups (UNIX only) who are to have access to the service. For instruction on how to do this, see Specifying the Privileged Users, later in this chapter.
3. Assign the realm to the service and specify either Basic or Digest Authentication. To learn how to perform the assignment, see Performing the Security Assignments, later in this chapter. To find out about Basic and Digest Authentication, see Security Schemes, later in this chapter.
For more information of Web Listener security in general, see "Security Definitions", later in this chapter, and "Client to Listener Security" is Chapter 3.
To perform any of the other operations, choose the appropriate existing Web Listener from the list provided. This list uniquely identifies each Web Listener by the network port on which it is configured to listen. For your convenience, the directory in which the configuration file resides is specified beside the port number. Unlike the port number, this directory need not be unique.
If a Web Listener is running, the word "running" appears beside it in the list.
To modify an existing Oracle Web Listener, find it in the list and click Configure. This takes you to the Configure Extended Parameters form, with the current parameter values filled in. Effect the changes by writing over the current values. For information on the parameters themselves, see "Configure Extended Parameters", later in this chapter.
To start a currently-stopped Oracle Web Listener, find it in the list and click Start.
To stop a currently-running Oracle Web Listener, find it in the list and click Stop.
Note: You might need to perform a manual startup of an existing Web Listener if you inadvertently shut down the Web Listener that the Administration Utility itself is using. To find out how to do this, see "The WLCTL" in Chapter 3.
Host Name This is the name of the machine on which the Oracle Web Listener is to reside. It can either be an actual machine name or an alias in the form of a domain name, such as:
www.oracle.com
Port Number The Oracle Web Listener listens for network messages on a particular port. Use this parameter to specify that port. Numbers can range from 0 to 65535 with numbers below 1024 requiring superuser privileges on UNIX systems.
Configuration Directory This is the directory where the configuration file for the Web Listener is to be located. A default is provided.
Web Listener Root (/) Directory This is the directory that serves as the root (/) directory for the Web Listener. This is often called the "Document Root". It is used to resolve client calls that do not specify a directory. Thus a URL that does not contain a directory, such as http://www.oracle.com/index.html, causes the Web Listener to retrieve the document "index.html" from the directory specified here.
User ID (UNIX only) The User ID specifies the user (or user identification number) under whose privileges the Web Listener executes. To acquire these privileges, the Web Listener must be owned by this user or by root with "Set user ID" set. The User ID can be either a user name or a userid number. The default is the effective ID. Specifying the Oracle owner or an Oracle user with the DBA role is not advised, as this compromises database security. For more information, see "roles" and "Database Administrators" in the Oracle7 Server Concepts Manual.
Group ID (UNIX only) The Group ID specifies the group (or group identification number) under whose privileges the Web Listener executes. To acquire these privileges, the Web Listener must be owned by this group or by root with "Set group ID" set. The Group ID can be either a group name or a GID number. The default is the effective GID.
Note that any Oracle Web Listener that invokes the Oracle Web Agent must have read access to the OWA configuration file (owa.cfg). However, since this file contains Oracle usernames and their corresponding passwords, it is recommended that read permissions only be given to the oracle owner and to one privileged group. This is the group that a Web Listener should run under if it is going to run the Oracle Web Agent.
A convenient group to use for this is the OracleDBA group. However, if you run an Oracle Web Listener under the DBA group, then you will want to place strict controls on who can create CGI programs that will be invoked by that Web Listener. This is because the CGI program will also then run with group DBA privileges, and someone with malicious intent could potentially startup and shutdown your Oracle database, or even remove Oracle files. If you wish to avoid this potential security risk, then you need to do the following:
1. Create a new operating system group.
2. Make the Oracle owner a member of the group created in step 1.
3. Log in as the Oracle owner and change the group ownership of owa.cfg to the group created in step 1.
Note: If at any future point in time you remove the owa.cfg file, you must reset its group ownership when it is recreated.
4. Configure the Group ID parameter of your Oracle Web Listener to be the name of the group created in step 1.
5. Restart your Administration Server (the Web Listener that the Administration Utility is using). This must be done to ensure that the Web Agent Administration program inherits the group created in step 1. Do not change the Group ID for the administration server. It must continue to run as the OracleDBA group.
From this form, you will be able to customize the following:
These parameter sets are explained later in this chapter. For more information, see "Configuration Parameters" in Chapter 3.
Below each parameter set are three buttons. Click one to apply the changes you have made. The buttons are as follows:
Copy Web Listener If you select this button, you must first change the Port Number listed in the Network Parameters section on this form. Then, the entire contents of this form will be used to create a new configuration file and thus a new Oracle Web Listener, leaving the existing Web Listener's configuration file intact. If you reached this form as part of a "create" operation, two Web Listeners are created.
Modify Web Listener Pushing this button will overwrite the existing Oracle Web Listener's configuration file. This operation creates a new Web Listener and deletes the existing one. If you reached this form as part of a "create" operation, one Web Listener is created.
Delete Web Listener Pushing this button will bring up a dialog for you to confirm the deletion. Confirming will do the following:
Host Name The Host Name is the name of the machine on which the Web Listener executes. You must provide this parameter if the Web Listener cannot determine the host name or if the Web Listener executes under an alias. The Web Listener needs this information for logging purposes and to answer SNMP (Simple Network Management Protocol, an Internet standard protocol for checking the status of network components) requests for status. If no host name is specified here, the Web Listener uses the primary Internet address as the default.
Host Address The Host Address is the IP address on which the Web Listener accepts connections. Specifying this address enables a Web Listener residing on a multi-homed machine to designate explicitly the direction from which it receives connections. Multi-homed machines are machines that are configured to have more than one IP address. Specifying ANY, which is the default, indicates that the Web Listener is willing to accept connections arriving on any of its available IP addresses. Leave the parameter set to ANY unless you have a specific reason for changing it.
Port Number The Port Number is the advertised port on which the Web Listener listens for connections. Except for the value PortNumber = 0, any port number is valid provided the Web Listener has the authority to use the requested port. Port numbers between 0 and 1023 are reserved ports and require root (or special) privileges in order to be used. Non-privileged processes can use any port greater than or equal to 1024 unless those ports are reserved for some other use. Ordinarily, the Web Listener will use 80 as the default port if no port number is specified
Max Connect Count The Maximum Connect Count is the maximum number of simultaneous connections to the Web Listener that the Web Listener will accept. After the maximum count is reached, the Web Listener ignores requests for new connections. The default value is 50. The maximum and optimal value is 338.
DNS Resolution Domain Name Service Resolution indicates whether and when the Web Listener will resolve hostnames from IP addresses. If the value of this parameter is set to ALWAYS, then the Web Listener always resolves the name. If it is set to LAZY, then the Web Listener resolves the name only on demand. If it is set to LAZY_WITH_CGI, then the Web Listener also resolves the name on demand for CGI applications. If it is set to NEVER, then the Web Listener never resolves the name, even on demand. The default is NEVER.
Web Listener PID The Web Listener PID file is a one-line file that contains the Web Listener's process identifier. There is no default. This information can be used from a UNIX shell command line to stop the Web Listener, determine its status, or reinitialize a Web Listener that is already running. Normally, however, you use the Administration Utility itself to do these things.
Log File Directory The Log File Directory determines the directory where error and information logs are kept. You need to make sure that this directory actually exists, because the Web Listener will not create one if it does not. The Web Listener needs write permission on this directory and on the files it contains. There is no default.
Info File Name The Log Information File contains periodic and event-driven messages that the Web Listener generates to help WebServer Administrators monitor their web site. The Oracle Web Listener records connections in the industry-standard Common Log Format. This format includes the first line of the client's request, which specifies the action, URL, and protocol version. It also includes such information as the client's host address, whether the request was successful, and how many bytes were transmitted. This information may be used later to analyze what types of accesses are performed by which users, and to check for errors.
Error File Name The Log Error File contains event-driven error messages that the Web Listener generates as it encounters problems with clients, resource access, and various other internal problems. There is no default.
Configuration Directory This is the directory where the configuration file for the Web Listener is to be located. The current value is shown. Change it to change the location of the file to which the changes you are making to all parameters will be stored.
Initial File The Initial File specifies the file that the Web Listener looks for when it is sent a directory URL. For example, if the Initial file is welcome, a request for http://www/stuff causes the Web Listener to return the file /stuff/welcome. The default is initial.
User Directory The User Directory is applicable only to Web Listeners running under the UNIX Operating System. It specifies the subdirectory in a user's directory the Web Listener searches when it receives a URL that starts with /~username/. For example, if at www, this value is set to public_html, then a request for http://www/~charles/initial.htm causes the Web Listener to return the file ~charles/public_html/initial.htm. There is no default.
User Directory Initial File The User Directory Initial File is a special case. For directories accessed with a URL with /~username/, the Oracle Web Listener will not use HTTP incomplete file negotiation and so the Web Listener needs a complete filename in the URL. The default is initial.htm.
Default MIME Type The default MIME (Multimedia Internet Mail Extension) Type is the MIME type for the Web Listener to use if a URL uses a file name extension to request a MIME type the Web Listener does not support. MIME types specify the kind of file, such as image, audio, or video, and the subtype gives the precise file format. The default for this parameter is application/octet-stream, which means the Web Listener will regard any unsupported MIME type as a binary file. If you would like the Web Listener to regard unsupported MIME types as anything other than application/octet-stream, you need to set this parameter.
Default Character Set This is the character set that the Web Listener uses if it does not recognize the character set specified in the URL's file extension. For more information, see "Language Extensions", later in this chapter.
Preferred Language This is the language that the Web Listener uses if the specified or defaulted character set can apply to multiple languages, and the exact language is not specified.. For more information, see "Language Extensions", later in this chapter.
Image Map The contents of this field denote the extension that the Web Listener expects image maps to have. Thus, if your image maps have .map as their extension, set this parameter to map.
An image map is a graphic that is specially configured to act as a selector for one or more hyperlinks, based on where the user clicks on the graphic as displayed on the browser. Oracle WebServer processes image maps internally, without creating a separate process to handle them. An example of an image map would be a map of the world that allows the user to bring up an HTML document on a specific country by clicking on the country on the map. The Oracle Web Listener supports the same image map file formats as are read by the NCSA server.
User ID The User ID specifies the user (or user identification number) under whose privileges the Web Listener executes. To acquire these privileges, the Web Listener must be owned by this user or by root with "Set user ID" set. The User ID can be either a user name or a userid number. The default is the effective ID.
Group ID The Group ID specifies the group (or group identification number) under whose privileges the Web Listener executes. To acquire these privileges, the Web Listener must be owned by this group or by root with "Set group ID" set. The Group ID can be either a group name or a GID number. The default is the effective GID. For further information on the use of Group ID for security, see "Basic Configuration Parameters", earlier in this chapter.
You specify one mapping per line, putting the actual path on the left, the virtual path on the right, and selecting from the pop-up menu one of the following:
File-System Directory R/N/C Virtual Directory --------------------- ----- ----------------- /html R / /prodinfo N /products /final/xr25 R /products/xr25 /misc/people R /employees /misc/routines C /scripts
In the example above, /html is the virtual root directory. This means that any URL that does not contain a directory gets mapped to /html. Thus, the URL http://www/foo.html will cause the Web Listener to look for foo.html in the /html directory. Since this particular mapping is set to be recursive, any URL which contains a directory other than /products, /employees, and /scripts will get mapped to / /html/directory. Thus, the URL http://www/foo/bar.html will cause the Web Listener to look for bar.html in the /html/foo directory. Since /prodinfo is set to non-recursive, any URL which contains a subdirectory off of /products causes an error. The exception would be a URL that contains /products/xr25, because the third line maps this virtual directory to /final/xr25. The last line is an example of a mapping to a directory that contains CGI and other scripts. Thus, the URL http://www/scripts/baz will cause the Web Listener to execute /misc/routines/baz.
Note: If you change the directory mappings, you must reload the Web Listener to put the new mappings into effect. To do this, use the WLCTL utility, as described under "WLCTL" in Chapter 3 of this document.
File caching decreases the access time required for commonly-used files, but at the cost of increasing the memory that the WebServer process uses. For this reason, you should not cache very large files unless absolutely necessary.
You can specify files for caching in any of the following three ways:
This section defines the filename extensions that the Web Listener recognizes and the languages and character sets to which those extensions map. Extensions are case-sensitive. Each line in this section of the configuration file contains a language identifier as described in RFC 1766, followed by a character set, such as those described in RFC 1521, followed by one or more extensions that represent this combination of language and character set to the Web Listener. Multiple extensions are separated by blanks. For more information on Oracle Web Listener's support for character sets, see "Oracle Web Listener Language Extensions" in Chapter 3 of this manual.
Language Type Character Set File Extension(s) ------------- ------------- ----------------- en iso-8859-1 eng en unicode-1-1 engU uc fr-CA iso-8859-1 frc fr-FR iso-8859-1 fr jp-JP iso-2022-jp jp jp-JP unicode-1-1-utf-8 jpU
Since character sets are used only on textual files, language extensions differing only in the character set are equivalent for non-textual files. For example, for a Web Listener configured with the type extensions below, the file image.eng.jpg and the file image.uc.jpg both indicate an International English version of an image.
You can find more information and further examples under "Oracle Web Listener Authentication Configuration" in Chapter 3 of this manual.
http://www.oac.uci.edu/indiv/ehood/MIME/MIME.html
This section defines MIME types known to the Web Listener by their file name extensions. Each line in this section names a MIME type and then lists extensions that map to that MIME type. Extensions are case-sensitive, and multiple extensions are separated by blanks.
Click on a security scheme to navigate to the form where specify the users in the manner relevant to that scheme, as described in the second section below. When you have completed these definitions, click Protection Section to navigate to the form where you apply the security schemes by associated the specified users with the protected files. Alternatively, you can scroll down the Security Definitions page to reach all these forms in sequence.
Basic Authentication Basic authentication employs usernames and passwords to restrict access to certain files on the Web Listener. Since Basic authentication sends passwords in the clear across the network, it is not recommended for extremely sensitive information.
Digest Authentication Digest authentication works essentially the same way as Basic authentication, except that it does not send passwords in the clear. Instead, it uses a cryptographic checksum, often called a digest, to encode the password. This scheme is a significant improvement over Basic Authentication and should be used whenever possible. Unfortunately, this scheme depends on the cooperation of the browser to encrypt the password, and therefore does not work with older browsers that don't support this feature.
IP-based Restriction IP-based restriction uses the network Internet Protocol (IP) address of the client to determine whether to allow or deny access to the protected page. Individual IP addresses or entire IP subnetworks may be specified. IP-based restriction is significantly more convenient than Basic and Digest authentication because it avoids the problems associated with passwords, such as keeping the password file secure and users forgetting their passwords. However, it is also less secure than Digest authentication because it is possible for a clever intruder to arrange to falsify his or her network address. Furthermore, many users need proxy servers because they are behind firewalls. In order to allow access to such a user, the IP address of the proxy server needs to be specified. However, specifying the IP address of the proxy server provides access to the protected page to all users employing that proxy server.
Domain-based Restriction Domain-based restriction is similar to IP-based restriction, except that it uses symbolic hostnames rather than network addresses. The use of symbolic hostnames is preferable because it is immune to changes in the underlying network architecture, which can result in changes to network addresses. Domain-based restriction, like IP-based restriction, is more convenient than Basic and Digest authentication, but also less secure than Digest authentication. As in IP-based restriction, it suffers from the problem with proxy servers.
Basic and Digest Authentication For Basic and Digest Authentication, the information consists of a list of users, then groups, and then realms. Users and groups refer to the standard UNIX notion of users and groups. Realms are collections of groups that are assigned the same security privileges. In other words, they constitute a new (higher) level of the Unix grouping hierarchy. For example:
User Name Password --------- -------- mcarey: achtung mloaf: anything chines: pretend pgabriel: shockmonk Group Name User(s) --------- ------- temps: mcarey chines sales: chines mloaf analysts: mloaf pgabriel Realms Group(s) ------ -------- red: temps blue: sales temps green: analysts
In the above example, chines, whose password is pretend, belongs to the sales group, which is part of the blue realm. Thus, any files that are set to be accessible to the blue realm are accessible to chines as long as he types in "pretend" when prompted for his password. When specifying multiple users or groups, please make sure that they are separated by at least one space. Realms are non-exclusive, which is to say, users and groups can belong to multiple realms. A given realm can contain a mixture of users and groups.
IP-based Restriction For IP-based restriction, you define groups of IP addresses that do or do not have access to a set of documents. For example:
IP Group IP Address(es) -------- -------------- spyonly +192.246.238.* +204.4.214.* notspy -192.246.238.* -204.4.214.* +* friends +10.0.0.11 +10.0.0.83 +10.0.0.117
A '+' before an IP address denotes that that particular IP address or subnet should be given access. A '-' before an IP address denotes that that particular IP address or subnet should be denied access. An asterisk, as usual, is a wildcard indicating that the entire subnet--that is, all IP addresses that begin with the preceding numbers--is included. You can later use the group names given here to refer to the entire complex of associated IP addresses, with their permissions or lack of same, and assign file permissions to these groups.
In the above example, documents in the spyonly group are accessible only from sytems within the two specified subnets. Documents in notspy are accessible only from systems outside the two specified subnets. Documents in friends are accessible only from the three specified systems.
Domain-based Restriction Domain-based restriction is similar to IP-based restriction. A list of groups and hostnames are specified listing which hosts should or should not have access to a set of documents. The following is an example of domain-based restriction:
Domain Group Domain Name(s) ------------ -------------- oracle: +*.oracle.com misc: +dilbert.redbull.com -*.redbull.com -liz.lotion.com +*
You later can use the group names given here to refer to the entire complex of associated domain names, just as in IP-based restriction.
In the above example, documents in the group oracle are accessible only to systems within the oracle domain. Documents in misc are accessible to connections from the specific system dilbert.redbull.com, but not any other system in the redbull.com domain. Documents in misc are also accessible to anyone outside of the redbull.com domain as long as they are not connecting from liz.lotion.com.
You can specify files in any of the following three ways:
You can apply one authentication and/or one restriction scheme for each file specification. If you choose to use both, you must choose '&' or '|' from the '&|' pop-up menu to decide whether the user must satisfy both ('&') or only must satisfy either ('|') of the two schemes. Restriction schemes, naturally, are applied without user input.
To specify an authentication scheme, choose Basic or Digest from the pop-up menu. Then specify in the realm column the realm to which the scheme is to apply.
To specify a restriction scheme, choose IP or Domain from the pop-up menu. Then specify in the group column the group of IP addresses or Domain names, as appropriate, to which the scheme is to apply.
Here is an example:
Files Basic/Digest Realm &/| IP/Domain Group ----- ------------ ----- --- --------- ----- /secret/ Basic red | IP spyonly /misc/hot* Basic green & IP spyonly /company/* IP spyonly /update.htm Basic blue
In this example, all of the files in /supersecret/, but not its subdirectories, can be accessed by any user who is either coming from the correct IP address or has a username and password for the red realm. In the second case, all files in /misc/ that start with "hot" can be accessed only by a user who has both the correct IP address and authentication for the green realm. Files in /company/, including subdirectories (as indicated by the asterisk (*)), are protected only by IP address. The single file update.htm is protected only by authorization in the blue realm.
| Cause: | The file may not exist or may not be readable to the signalling program. |
| Action: | Verify the file's existence in the file-system. Check file and directory permissions and ownership, as well as the ownership of the signalling process. |
| Cause: | Usually an operating system error. If the problem had been file permissions or non-existence, error 1 would have been signalled. |
| Action: | Check your operating system log for errors. |
| Cause: | Usually an operating system error. If the problem had been file permissions or non-existence, error 1 would have been signalled. |
| Action: | Check your operating system log for errors. |
| Cause: | The signalling program expected a specific file format which it did not find. |
| Action: | Analyze the file for incorrect formatting. |
| Cause: | The signalling program checked if root.sh completed successfully. |
| Action: | Run root.sh as indicated by your installation guide. |
| Cause: | The operating system was not able to supply the requested amount of memory. |
| Action: | Reduce the number of running programs on the machine and retry the operation. |
| Cause: | This is the generic internal error number for Oracle WebServer program exceptions. This indicates that a process has encountered an exceptional condition. |
| Action: | Report as a bug. The first argument is the internal error number |
| Cause: | The Oracle Web Agent was unable to connect to the specified Database due to an Oracle error. |
| Action: | See the corresponding Oracle error in the Oracle7 Server Messages. Common errors are: invalid username/password - check the Web Agent configuration file; Oracle not available - start up the database. |
| Action: | See the associated Oracle error. |
| Cause: | OWA requires the PATH_INFO environment variable to be set. PATH_INFO is set based on information passed through the URL after the OWA executable name and before the "?" or the end of the URL |
| Action: | Specify a stored procedure name after the OWA executable name in the URL. |
| Cause: | The procedure and parameters passed to OWA don't match any existing stored procedures. |
| Action: | Verify that there is a stored procedure (not function)with no RECORD parameters which matches the parameters passed. If no value was passed for a parameter, be sure that the stored procedure was declared to have a default value for that parameter. Check for typographical errors. |
| Cause: | The procedure and parameters passed to OWA match too many existing stored procedures. |
| Action: | This should only happen if there are overloaded stored procedures, and that in two different versions of the procedure, a given parameter is declared in one as a scalar and the other as a PL/SQL table. |
| Cause: | The Web Server or other invoking application did not set the listed environment variable. |
| Action: | Contact your Web Server or other invoking application vendor. |
| Cause: | The network port that the invoking an HTTP server is listening on is not a valid port for this OWA Service. |
| Action: | Add this port to the list of valid ports for this Service, or access this service through an HTTP server listening on a valid port. |
| Cause: | The piece of the URL just before the Web Agent executable name did not correspond to a Web Agent Service in the OWA configuration file. |
| Action: | Correct the URL, or create the desired service. |
| Cause: | A parameter required by the Oracle Web Agent was not found in the configuration file along with the specified service. |
| Action: | Modify the Web Agent service configuration using the Web Agent administration form. |
OWS--05501 Service name successfully modified
OWS--05502 Service name successfully deleted
OWS--05503 Password for service name successfully modified
OWS--05504 Service name successfully written. Verify the PL/SQL output in filename
| Cause: | The Web Agent Administration program successfully wrote the specified service and performed all actions requested. The program, however, cannot detect errors which may have occurred while installing the PL/SQL. |
| Action: | Check the output in the file specified and verify that there are not unexpected error messages. |
OWS--05521 Service name submission failed because the passwords submitted do not match
| Cause: | The password and the confirm password field do not match. |
| Action: | Retype both passwords and re-submit the form. |
| Cause: | The form submitted indicated that the Web Agent service should use a password to connect to the database, however no password was provided. |
| Action: | Select operating system authentication for this service, or provide a password for the given OWA Database User. |
| Action: | Fill in the specified field and re-submit the form. |
| Cause: | Both of the listed fields were NULL. |
| Action: | Fill in one of the listed fields and re-submit the form. |
| Action: | Choose another name for the service you are attempting to create, or delete the existing service. |
| Action: | See the associated error and take the appropriate action. |
| Cause: | Either the OWA Database User name and password, or the DBA username and password, is not a valid username/password combination for the specified database. |
| Action: | Correct the username and password and re-submit the form. |
| Action: | Choose a different Oracle username and password for the DBA. |
| Cause: | The user chose to modify an existing Web Agent service, however that service does not exist. This error should not occur unless the service to modify was deleted from a different form after this Service Modification Form was generated. |
| Action: | Create a new service. |
| Cause: | The Web Agent Administration package was unable to lookup the NLS Language for the specified database. |
| Action: | Specify the value explicitly or submit a valid dba username and password. |
| Cause: | The Web Agent Administration package was unable to install the WebServer Developer's Toolkit which must be done from Server Manager (line mode) or SQL*DBA. |
| Action: | Install either Server Manager or SQL*DBA in the ORACLE_HOME where the Web Agent administration is installed. |
OWS--05601 Shutd valign=top of database name successful
OWS--05610 Startup of database name failed due to error number
| Action: | See the associated error and take the appropriate action. |
OWS--05620 DB Admin : no database selected
| Action: | Return to the administration screen and select a database. |
| Cause: | A request was made to start up a database, but that database is already running. |
| Action: | If you would like to restart the database, shut it down first or choose Startup Force . |
| Cause: | A request was made to shut down a database, but that database is already shut down. |
| Cause: | The initialization file listed was larger than the maximum allowable size. |
| Action: | Reduce the size of the initialization file. |
OWS--05701 Configuration for Web Listener on port number successfully deleted
OWS--05710 Value submitted for parameter must not be NULL
| Action: | Type a value for the specified parameter and re-submit the form. |
| Cause: | The port specified for Web Listener creation or modification is already in use by the Oracle WebServer Administration Utility. |
| Action: | Choose another port to run one of the Web Listeners on. |
| Cause: | The port specified for service creation or modification is already in use by another Oracle Web Listener. |
| Action: | Choose another port to run one of the Web Listeners on. |
| Action: | Specify a value between the two limits and resubmit the form. |
| Cause: | Ports below that specified require superuser privileges to run a Web Listener on it. |
| Action: | Configure the server to run on a unrestricted port, or set the effective userid for this Web Listener the superuser. |
OWS--05721 Web Listener on port number successfully stopped
OWS--05722 Web Listener on port number successfully stopped
OWS--05725 Web Listener on port number failed to start
| Action: | See the associated error message. |
| Action: | See the associated error message. |
| Action: | See the associated error message. |
OWS--05901 Please enter a value for field name.
| Cause: | The specified field is a required field for registration. |
| Action: | Type a value into the specified field and re-submit the form. |