| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Website using Oracle (Long post)
Morning,
I needed to build a dynamic web site thing recently for an asset management system I wrote here at work. Nothing special, a simply enquiry system is all that it is.
If your top level procedure has parameters, then you can wrap the call
to that proc in another web page which allows the user to select or
enter parameters then call the procedure, or, call it and pass the
parameters on the URL, as in
'http://server/pls/dad_name/procedure_name?ename=xxxxx' as with normal
URLs and parameter passing.
Example : Procedure to select all employees (in a certain department, if you like) and build a dynamic web page, note parameters 'ename' and 'dname' :
CREATE OR REPLACE PROCEDURE DeptEmployees (
ename IN VARCHAR2 DEFAULT '%',
dname IN VARCHAR2 DEFAULT '%') AS
MyDepartment DEPARTMENT.dept_name%TYPE;
BEGIN
owa_util.mime_header('text/html');
htp.htmlopen;
htp.headopen;
htp.title('Employee List By Department');
htp.print('<LINK REL="stylesheet" TYPE="text/css"
HREF="/lynxstyle.css">');
htp.headclose;
htp.bodyopen;
htp.img('/lynxlogo.gif');
htp.HEADER(1, 'Employee List By Department');
htp.br;
-- Get the department name once only
SELECT Dept_name
INTO MyDepartment
FROM DEPARTMENT
WHERE dept_id = dname;
Showoneemployee(HeadingsOnly => TRUE);
FOR e IN (SELECT employee_name,
location_name
FROM EMPLOYEE,
LOCATION
WHERE employee_name LIKE UPPER(ename)
AND employee_department_id = dname
AND location_id = employee_location_id
ORDER BY employee_name)
LOOP
Showoneemployee(e.employee_name, MyDepartment,
e.location_name);
END LOOP;
Showoneemployee(TrailerOnly => TRUE);
EXCEPTION
WHEN OTHERS THEN
htp.init;
Error_Page;
and, as the above makes calls to ShowOneEmployee, here is that procedure as well :
CREATE OR REPLACE PROCEDURE ShowOneEmployee (EName IN VARCHAR2 DEFAULT ' ',
DName IN VARCHAR2 DEFAULT
' ',
LName IN VARCHAR2 DEFAULT
' ',
HeadingsOnly IN BOOLEAN
DEFAULT FALSE,
TrailerOnly IN BOOLEAN
DEFAULT FALSE)
AS
EXCEPTION
WHEN OTHERS THEN htp.init; Error_Page;
END;
/
The Error_page proce simply lets me know that 'sh1t happened' on the database, here it is too :
CREATE OR REPLACE PROCEDURE Error_Page AS BEGIN
owa_util.mime_header('text/html');
htp.prn('<html><head><LINK REL="stylesheet" TYPE="text/css"
HREF="/lynxstyle.css">');
htp.prn('<title>TAMDB Error</title></head>' || CHR(10));
htp.prn('<BODY><TABLE>');
htp.prn('<tr><td rowspan="2"><img
htp.prn('<tr><td colspan="2">The server date & time is currently <B>'|| TO_CHAR(SYSDATE, 'dth Month YYYY hh24:mi:ss')||'</B></td></tr>' || CHR(10));
htp.prn('</TABLE></BODY></html>');
END;
/
And here's one wrapped up in a top level search page. This one builds a page showing an input box where the user can type an employee name or part of one, and a selection list where a known department can be selected. The values typed and chosen are passed out of this page to a proc called 'deptemployees' from the 'action' part of the FORM tag, and passed as parameters using the 'name' of the field/selection list on this form (ename and dname). :
CREATE OR REPLACE PROCEDURE Getemployeedepartment AS
BEGIN
owa_util.mime_header('text/html');
htp.htmlopen;
htp.headopen;
htp.title('Enter employee name & department');
htp.print('<LINK REL="stylesheet" TYPE="text/css"
HREF="/lynxstyle.css">');
htp.headclose;
htp.bodyopen;
htp.img('/lynxlogo.gif');
htp.HEADER(1, 'Enter Employee Name & Department');
htp.para;
htp.print('Enter a name, or part of a name and a ''%'' then select a
department and finally, press the search button');
htp.print('<FORM METHOD="post" action="deptemployees">');
htp.tableopen;
htp.tablerowopen;
htp.tabledata('Name', cattributes => 'class="white"');
htp.tabledata('<input TYPE="text" NAME="ename" SIZE="60"
VALUE="%"/>', cattributes => 'class="white"');
htp.tablerowclose;
htp.tablerowopen;
htp.tabledata('Department', cattributes => 'class="white"
size="1"');
htp.print('<td class="white"><Select name="dname" size=1>'); FOR e IN (SELECT dept_id,
dept_name
FROM DEPARTMENT
ORDER BY dept_name)
LOOP
htp.print('<OPTION VALUE="'|| e.dept_id || '">' ||
e.dept_name);
END LOOP;
htp.print('</SELECT></TD>');
htp.tablerowclose;
htp.tableclose;
htp.bodyclose;
htp.htmlclose;
htp.init;
Error_Page;
The DeptEmployees proc is shown above.
Now then, what's a DAD ?
Well, when you first fire up the http server, you get a page with a few
options on it. One of these allows you to create a DAD but I've found
(under 8174) that it doesn't actually work correctly, so I manually
edited the file $ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app and added
the following :
[DAD_tamdb]
connect_string = cmdb.world
password = xxxx
username = this_is_not_a real_password
default_page = home
document_table = sample.wwdoc_document document_path = docs document_proc = sample.wwdoc_process.process_downloadupload_as_long_raw =
;name_prefix = ;always_describe = ;after_proc = ;before_proc =
I think the [DAD_name] has to be in the format given, capital DAD and
lowercase 'name' or it won't work.
The connect string is as per tnsnames.ora on the server that the http
server is running.
Username & passwords are appropriate to the user on the database and is
the user which owns the procedures.
The default page is the name of a proc which displays the 'home' page on
initial contact.
I found a note on Metalink which states that if 'enablesso' is not set to 'No' then it won't work.
The rest of the above is 'standard'.
Hope this helps.
Cheers,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------Received on Wed Jan 29 2003 - 03:13:39 CST
 |
 |