HELP! * GREY=local Full HTML for

LOCAL foilset Master Foilset for Web-Oracle Integration

Given by Gang Cheng, C.W. Ou, Geoffrey C. Fox at CPS616 Basic Information Track for Computational Science on Winter-Spring Semester 96. Foils prepared 4 April 1996
Abstract * Foil Index for this file

See also color IMAGE
The Strengths, Weaknesses and Synergy of Web and Database Technologies
Architectures of Web and Oracle RDBMS Integration
A Technical Overview of The Oracle-Web Integration
using wowstub and PL/SQL
Examples of a Web-based Search Interface for the Phone List Database

Table of Contents for full HTML of Master Foilset for Web-Oracle Integration

1 CPS 616 January-April 1996 Computational Science Track on Base Technology for the Information Age:
Oracle RDBMS and Web Integration
Web/Oracle Module 3: Integration of WWW and Oracle RDBMS
2 Abstract of Web/Oracle Module 3: Integration of WWW and Oracle RDBMS
3 Key points of Web Technology
4 Strengths of Web Technology
5 Weak Points in Web Technology
6 Key points in database technology - I
7 Key points in database technology - II
8 The Synergy of Web Networking and Database Technologies - I
9 Web-Database Synergy - II
10 By integrating a database server into Web technology, a web server can be enhanced with:
11 By integrating Web into database technology, a database server can be enhanced with:
12 NPAC Web-Database Applications
13 Importance for Key Communities
14 Networking and Client-Server Archtecture in WWW - I
15 Networking and Client-Server Archtecture in WWW - II
16 Two Different Strategies for WWW Client-Server Interactions - I
17 Two Different Strategies for WWW Client-Server Interactions - II
18 Basic Features In Database Access -- A SQL-based Query Interface
19 Basic Client-Server model
20 Major Approaches and Components for Interfacing Web into DB Server Environment
21 Basic WWW DB Integration Model
22 The Oracle-Web Integration: A Technical Overview
23 Oracle-Web Integration Architecture
24 Oracle Web Agent
25 Oracle Web Agent Components
26 Oracle Approach to Web Information - I
27 Oracle Approach to Web Information - II
28 Why Use PL/SQL ?
29 Oracle Web Agent: Dynamic Creation of HTML
30 Anatomy of a Web Agent URL
31 The WOW OCI program (wowstub)
32 WebServer Developer's Toolkit
33 Examples of the Use of HTP and HTF - I
34 Examples of the Use of HTP and HTF - II
35 An Web/Oracle Example - Query A Person's Phone Number by Last or First Name From a Web Page
36 Web/Oracle Example Overall Set Up - CGI Script - II
37 The PL/SQL Package For Phone Example - I
38 The PL/SQL Package For Phone Example - II
39 The Web Page - Search Interface - I
40 The Web Page - Search Interface - II
41 More Examples of PL/SQL Procedures for Web/Oracle Application
42 Oracle's Latest WebServer 2.0 - I
43 Oracle's Latest WebServer 2.0 - II

This table of Contents Abstract

HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 1 CPS 616 January-April 1996 Computational Science Track on Base Technology for the Information Age:
Oracle RDBMS and Web Integration
Web/Oracle Module 3: Integration of WWW and Oracle RDBMS

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Instructor: Geoffrey Fox
Version 3 April 96
teamed with Gang Cheng, Chao-Wei Ou
Syracuse University
111 College Place
Syracuse
New York 13244-4100
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 2 Abstract of Web/Oracle Module 3: Integration of WWW and Oracle RDBMS

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
The Strengths, Weaknesses and Synergy of Web and Database Technologies
Architectures of Web and Oracle RDBMS Integration
A Technical Overview of The Oracle-Web Integration
using wowstub and PL/SQL
Examples of a Web-based Search Interface for the Phone List Database
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 3 Key points of Web Technology

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Characteristics
  • Current main components: HTTP; HTML; CGI; Fillout Form
  • Client-server communication model
  • (Flat hierarchical UNIX) File system as the major file (data) management system
main WWW components (before Java, VRML, LiveMedia etc.):
  • HTTP: the transport protocol - performs the request and retrieve functions to display objects
  • HTML: the makeup language - language used to author Web pages
  • URL - Uniform Resource Locator - addressing system for Web documents
  • CGI: an interface to extend HTTP and HTML- allows the Web server to execute a program to produce dynamic HTML documents
  • Fill-Out Form: a set of HTML constructs to allow clients passing run-time input data to server through GUI interface
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 4 Strengths of Web Technology

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Established Internet as the major vehicle in networking industry
Universal, hyperlinked information access and dissemination
Transparent networking navigation and GUI with multimedia information access for information dissemination--- a killer networking application
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 5 Weak Points in Web Technology

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Static, browser-oriented client (unless Java or JavaScript enabled)
Tedious to manage and update (HTML) files on the server side difficult to keep pages of data updated automatically --- modification usually done by a human using word processor or editing tools
A plain UNIX file system as "database" only supports primitive functions such as open,read/write and close
More powerful operations on files are required for an information system which are supposed to provide information instead of raw data.
e.g. need: low-level data manipulation operators such as those provided in RDBMS: select, insert, update, commit, rollback,etc
Meaningful information needs to be generated on-the-fly from raw data
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 6 Key points in database technology - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Characteristics
  • Relational tables in RDBMS as the major data model for tightly controlled information management, retrieval and processing
  • Client-server as the major communication model
  • Main components: search engine; SQL; procedure languages with embedded SQL; GUI application interfaces to DBMS;
main RDBMS components
  • a search engine: the database management system
  • SQL: the query language
  • embedded SQL interfaces: interfaces to extend the SQL non-procedure programming in high-level programming languages
  • database tools: various (GUI) application interfaces to the DBMS
  • client-server as the major communication model
  • customized proprietary network protocols built on TCP/IP as the major transport protocol
  • focus on file management and I/O processing for a powerful text-based searching and data manipulation system
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 7 Key points in database technology - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Strengths
  • Database server stores, manipulates and manages data in a powerful, high functionality information system
  • Supports design of data system with complex relationships
  • Supports complex, precise, customized access requests
  • Easy to automate most data update processes
  • Information retrieved is as current as the database
Weaknesses
  • Network access not universal or portable: employs proprietary network protocols & requires client license for each end user
  • Plain-text -- no links to, support for multimedia
  • Isolated from links to information outside database system
  • Nonstandard browsers (most are line-based) or proprietary forms
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 8 The Synergy of Web Networking and Database Technologies - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Database techniques used in Web technology
  • data storage and data caching
  • index searching
  • data processing
Networking techniques used in database technology
  • distributed database
  • two-phase commit
  • data replication
  • client/server model
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 9 Web-Database Synergy - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Web server integrated with database is enhanced with:
  • Powerful backend text searching engine for complex queries
  • Representation and organization of (often complex) logical relationships among many information entities
  • Optimizable search performance in large information systems
  • Real time creation of up-to-date HTML documents
Database server linked to web server is enhanced with:
  • Ability to share data globally for maximum information dissemination
  • Ability to add data globally for remote collaborations
  • Transparent Web browser interface for remote database server access
  • Universal Web interface supports hypertext-added data retrieval
  • Integrated multi-media information system
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 10 By integrating a database server into Web technology, a web server can be enhanced with:

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
A powerful backend text searching engine supporting complex queries an efficient, reliable and sophisticated data management system supporting representation and organization of logical relationships among information entities
A dynamic data processing engine, provided with opportunity for optimized searching performance in large information systems
A time-dimension added HTML programming engine which displays latest available data.
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 11 By integrating Web into database technology, a database server can be enhanced with:

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
A transparent networking interface for remote database server access
A universal GUI-based search interface for hypertext-added data retrieval
A truly globally-shared data system for maximum information dissemination
  • Note Oracle used to hold "meta-data" and text indices to our video on demand servers
  • video stored in "flat files"
An integrated multi-media information system
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 12 NPAC Web-Database Applications

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Mail databases: internal corporate utility
  • Input from mh mail handler; access password protected
  • Query single folder or all folders simultaneously
    • by date, subject keywords, sender, mail header:
Usenet Newsgroups: http://asknpac.npac.syr.edu/
  • Input from Usenet feed; public access
  • Query single group or multiple newsgroups simultaneously
    • by keywords, date, URL, sender, others
Education databases
  • Living Schoolbook NY State Image database
  • Travel Venture database
  • Whalenet and Environmental K12 databases (under development)
Health care: demo patient record database
Oracle SQL*TextRetrieval full text search of 3 online books
Corporate product databases (under development)
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 13 Importance for Key Communities

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Education
  • Supports school access to organized data in many disciplines
  • Supports multi-school projects doing data collection and sharing
  • Multimedia links provide organization of visual, audio material
Research
  • Provides universal access to major research databases
    • Examples, protein & other biomolecular database (data + applications)
  • Provides universal interfaces for data entry with data validation done by database procedures
  • Can link research databases with online publications or abstracts
Industry
  • Can enhance marketing websites
  • Improves support for distributed dealerships and/or customer helplines
  • Provides framework for internal enterprise systems, groupware
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 14 Networking and Client-Server Archtecture in WWW - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Basic features in HTTP protocol -- a client-server model:
  • Single transaction per connection:
  • Stateless nature of the connection: end-user's previous connection run-time input will not be seen by current connection
  • client & server open the connection --->
  • client sends request messages to server --->
  • server locates requested information and sends
  • response requested back to client ----> client & server close the connection (note each URL accessed represents a separate connection process)
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 15 Networking and Client-Server Archtecture in WWW - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
The Web is Stateless
  • each GET or POST request from a Web browser to the Web server is independent of the other
  • unlike client-server database connections, no concept of a session between a Web browser and Web server
  • no persistence of data between successive GET or POST requests from the same Web browser
Fill-out forms are used for the server to accept clients run-time input from a HTML page
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 16 Two Different Strategies for WWW Client-Server Interactions - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Pre-edited HTML files (links) ---
  • static and plain ASCII read-only files,
  • identified by a file path;
  • exist on disk;
  • no need to process when used by the server (server performance is only determined by disk -> core memory I/O transfer)
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 17 Two Different Strategies for WWW Client-Server Interactions - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
CGI scripts --- programs (shell,perl,programming languages) to
  • Generate data (usually in HTML format) to be returned to the server
  • (which will then be transferred to the requesting client in HTTP protocol);
  • the resultant data are dynamically generated on the fly; pipes are used to feed the output (via STDOUT) to server; server performance is only determined by CPU processing)
CGI is Interface for running external programs on a Web server
  • Web server is configured to realize that any request for a file in a specific directory or with a specific filename extension are to be executed instead of served
  • Allows Web servers to create dynamic documents which only get created when they are requested by a browser
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 18 Basic Features In Database Access -- A SQL-based Query Interface

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
SQL is the standard way to extract(query) data out from the database
  • low-level SQL-based interfaces are available on different vendors RDBMS
Most commonly used one: Embedded SQL in C but we discussed PL/SQL and oraperl
SQL programs: static (compiled) SQL and dynamic (interpreted) SQL
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 19 Basic Client-Server model

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Clients send SQL statements to server --->
Server parses, executes the SQL against the current database --->
returned rows are buffered in server's core memory --->
Server fetches buffered rows and sends them to clients
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 20 Major Approaches and Components for Interfacing Web into DB Server Environment

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Embedded SQL in C + CGI (C program)
Form-based relational querying and text searching
Different approaches such as PL/SQL or oraperl for different vendor's RDBMS systems (Sybase,Oracle,Informix, DB2, etc)
More details can be found at "http://greatwall.npac.syr.edu:1963/PDB/web-db.html"
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 21 Basic WWW DB Integration Model

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * Critical Information in IMAGE
Full HTML Index
Web Server and DB client must be on the same machine
DB client and DB server can be on different machine and a proprietary networking protocol (e.g., SQL*NET for Oracle7) is used
Inter-process Comunication (IPC) is used for the Web server to communicate with DB client on the same machine
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 22 The Oracle-Web Integration: A Technical Overview

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Integrates Oracle7 databases with the Web
Manage Web information effectively
Provides transaction logic and integrity to Web applications
Uses PL/SQL stored procedures to build dynamic HTML documents on the fly
Bring all the benefits of Oracle7 to the Web, including portability, scalability, manageability, and gateway technology
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 23 Oracle-Web Integration Architecture

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * Critical Information in IMAGE
Full HTML Index
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 24 Oracle Web Agent

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Facilitates the creation of dynamic HTML documents via PL/SQL
Provides an easy way to publish existing Oracle data on the World Wide Web
Web data can be stored in an Oracle DB which will make it much simpler to manage and manipulate
Provides utilities which take care of the repetitive tasks associated with building a CGI application
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 25 Oracle Web Agent Components

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
An OCI (Oracle Call Interface) program called wowstub (or owa in Oracle WebServer) which serves as the actual CGI program
Two PL/SQL packages (HTP and HTF) that contain utilities to automatically generate HTML tags
(optional to Oracle WebServer)
  • A PL/SQL package called owa which contains utilities used by the OCI program to set up the CGI environment variables, etc.
  • A utility to administer the Web Agent via HTML forms
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 26 Oracle Approach to Web Information - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Data and SQL Engine
  • use tables, instead of static files, to store data
  • build a schema on data tables to define data and the relationships of different information entities (the relational model)
  • Web pages can be created, managed and delivered in more powerful way
  • SQL operators for data and file processing: Select data from tables to provide accurate and up-to-date information, and data can be entered, updated through a web page to keep a consistent information base
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 27 Oracle Approach to Web Information - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Procedural and programmable mechanism
Use PL/SQL for data manipulation and processing. PL/SQL:
  • Oracle's procedural SQL application development language
  • support stored procedures, database triggers, optimized concurrent data access, and executable programs as meta-data (i.e., programs are stored together with and attached to the data objects to be manipulated on)
  • a programming language between C and SQL: loops, decisions, modules and function calls, subroutines, etc.
  • database server is both a SQL engine and a computing engine
  • programmable HTML pages - program dynamic html pages on the fly
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 28 Why Use PL/SQL ?

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Performance: tends to execute faster than regular SQL code becuse it doesn't need to be parsed and interpreted before executing
Portable across all Oracle platforms: resides completely inside the database thus is 100% portable to any platform that Oracle7 runs on
Modularity: has procedure,package etc mechanisms for software development in modules
Why not? Not portable to other databases!
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 29 Oracle Web Agent: Dynamic Creation of HTML

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * Critical Information in IMAGE
Full HTML Index
Web pages created on the fly to include data dynamically obtained from Oracle7
Oracle7 stored Web related information
Web pages are therefore always up to date
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 30 Anatomy of a Web Agent URL

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Typical URL - http://www.npac.syr.edu/cgi-bin/wow/mypkg.myplsql
http - specifies protocol to use
www.npac.syr.edu - specifies hostname
cgi-bin/wow - specific the OCI program that needs to be executed
mypkg - specifies the PL/SQL package that wow will invoke
myplsql - specifies the PL/SQL procedure that wow will invoke
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 31 The WOW OCI program (wowstub)

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * Critical Information in IMAGE
Full HTML Index
Extract the PL/SQL package and procedure name embedded in URL and log on to the DB
Set up the parameters passed from the Web server so that they are accessible to the PL/SQL procedure
Execute the PL/SQL procedure which extracts data from DB and formats it into an HTML document stored in a PL/SQL table
Take the HTML document and write it to standard output
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 32 WebServer Developer's Toolkit

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
utility packages that simplify the generation of HTML documents from within PL/SQL (HTP and HTF packages)
each HTML tag has a corresponding function and procedure
the procedure generates a line of output in a PL/SQL table
when the entire document is done, the OCI program (wowstub) takes the contents of the PL/SQL table and writes it to standard output
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 33 Examples of the Use of HTP and HTF - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
HyperText Procedures (HTP)
  • A HTP procedure generates a line in an HTML document that contains the HTML tag that corresponds to its name
  • For example, htp.url('http://www.npac.syr.edu',
  • 'NPAC Home Page')
  • actually prints the text:
  • <a href="http://www.npac.syr.edu
  • ">NPAC Home Page</a>
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 34 Examples of the Use of HTP and HTF - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
HTF (HyperText Functions) is the corresponding package which contains PL/SQL functions for generating HTML tags
i.e. it returns HTML string whereas HTP writes HTML
  • Analogous to printf(HTP) versus sprintf(HTF)
  • HTF functions are used only when the programmer needs to nest calls
  • For example, htp.url('http://www.npac.syr.edu',
  • htf.italic('NPAC Home Page'))
  • prints out the text:
  • <a href="http://www.npac.syr.edu">
  • <i>NPAC Home Page</i> </a>
Details about syntax and use of each procedure and function in HTP and HTF can be found from the handout
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 35 An Web/Oracle Example - Query A Person's Phone Number by Last or First Name From a Web Page

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
A PL/SQL package called "example" defined by a Oracle user 'cps616' with password 'xyz'.
  • One procedure "get_phoneno_by_name" is defined in the package.
Assume a host machine runs a Web server (http://myhost) and a Oracle RDBMS server (ORACLE_SID = myoracle_server, ORACLE_HOME=/usr/oracle)
The user 'cps616' has a CGI path as http://myhost/cgi-bin/cps616 in which a CGI script named 'wow' is set up as on following foil:
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 36 Web/Oracle Example Overall Set Up - CGI Script - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
#!/bin/csh
# Specify which Oracle server to use
setenv ORACLE_HOME /usr/oracle
setenv ORACLE_SID nodb
# specify Oracle user and password
setenv WOW_UID 'cps616/xyz'
# point to where the Web Agent executable is located
/usr/oracle/wow-src/wowstub $1
exit
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 37 The PL/SQL Package For Phone Example - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
CREATE PACKAGE EXAMPLE is procedure get_phoneno_by_name(name IN VARCHAR2); -- only one procedure
END EXAMPLE;
CREATE PACKAGE BODY EXAMPLE is
  • CREATE PROCEDURE get_phoneno_by_name(name IN VARCHAR2) IS
  • CURSOR person_cur(cname IN VARCHAR2) IS
  • SELECT last_name,first_name,phone_no,phone_type
    • from person_info_table,phone_list_table WHERE
    • (person_info_table.person_id = phone_list_table.person_id) AND
    • (last_name LIKE ('%' || LOWER(cname) || '%' ) OR
    • first_name LIKE ('%' || LOWER(cname) || '%'));
    • lname person_info_table.last_name%TYPE; -- a variable to hold last name
    • fname person_info_table.first_name%TYPE; -- a variable to hold first name
    • phone phone_list_table.phone_no%TYPE; -- a variable to hold phone no.
    • ptype phone_list_table.phone_type%TYPE; -- a variable to hold phone type
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 38 The PL/SQL Package For Phone Example - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
BEGIN
  • htp.htitle('Query Results');
  • htp.ulistOpen;
  • OPEN person_cur(name); -- open the cursor
  • LOOP; -- Fetch each row matching the query into variables repeatedly
  • FETCH person_cur INTO lname,fname,phone,ptype;
  • EXIT WHEN person_cur%NOTFOUND; -- check end of result buffer
/* print out the query result */
htp.p(htp.item||'The ' || ptype ||' phone no. of ' || fname || ' '
|| lname || ': ' || phone);
END LOOP;
CLOSE person_cur; -- close the cursor after it is done
htp.ulistClose;
END get_person_by_name;
END EXAMPLE;
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 39 The Web Page - Search Interface - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
A HTML form page to accept user search input and invoke the CGI script on the web server to access the database
  • <html><header> <title>A Web/Oracle Phonebook Example</title></header>
  • <body>
  • <form action= "http://myhost/cps616/wow/
  • get_phoneno_by_name" >
  • Enter Last or First Name: <input type = "text" name= "name" value= "" >
  • </form></body></html>
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 40 The Web Page - Search Interface - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Query results are displayed in another Web page as (if user typed in 'geoffrey')
<html><header>
<title>Query Results</title></header>
<body><h1>Query Results</h1>
<ul>
<li>The office phone no. of Geoffrey Fox: 3154434889
<li>The home phone no. of Geoffrey Fox: 3154238422
</ul>
</body>
</html>
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 41 More Examples of PL/SQL Procedures for Web/Oracle Application

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Details of the phone-list example can be found from the handout or at http://pacman.npac.syr.edu:1996
Further application examples can be found at
  • a searchable USENET newsgroups archive - http://asknpac.syr.edu
  • Travel Venture Database and several other databases - http://kayak.npac.syr.edu:1963
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 42 Oracle's Latest WebServer 2.0 - I

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
Overview - The Oracle WebServer is a HyperText Transfer Protocol (HTTP) Internet Server with unprecedented database integration and a powerful development environment.
The Web Listener - This is the portion of the WebServer that interfaces to the local network or the World Wide Web.
The Secure Sockets Layer - Use's SSL with public key encryption.
The Web Server Manager - The set of Web pages you can use to perform most WebServer administration functions.
The CGI Interface - The standard Web mechanism for executing applications on a Web server.
HELP! * GREY=local HTML version of LOCAL Foils prepared 4 April 1996

Foil 43 Oracle's Latest WebServer 2.0 - II

From Master Foilset for Web-Oracle Integration CPS616 Basic Information Track for Computational Science -- Winter-Spring Semester 96. * See also color IMAGE
Full HTML Index
The Web Request Broker (WRB) - The core of the WebServer. An asynchronous request broker with an open API that Oracle WebServer uses to execute applications on the server.
The PL/SQL Agent - The program the Oracle WebServer uses to execute procedures written in PL/SQL, Oracle's application development language, on the Oracle7 Server.
Java - The language for developing distributed network applications. Oracle WebServer enables you to execute Java either on the WebServer itself or on the client's browser.
LiveHTML - A way to embed dynamic content in Web pages. This content can be either other Web pages or the output of scripts run by the Operating System. LiveHTML is an Oracle extension of the NCSA standard Server Side Includes functionality.
Northeast Parallel Architectures Center, Syracuse University, npac@npac.syr.edu

If you have any comments about this server, send e-mail to webmaster@npac.syr.edu.

Page produced by wwwfoil on Tue Feb 18 1997