Full HTML for

Basic foilset PL/SQL and General Web-Oracle Integration

Given by Gang Cheng, C.W. Ou, Geoffrey C. Fox at ARL Database Tutorial on February 98. Foils prepared 7 October 97
Outside Index Summary of Material

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 Oracle WebServer and PL/SQL
Examples of a Web-based Search Interface for the Phone List Database

Table of Contents for full HTML of PL/SQL and General Web-Oracle Integration

 Denote Foils where Image Critical
Denote Foils where Image has important information
Denote Foils where HTML is sufficient
1 Technologies 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 Weaknesses in Web Technology
5 Key points in database technology - I
6 Key points in database technology - II
7 The Synergy of Web Networking and Database Technologies - I
8 Web-Database Synergy - II
9 By integrating a database server into Web technology, a web server can be enhanced with:
10 By integrating Web into database technology, a database server can be enhanced with:
11 Importance for Some Key Communities
12 Networking and Client-Server Archtecture in WWW - I
13 Networking and Client-Server Archtecture in WWW - II
14 Two Different Strategies for WWW Client-Server Interactions - I
15 Two Different Strategies for WWW Client-Server Interactions - II
16 Basic WWW DB Integration Model
17 The Oracle-Web Integration: A Technical Overview
18 Oracle-Web Integration Architecture
19 Oracle Web Agent: Dynamic Creation of HTML
20 The Oracle Web Agent: Web Request Broker - PL/SQL Cartridge
21 Why Use PL/SQL ?
22 Oracle Web Application Developer's Toolkit
23 Oracle Approach to Web Information - I
24 Oracle Approach to Web Information - II
25 Examples of the Use of HTP and HTF - I
26 Examples of the Use of HTP and HTF - II
27 The PL/SQL Package For Phone Example - I
28 The PL/SQL Package For Phone Example - II
29 The Web Page - Search Interface - I
30 Anatomy of a Web Agent URL
31 The Web Page - Search Interface - II
32 More Examples of PL/SQL Procedures for Web/Oracle Application

Outside Index Summary of Material

HTML version of Basic Foils prepared 7 October 97

Foil 1 Technologies for the Information Age:
Oracle RDBMS and Web Integration
Web/Oracle Module 3: Integration of WWW and Oracle RDBMS

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
Gang Cheng, Geoffrey Fox, Nancy McCracken, Marek Podgorny, Chao-Wei Ou
Northeast Parallel Architectures Center
Syracuse University
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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 Oracle WebServer and PL/SQL
Examples of a Web-based Search Interface for the Phone List Database
HTML version of Basic Foils prepared 7 October 97

Foil 3 Key points of Web Technology

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 4 Weaknesses in Web Technology

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
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
HTML version of Basic Foils prepared 7 October 97

Foil 5 Key points in database technology - I

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 6 Key points in database technology - II

From Untitled presentation ARL Database Tutorial -- February 98. *
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 direct support for multimedia
  • Isolated from links to information outside database system
  • Nonstandard browsers (most are line-based) or proprietary forms
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 8 Web-Database Synergy - II

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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.
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 11 Importance for Some Key Communities

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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)
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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)
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 16 Basic WWW DB Integration Model

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 17 The Oracle-Web Integration: A Technical Overview

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 18 Oracle-Web Integration Architecture

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
HTML version of Basic Foils prepared 7 October 97

Foil 19 Oracle Web Agent: Dynamic Creation of HTML

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
Web pages created on the fly to include data dynamically obtained from Oracle7
Oracle7 stored Web related information
HTML version of Basic Foils prepared 7 October 97

Foil 20 The Oracle Web Agent: Web Request Broker - PL/SQL Cartridge

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

Foil 21 Why Use PL/SQL ?

From Untitled presentation ARL Database Tutorial -- February 98. *
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!
HTML version of Basic Foils prepared 7 October 97

Foil 22 Oracle Web Application Developer's Toolkit

From Untitled presentation ARL Database Tutorial -- February 98. *
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 OWA takes the contents of the PL/SQL table and writes it to standard output
HTML version of Basic Foils prepared 7 October 97

Foil 23 Oracle Approach to Web Information - I

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
Data and SQL Engine
  • use tables, instead of static files or CGI-base dynamic pages, 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 dynamic and efficient way
  • Data can be retrieved and mainpulated through Web page to keep the consistency of the database
HTML version of Basic Foils prepared 7 October 97

Foil 24 Oracle Approach to Web Information - II

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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>
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
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;
HTML version of Basic Foils prepared 7 October 97

Foil 29 The Web Page - Search Interface - I

From Untitled presentation ARL Database Tutorial -- February 98. *
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/owa/
  • get_phoneno_by_name" >
  • Enter Last or First Name: <input type = "text" name= "name" value= "" >
  • </form></body></html>
HTML version of Basic Foils prepared 7 October 97

Foil 30 Anatomy of a Web Agent URL

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
Typical URL - http://osprey7.npac.syr.edu:6666/CPS616/owa/
phone_pkg.get_person_by_name
http - specifies protocol to use
osprey7.npac.syr.edu:6666 - specifies hostname
CPS616/owa - specific the OCI program that needs to be executed
phone_pkg - specifies the PL/SQL package that owa will invoke
get_person_by_name - specifies the PL/SQL procedure that owa will invoke
HTML version of Basic Foils prepared 7 October 97

Foil 31 The Web Page - Search Interface - II

From Untitled presentation ARL Database Tutorial -- February 98. *
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>
HTML version of Basic Foils prepared 7 October 97

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

From Untitled presentation ARL Database Tutorial -- February 98. *
Full HTML Index
Details of the phone-list example can be found from the handout or at http://osprey7.npac.syr.edu:3768/
reference-docs/cwou/database
Further application examples can be found at
© 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 Sat Feb 7 1998