The PL/SQL Agent uses the environment variables shown below:
These key concepts and tips are:
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.
<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 does the same thing as the previous example, except that instead of populating the QUERY_STRING environment variable with "person=Gretzky," the Web Listener writes "person=Gretzky" to standard input.
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;
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;
Suppose the PL/SQL Agent receives a request to call procedure showvals with no value for "a" and the value of 'Hello' for "b", and there was no DEFAULT NULL clause in 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
There are a number of instances where you can have such multiple values for the same HTML variable. The HTML tag "SELECT" allows users to select from a set of possible values for an HTML form field. If the SIZE parameter of that SELECT tag is greater than one, the form allows multiple selection, and the user can choose to select more than one value. For example, if you ask a user to indicate her hobbies, she may well have more than one. In this case, you pass the multiple values to a single PL/SQL parameter, which must be a PL/SQL table. A PL/SQL table is a data structure similar to an array.
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 brings up a page that looks like this:
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 the following:
If you cannot guarantee that at least one value will be submitted for the PL/SQL table, it is a good idea to use a hidden place-holder variable as the first value. The reason is that you cannot provide a default NULL value for a PL/SQL table, and a call to this procedure with just one argument (the_table) would cause the PL/SQL Agent to generate an error.
Note that the PL/SQL Agent can only pass parameters to PL/SQL tables that 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 32K (32767 bytes). The values can then be explicitly converted to NUMBER, DATE, or LONG within the 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; create or replace package overload is
procedure proc1(val in varchar2);
procedure proc1(val in number);
end;
For example, if the Oracle PL/SQL Agent cannot make a connection to the Oracle7 Server, the PL/SQL procedure cannot run and a system error occurs. The PL/SQL Agent then returns a default error message to the browser or returns a customized HTML error page (if one was previously configured as part of the DCD using the OWA_ERR_PAGE parameter).
Go to the top of the section.
To report any problems or comments, e-mail Oracle WebServer Documentation.