Website using Oracle
Wayne Hinch
I am trying to create a web site to connect to a oracle 9i database with
procedures and views in it.
Could anyone please advise me the easiest way to do this.
Any help will be much appricated.
Wayne
Oradba Linux
If you could turn on the http server that comes with oracle 9i database .
You could use pl/sql web toolkit to create some web pages .
Just a suggestion .
I would love to follow this post for other suggestions .
Wayne Hinch
Do you have any sample code to demostrate how it works? As i have created a
form linking to a procedure but it doesn't connect to the database.
Wayne
"Oradba Linux" <oradba...@attbi.com> wrote in message
news:pan.2003.01.29....@attbi.com...
Norman Dunbar
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.
1. Get the HTTP server working correctly.
2. Login to it and create a DAD for your database.
3. Write a procedure in the database and use the HTP.xxxx procedures and
HTF.xxxx functions to create the HTML for the page (examples below)
4. Call the procedure from the web page as
'http://server/pls/dad_name/procedure_name'
5. Pray :o)
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;
END;
/
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
BEGIN
--
-- Do the table headings only
--
IF HeadingsOnly THEN
htp.tableopen;
htp.tablerowopen;
htp.tableheader('Employee name');
htp.tableheader('Department name');
htp.tableheader('Location name');
htp.tablerowclose;
RETURN;
END IF;
--
-- Do the table endings and finish the HTML page only
--
IF TrailerOnly THEN
htp.tablerowopen;
htp.tabledata('End of listing.', ccolspan => '3');
htp.tablerowclose;
htp.tableclose;
htp.bodyclose;
htp.htmlclose;
END IF;
--
-- Do one employee record
--
htp.tablerowopen;
htp.tabledata(EName);
htp.tabledata(DName);
htp.tabledata(LName);
htp.tablerowclose;
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
src="/lynxlogo.gif"></td><td><h1>TAMDB Error Page</h1></td></tr>' ||
CHR(10));
htp.prn('<tr><td><p>TAMDB has encountered a problem which it is
unable to resolve. Please return to the previous page and make a note of
any details you have set up before reporting the problem to Technical
Services Leeds.</p></td></tr>' || CHR(10));
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.tabledata('<input TYPE="submit" VALUE=" Search " />', ccolspan
=> '2', cattributes => 'class="white"');
htp.tablerowclose;
htp.tableclose;
htp.bodyclose;
htp.htmlclose;
EXCEPTION
WHEN OTHERS THEN
htp.init;
Error_Page;
END;
/
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_download
upload_as_long_raw =
upload_as_blob = *
reuse = Yes
connmax = 10
enablesso = No
pathalias = url
pathaliasproc = sample.wwpth_api_alias.process_download
;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.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
Norman Dunbar
'manually' using htp.prn('HTML stuff');' or you can call various parts
of the htp package to do it for you as in 'htp.tablerowopwn;' and so on.
As the examples I posted were my very first (and so far, last) effort,
I've got some in one format and others in the other.
Regards,
Telemachus
"Wayne Hinch" <a...@a.com> wrote in message
news:b16thj$nof$1...@sparta.btinternet.com...
DA Morgan
You are asking for far more information than can be presented in a usenet group.
Go to http://tahiti.oracle.com and look it up.
Daniel Morgan
Tim X
>> "Oradba Linux" <oradba...@attbi.com> wrote in message
>> news:pan.2003.01.29....@attbi.com...
>> > On Tue, 28 Jan 2003 21:43:16 +0000, Wayne Hinch wrote:
>> >
>> > > Hi,
>> > >
>> > > I am trying to create a web site to connect to a oracle 9i
>> > > database with procedures and views in it.
>> > >
>> > > Could anyone please advise me the easiest way to do this.
>> > >
>> > > Any help will be much appricated.
>> > >
>> > > Wayne
>> > If you could turn on the http server that comes with oracle 9i
>> > database . You could use pl/sql web toolkit to create some web
>> > pages . Just a suggestion . I would love to follow this post
>> > for other suggestions .
There are heaps of options - all depends if you want to stick to using
just oracle stuff or you want to use other technologies.
Oracle 8i and above comes with quite extensive web development support
(including its own apache web server) which allows you to use Java
servlets, PL/SQL Servlets, Perl and the PL/SQL web development
toolkits. You also have Oracle's 9IAS which takes all this a little
bit further.
If you just want to be able to access Oracle db from some other web
server and use Oracle just as the database back-end, then you can
pretty much use all the standard tools available which support
database connectivity (e.g. Perl, PHP, Java, ASP etc).
Tim
--
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you
really need to send mail, you should be able to work it out!
Frank
> 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.
>
> 1. Get the HTTP server working correctly.
> 2. Login to it and create a DAD for your database.
> 3. Write a procedure in the database and use the HTP.xxxx procedures and
> HTF.xxxx functions to create the HTML for the page (examples below)
> 4. Call the procedure from the web page as
> 'http://server/pls/dad_name/procedure_name'
> 5. Pray :o)
>
> 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.
>
>
>
>
how about:
create or replace procedure testhtml as
begin
htp.p('Hello World!');
end;
/
for starters?
Frank
PS Have been over this is detail beore - search archives...