This is a step by step description of how the Oracle Web Agent works:
Company A would like data stored in its Oracle7 database to be accessible to the public via an Oracle Web Listener. However, different departments within Company A have access to different parts of the database (schemas), which are partitioned by way of user names. Thus, the Human Resources department and the Accounts Payable department use different username/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 username/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 username/password, an ORACLE_HOME parameter, and an ORACLE_SID parameter. When a request from a Web browser comes in, the Web Listener 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:
![[*]](jump.gif)
for more information.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 up the database NLS_LANG 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
, "The Oracle Web Listener," for more information on how the Web Listener recognizes the difference between a CGI program and a static HTML document.
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.
Note that the name of the HTML input variable, in this case "person", has to be the same as the PL/SQL parameter it is to match.
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 the PL/SQL developer cannot guarantee that a value will be passed from a Web Browser for a particular PL/SQL procedure parameter, then it is recommended that he or she give the parameter a default value. 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 part of the procedure's definition, 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).
of this manual). For example:
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 that are overloaded on datatypes should not have the same parameter 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. This consists of the document root concatenated to the PATH_INFO. |
| 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,"The Oracle WebServer Developer's Toolkit".