Using Oracle8

Chapter 26

Using Other Oracle8 Features and Functionality

• National Language Support
  • Setting Database Character Set
  • Setting National Character Set
  • Setting Initialization File Parameters
  • Setting Session Values
  • Using SQL NLS Options
• Advanced Replication
  • Advanced Replication Configurations
  • Advanced Replication Components
  • Advanced Replication User Accounts
  • Managing Job Queues
  • Preparing for Replication
  • Using Replication Manager
  • Managing Replication
• Spatial Data Cartridge
  • The Spatial Data Model
  • The Spatial Table Structure
  • Using Spatial Data
• Oracle Web Server Cartridges
  • Using Oracle Web Application Server Cartridges
  • Handling Requests
  • The PL/SQL Cartridge
  • The Java Cartridge
• Oracle Advanced Queuing
  • Oracle AQ Components and Terminology
  • Major Features of Oracle AQ

• Use National Language Support (NLS) at the server, session, and operating system level
• Use advanced replication and Replication Manager
• Store and retrieve spatial data with the Spatial Data Cartridge
• Review Oracle Web Application Server architecture
• Configure PL/SQL and Java cartridges
• Use Oracle Advanced Queuing (AQ) to track workflow and applications

National Language Support

National Language Support (NLS) is a feature that enables Oracle database users to interact with the database in their native language. It also enables applications written in different national languages to interact with different NLS configurations.

Oracle's NLS architecture has two main components: language-independent functions used to provide generic language features, and language-dependent data used to work with features for a specific language. Oracle Server uses several encoding schemes to enable NLS capability. In essence, letters typed from a keyboard are displayed onscreen with character encoding. The encoding schemes commonly used by Oracle8 Server are categorized into single-byte 7-bit (7-bit ASCII), single-byte 8-bit (ISO 8859/1), varying-width multi-byte (for Chinese and other Asian languages), and fixed-width multi-byte (a subset of varying-width multi-byte). For more information on encoding schemes and restrictions on character sets, review the Oracle8 Server Reference in the Oracle documentation set.

Oracle8 has a new datatype, NCHAR, which facilitates the existence of two character sets in one database instance. Oracle8 also adds support for fixed-length native Unicode 2.0 and Chinese language support.

Setting Database Character Set

Setting the database character set is something you should do before creating a database instance. All database character data is stored in the character set specified during database creation. Although you may be able to change the character set of objects created later, characters in the data dictionary are stored in the character set used during creation and can't be changed later. The database character set is used for the following items:

• Table and field (column) names
• PL/SQL variables
• Data (LONG, CHAR, VARCHAR, and CLOB datatypes)
• Other SQL and PL/SQL scripting commands

The database set must be derived from 7-bit ASCII or EDCDIC character sets. The database character set can be a varying-width multi-byte character set.

Setting National Character Set

The National Character set is used for handling data stored in the NCHAR, NCLOB, and NVARCHAR2 datatypes. You can use a fixed-width or a varying-width multi-byte character set for the National Character Set.

Setting Initialization File Parameters

You can set NLS parameters as default values for the server. The client software can alter the server default values, if desired. The following parameters in the initialization file (INITSID.ORA) set the default NLS values for the server:

• NLS_LANGUAGE sets up the default language of the database. This language sets up default values for several other NLS variables. For example,

NLS_LANGUAGE ="US7ASCII"

or

NLS_LANGUAGE="GERMAN"

This important parameter is used in several places internally in the Oracle server, including server messages; language for day-and-month functions and their extensions; abbreviations for time such as AM/PM, AD/BC, and so on; writing direction; and default sorting sequence. The value set for NLS_LANGUAGE in the initsid.ora file sets the default for all sessions for that database instance.
• NLS_TERRITORY sets and specifies the conventions to be followed for date and several other numeric formatting characteristics (such as decimal character and group separator, local currency symbol, ISO currency symbol, week start day, ISO week flag, and so on). For example,

NLS_TERRITORY="GERMANY"

• NLS_CALENDAR is a string whose default value is Gregorian. This parameter sets a valid calendar system format that's used by date fields for displaying time and date information. For example,

NLS_CALENDAR="Gregorian"

or

NLS_CALENDAR='Thai Buddha"

• NLS_CURRENCY specifies the local currency symbol. This parameter overrides the value set by the NLS_TERRITORY parameter.
• NLS_DATE_FORMAT sets the date format to be used by the TO_DATE and TO_CHAR built-in functions. NLS_TERRITORY variables provide a default value for NLS_DATE_FORMAT. This string can take any valid date format-for example,

NLS_DATE_FORMAT = "DD/MM/YYYY"

• NLS_DATE_LANGUAGE sets the values to be used for spelling day and month names (for example, January, Enero, or Janvier) and time abbreviations (such as AM or PM). For example, if NLS_DATE_LANGUAGE="US7ASCII", a query of the form SELECT TO_CHAR(sysdate, 'Day-dd-Month- yyyy') from dual would return Friday-22-May-1998.
• NLS_ISO_CURRENCY provides an explicit definition for ISO currency values, overriding the defaults set by NLS_TERRITORY. For example, this is used to specify Canadian dollars versus US dollars to eliminate ambiguity. For example, if NLS_ISO_CURRENCY="GERMAN", the query SELECT TO_CHAR("Monthly_Total_Sales",'C099G999D999) from SALES (where Monthly_Total_Sales is a column in the table SALES) would give the result DM132,345.345.
• NLS_NUMERIC_CHARACTERS sets up the characters that act as decimal and group separators. These two characters must be single byte and differ from each other. They also can't take the values of the addition sign (+), subtraction sign (-), and equality signs (< or >).
• NLS_SORT specifies the type of sort for character data. The default is set by NLS_LANGUAGE. For example,

NLS_SORT = Greek

Setting Session Values

Most NLS parameters can be set in an interactive session and as environment variables on UNIX and Windows NT. You can set the following NLS variables as initialization parameters, environment variables, and ALTER SESSION parameters:

You can set NLS_LANGUAGE and NLS_TERRITORY as initialization parameters and ALTER SESSION parameters but not as environment variables.

The NLS variables that you can set only as environment variables are

Using SQL NLS Options

You can use NLS parameters in SQL statements that depend on NLS conventions. Applications using these SQL functions can explicitly call the functions independently of the environment variables or session parameters:

• TO_CHAR (Example: TO_CHAR(Date_of_Birth, 'DD-MM-YYYY', 'nls_date_language = GERMAN'))
• TO_DATE (Example: TO_DATE('01-14-93','DD-MM-YY',nls_date_language = French'))
• TO_NUMBER
• NLS_UPPER
• NLS_LOWER
• NLS_INITCAP
• NLS_SORT

For more details, refer to the Oracle8 Server Reference in the Oracle8 documentation set.

Advanced Replication

In today's distributed architecture and global economies, organizations have database servers all over the world and need to exchange information in a timely and efficient manner. Oracle Replication is widely used to provide data access to different Oracle databases, as well as provide redundancy in the case of disaster. Replication is also used to distribute processing loads in distributed systems and give mobile workers the capability to work with the master database site.

Oracle provides two forms of replication: basic and advanced. In the most elementary form of replication-basic-replicated sites have read-only access to data being maintained at the master site. Advanced replication gives replicated sites full read/write access to data at the master site.

Advanced Replication Configurations

You can implement Oracle advanced replication by using two basic configurations or a combination of the two:

• Multimaster replication. In this configuration, multiple sites act as equals. Depending on the setup, the entire database or groups of objects can be replicated. You can configure multimaster replication so that any change in the replicated group is propagated to all locations.
• Snapshot replication. In this configuration, a designated master site and replicated sites can maintain a full copy of the replication group or part of it. Changes in tables can be propagated from the master site to the replicated members, or vice-versa. Changes are made in both directions by using updatable snapshots.
• Mixed configurations. This is normally a combination of multimaster and snapshot replication. An example would be an organization having full multimaster replication configured between its regional headquarters and company headquarters, and snapshot replication configured between the headquarters and service centers.

Advanced Replication Components

Replication objects, groups, sites, and catalogs are the basic components of an advanced replication configuration. Let's take a quick look at their definitions:

• Replication object. This is a database object (such as a table, package, synonym, or trigger) that exists on multiple database servers in a distributed database system. An example would be an EMPLOYEE table replicated in all regional offices of an international organization. A replication object may or may not be part of a replication group.
• Replication group. To ease administration of an advanced replication configuration, replication objects are grouped into replication groups, which can contain replication objects from different database schemas. The only restriction is that each replication object can be included in only one replication group. A replication group at a master site is called a master group. A replication group at a snapshot site is called a snapshot group.
• Replication site. Oracle8 advanced replication classifies replication sites into two categories: master and snapshot sites. A master site contains a full, complete copy of all replication objects and groups. In a distributed advanced replication architecture, there can be only one designated master site, although the configuration may be full multimaster replication. The master site propagates object and data changes to other sites in the configuration. A snapshot site reflects simple read-only and updatable snapshots of tables in a master site.
• Replication catalog. In an advanced replication configuration, a separate set of data-dictionary tables are maintained at master and snapshot sites. These tables form the replication catalog. They're used for administrative purposes and help in configuring and maintaining a replicated architecture. Oracle Replication Manager and other tools use the replication catalog to make administration easier.

Advanced Replication User Accounts

An advanced replication configuration requires certain special user accounts: replication administrator, replication propagator, and replication receivers. Each site-snapshot or master-requires a replication administrator. The replication administrator, propagator, and receiver can be the same Oracle account but have to be set up individually.

Managing Job Queues

Oracle8 uses a combination of internal triggers, deferred transactions, and job queues to propagate data and object changes between replicated sites. The internal triggers capture the changes to be propagated and execute them on replicated sites by using remote procedure calls. These remote procedure calls are stored in deferred transaction queues for later propagation. The actual propagation process is managed by using job queues and deferred transactions.

A local job queue is maintained on each database server participating in the replicated configuration containing information about the PL/SQL procedure, the schedule to run the job, and so on. These jobs will probably include but not be restricted to pushing deferred transactions to master sites, purging applied transactions from deferred transaction queues, and refreshing update snapshot groups.

Oracle8 provides data-dictionary views that contain detailed information about deferred transactions. These views are used to administer an advanced replication configuration. Some views are DEFCALL, DEFCALL_DEST, DEFDEFAULTDEST, DEFERRCOUNT, DEFERR, and DEFSCHEDULE.

Transactions that can't be completed are rolled back at the destination site and are logged in the DEFERROR view, which stores the identity number of the transaction that couldn't be applied. The DEFCALL view lists each remote procedure call associated with a particular deferred transaction and the number of arguments to the procedure. However, to know the value of the argument, you have to know the type of argument. This is obtained by using the GET_ARG_TYPE function of the DBMS_DEFER_QUERY package:

	Argument number of the remote call
	Identity number of the deferred transaction; get its value from DEFERROR view

For example, in a SQL*Plus session, you would call this package as follows:

	Numeric variable declared in SQL*Plus session; you can call it anything
	ID number corresponding remote call

This function would return a numeric value that corresponds to a datatype: 1 represents VARCHAR2, 2 represents NUMBER, 11 represents ROWID, 12 represents DATE, 23 represents RAW, and 96 represents CHAR. When you know the value of the returned number and hence the corresponding datatype, you can use another function, GET_VARCHAR2_ARG (if the return value was 1 representing a VARCHAR2), to get the actual value of the datatype.

Preparing for Replication

The process of building a replicated architecture involves several steps that involve planning and making decisions regarding the replicated configuration. At this stage, you define the schemas to be replicated, including tables, packages, and triggers. You also decide the number of sites at which you want your data replicated.

For organizational efficiency and for ease of administration, it's a good idea to group objects into replication groups. With Oracle8, you can turn off replication for replicated groups without affecting the other groups. This helps sometimes when you want to do maintenance on certain schemas and don't want to stop the rest of the database to stop replication.

You also can decide if you want snapshot replication or full multimaster replication or a combination of both. On today's systems, a lot of static tables or metadata tables are meant for lookup only and don't change. You can decide not to replicate such static tables, at least when using Advanced Replication. When configuring snapshot replication, the additional components of the system are snapshot logs and refresh groups. Snapshot logs are created at each master site to support the necessary snapshots. Refresh groups are created at each snapshot site. At each snapshot site, you also create snapshot groups that correspond to master group objects.

Finally, depending on the need for "fresh or refreshed" data at replicated sites, you might want to decide on an interval at which you want the replicated data to go across to the replicated site. As a DBA, you'll have to go through some trial and error to get the exact configuration you want for your shop, depending on your business needs, nature and amount of data, money you can invest in hardware, and so forth.

Using Replication Manager

Oracle Replication Manager is a GUI tool that helps you build and track a replicated schema. This tool provides graphical shortcuts to several API calls that are normally made from the server, and displays information in a easy-to-understand graphical fashion.

Replication Manager comes with replication wizards that help configure a replication schema. With Replication Manager, you can also connect to a master site and create master replication groups. Replication Manager will also create the Replication Administrator, Propagator, and Receiver accounts. Finally, you can use Replication Manager to create the database links that facilitate communications between sites. You can configure and create multimaster as well as snapshot replication sites with this tool.

To start Replication Manager, from the Start menu choose Programs, Oracle Replication Manager. Click the icon for Oracle Replication Manager. Figure 26.1 shows the startup window of the Replication Manager.

Figure 26.1 : In Replication Manager's start-up window, the button for creating a database connection is on the right and the database connections are on the left.

Click the Create button on the right. In the Create DB Connection dialog box, you may or may not want to keep the two check boxes checked (see Figure 26.2), depending on how secure your PC and office environment is.

Figure 26.2 : Selecting the Auto Connect at Startup check box will autoconnect the Replication Manager to the database. Deselecting the Always Prompt for Password check box will store the pass-word locally on the PC, which isn't always recommended.

Set up a master site with the Replication Manager Setup Wizard

1. From Replication Manager's File menu, choose Setup Wizard.
2. The first dialog box lets you choose to set up a master site or a snapshot site (see Figure 26.3). Click the Next button to choose the default, Setup Master Sites.
   Figure 26.3 : You can choose to set up the master site or the snapshot site. For this example, we will look at creating a master site. The steps provided by the wizard are identical for both Multimaster and Snapshot Replication.
3. In the next dialog box, you can choose from exiting sites or enter a new site name. Click the New button.
4. In the New Master Site dialog box (see Figure 26.4), enter a Site Name. The user is preselected (SYSTEM). Also enter the password for the user SYSTEM, which in this case is the installation default of manager. Clicking OK will save your input and return you to the Setup Wizard - Setup Master Sites dialog box with your site name saved.
   Figure 26.4 : If you want more sites to be made master sites, click Add instead of OK after entering each site name and password. When done, click OK.

5. After you select all the sites you want, click the Next button to move to the next dialog box, which is for creating default administrators, propagators, and receivers (see Figure 26.5). You can accept the defaults or change them. When done, click Next.
   Figure 26.5 : Enter the Schema name and Password for the administrator account, and optionally different propagator/receiver accounts.
6. In the next dialog box, you can optionally specify schemas to be created at the master sites to contain objects to be replicated (see Figure 26.6). For this example, we don't want any more schemas, so click Next.
   Figure 26.6 : If you want to create schemas to replicate objects at a master site, click the New button.
7. The next dialog box lets you set values for scheduled links: Next Date, Interval Expression, Parallel Propagation, and Delay Seconds (see Figure 26.7). You can change the defaults if you want to, and then click Next.
   Figure 26.7 : Click the Edit buttons to change the default values for Next Date and Interval Expression.
8. In the Default Purge Scheduling dialog box (see Figure 26.8), you can set values for Next Date, Interval Expression, and Rollback Segment for default purge schedules. Click Next when done.
   Figure 26.8 : Clicking the Edit button for Next Date brings up a graphical calendar (similar to the Windows calendar), from which you can select the next date. Clicking the Edit button for the Interval Expression brings up a Set Interval dialog box, in which you can set the interval as seconds, minutes, hours, and days (the smallest allowed value is 1 second).
9. In the next dialog box, you can to customize the master site's settings (see Figure 26.9). For this example, we aren't customizing the master site, so simply click Next.
   Figure 26.9: Selecting the master site enables the Customize button. You can customize the administrative users, link scheduling, and purge scheduling.
10. Click Finish in the last dialog box to accept all the values you've entered so far. The wizard creates the RepAdmin, propagator, receiver accounts, and schemas (see Figure 26.10). Clicking OK in the Setup Wizard Finish dialog box generates the master site(s). When it's completed, the wizard creates the master site, which is then visible in Replication Manager's left pane.
    Figure 26.10: Select the Record a Script check box in the final wizard dialog box to have the wizard generate a script of the replication management API calls that it uses to build the system. This script can be run in SQL*Plus, SQL Worksheet, or any interactive tool.
11. After the master site is created, you can click the Configuration, Scheduling, Administration, and Database Objects folders in Replication Manager's left pane (see Figure 26.11).
    Figure 26.11: You can view the database object by owner or by object type.

Managing Replication

The process of managing replicated sites is more complex than administering single sites. The issues are different for each configuration, whether you're dealing with multimaster, snapshot, or hybrid. Let's take a quick look at several tasks that database and replication administrators must perform for smooth operation of a replicated configuration:

• Master group maintenance. You can use Oracle Replication Manager to add or drop master groups. You can also modify master group properties with Replication Manager.
• Quiescing. This ensures that changes get propagated to all replicated sites. Deferred transaction queues are emptied at the propagation sites. The status of the queues changes from Await Callback to Quiesced when the transactions are pushed. The package called to initiate this is DBMS_REPCAT. SUSPEND_MASTER_ACTIVITY. This can be also done in Replication Manager. In Figure 26.11 on the left is a folder called Master Groups (under the Configuration folder). From here you can suspend replication.
  To suspend replication activity for a master group, click the target master group in the database connection the connects to the group's master definition site. Next, click the Properties toolbar button. On the Operations page of the Edit Master Group property sheet, click Suspend Replication.

• Resuming replication activity. After maintenance is completed on the master site, you can use Replication Manager to resume your sites' replication activity. The package that initiates this is called DBMS_REPCAT.RESUME_MASTER_ACTIVITY. You can execute this package in SQL*Plus or in Replication Manager. The procedure is identical to the preceding procedure for suspending replication activity for a master group, except that rather than click Suspend Replication, you click Resume Replication.
• Conflict resolution. Conflict resolution is used to ensure that two main objectives are achieved: data convergence and avoidance of cascading errors. Data convergence ensures that all sites have and share the same data. For example, conflicts can occur if two or more sites in a multimaster environment asynchronously update the same data.
  You can use Replication Manager to view errors. As shown earlier in Figure 26.11, the left pane of the page has a folder called Administration. Under the Administration folder are Deferred Transactions by Dest, Local Errors, and Local Jobs. Alternatively, the view DEFERROR can be queried to see the error transactions.
  A replication configuration must be designed with a specific conflict-resolution methodology in place. Oracle8 advanced replication provides system-defined routines for detecting and solving uniqueness and update conflicts. However, these system routines don't support delete conflicts, changes to primary key columns, referential integrity constraint violations, and presence of NULL values in the columns designated for conflict resolution.

Spatial Data Cartridge

Spatial data refers to location characteristics of objects as they relate to each other in real-world two-dimensional relationships. A map is an example of spatial data. Geographic information systems (GIS) that store spatial data would be likely users of Oracle's Spatial Data Cartridge, which provides a set of functions and procedures designed specifically for storing, retrieving, and analyzing spatial data. (For more detailed definitions of cartridges, see the later section "Oracle Web Server Cartridges.") Besides data from GIS systems, two-dimensional computer-aided design (CAD) drawings could be also stored as spatial data, because CAD drawings indicate the location of objects/parts (actually, primitives) in relation to each other.

In most CAD packages, data representation is done internally with primitive datatypes. A "primitive" is the most basic form of representation of an object. Different forms of geometry, such as arcs and circles, are created by using a combination of primitive datatypes. Oracle's Spatial Data Cartridge supports three basic type of primitives and geometries (all two-dimensional) generated by using a combination of these three types:

• Point and point cluster type. These elements are comprised of X and Y coordinates. These can correspond to axes in an engineering drawing, or to latitude and longitude. Point data consists of one coordinate in space-for example, (4,5), where 4 is the X value and 5 is the Y value.
• Line strings. When several points are joined together to form line segments, the representation is called a line string. Line data consists of two coordinates, which would represent the line segment joining the points. For example, (0,0) and (5,5) would be a line segment joining the points (0,0) and (5,5).
• N-point polygons. Polygons are shapes such as trapezoids, squares, and so forth, formed by joining line strings so that they form a "closed" geometry. Polygon data consists of coordinate pair values, one vertex joining the pair. Coordinates can be represented in a clockwise or counterclockwise order around the polygon.

The Spatial Data Model

The Oracle Spatial Data Model essentially consists of elements, geometries, and layers arranged hierarchically. Elements make up geometries, which in turn make up layers:

• Elements. An element, the most basic datatype in the Spatial Data Model, acts as a building block for a geometry. Points, line strings, and polygons are elements. For example, a square piece of metal to be machined could be represented as a polygon; cutter paths could be represented as line strings. An example of a line string in a GIS system could be a road; a city block could be a polygon.
• Geometries. A geometric object is the representation created by the combination or a set of elements, arranged in an orderly manner. For example, a golf course on a map is a geometry; there would be holes (points), walkways (line elements), and boundaries (polygons). Each geometric datatype is represented by a unique identifier (GID), that stores information about the attributes of the primitives making up the geometric object.
• Layers. A layer is a dissimilar collection of geometric objects all having similar attributes. For example, in a facility planning layout, the foundation geometry object would normally be on one layer, storage-space would be on a different layer, machinery would be on a different layer, lighting plan would be on another layer, and so on. Layers are used extensively by architects, engineers, and contractors to see selective details about a location.

The Spatial Table Structure

Oracle's Spatial Data Cartridge uses four tables/views to store and index spatial data. These four tables make up a layer:

• SDOLAYER  This table contains summary information about a layer of spatial data. This table has two columns: SDO_ORDCNT and SDO_LEVEL (see Table 26.1).
• SDODIM  This table stores information related to the dimension (such as latitude and longitude) of the data. This table has five columns: SDO_DIMNUM, SDO_LB, SDO_UB, SDO_TOLERANCE, and SDO_DIMNAME (see Table 26.1).
• SDOGEOM  This table contains information about geometric entities stored in the data. The columns of the table are SDO_GID, SDO_ESEQ, SDO_ETYPE, SDO_SEQ, SDO_X1, SDO_Y1, SDO_X2, SDO_Y2, and so on through SDO_Xn and SDO_Yn, where Xn and Yn are X and Y values of the nth coordinate (see Table 26.1).
• SDOINDEX  This table contains summary index information about geometry in a layer. SDOINDEX has three columns: SDO_GID, SDO_CODE, and SDO_MAXCODE (see Table 26.1).

Using Spatial Data

Spatial data must be treated differently as compared with conventional data, because the indexing and data-retrieval techniques are different. Spatial data can be entered into tables interactively from applications or by using SQL*Loader for mass/batch loads.

Spatial data is queried in essentially a two-step process. The first step, referred to as a "primary filter," uses algorithms to approximate search patterns to narrow down the search size. The output from the first filter is passed to the second stage (called the "secondary filter"). The secondary filter uses exact calculations of geometry to the smaller output of the primary filter to find a match.

Spatial data applications perform a spatial query when information contained in a bounding or a query window is requested. The query window calls the SDO_WINDOW package, which performs the data calculations.

A spatial join is the output of joining two tables (layers) with spatial operators. A spatial join would join two layers with the layername_SDOINDEX field. An example of a query that causes a spatial join could be a list of all road and railway intersections in town.

Oracle Web Server Cartridges

Oracle Web Application Server is a superset of a standard HTTP server, with extensions (cartridges) that integrate seamlessly with the Oracle server. Similar to Spatial Data Cartridges, the cartridges that ship with Oracle Web Server are functions and packages that act as extensions to the core database product of the Oracle database server. You can program cartridges in various programming languages, such as C++, C, Visual Basic, and Java.

The Web Application Server comes in two versions: advanced and standard. The advanced version is a superset of the standard version. Some notable features present in the advanced version but not in the standard version are as follows:

• Transaction service. Because HTTP (Hypertext Transfer Protocol) is stateless, it becomes difficult to enforce the concept of transactions, specially for data-entry applications. The advanced version of Oracle Web Server provides a transaction service-middleware software that maintains the equivalent of sessions, unlike the standard stateless HTTP.
• ODBC cartridge. This cartridge provides connectivity to ODBC-compliant databases other than Oracle. You use it if your site has other ODBC-compliant databases that need to work in a Web application; you don't need the ODBC cartridge to connect to Oracle.
• Distributed cartridges. You can run cartridges on remote nodes only if you have the advanced version. If you have the standard version, the cartridges all run on the same node.

The major components that are similar in the advanced and standard version are as follows:

• HTTP daemons or listeners. The HTTP daemon component (in UNIX) or listener component (in Windows NT) of the Web Application Server listens for requests from clients. The listener uses HTTP to communicate with clients and can accept connections on one or more IP address/port combinations.
• Web Request Broker. The Web Request Broker (WRB) keeps track of the execution of the entire system. It handles load-balancing tasks, tracks global resources such as available cartridges, and obtains the address (the object reference) of the required cartridge resource. The WRB also provides system services that all cartridges can use.
• Cartridges. Cartridges are server-side applications that handle cartridge-specific requests from clients. Some example of cartridges are the PL/SQL cartridge, LiveHTML cartridge, Java cartridge, and VRML cartridge.

Oracle Web Application Server 3.0 has been replaced by version 4.0, which is called Oracle Application Server. Oracle Application Server is designed to enable organizations to deploy and manage applications on a single server, rather than on hundreds or thousands of desktop PCs (the case in a two-tier architecture). Oracle Application Server 4.0 is designed to support all types of network clients, serving as a central point of access to any database, application, or legacy system.

All features of version 3.0 are available in 4.0, including backward compatibility with all cartridges. Version 4.0 also has several new cartridges, including new Oracle Call Interface (OCI) cartridge for native access to Oracle databases, an ODBC cartridge for access to any third-party database, and a new Rdb cartridge for access to Oracle Rdb database applications. A COBOL cartridge, made by Fujistu Corporation, enables access to COBOL applications. The AppBuilder wizard, made by Borland, provides designers with step-by-step help for designing Web applications.

Using Oracle Web Application Server Cartridges

This section explains major features of two commonly used cartridges: PL/SQL and the Java cartridge. To understand the functioning of the cartridges, you need to understand the architecture of the Web Server. The Oracle Web Application Server has three major components:

• HTTP listeners (daemons on UNIX). The HTTP daemon (UNIX) or listener component is a process that listens for requests from clients. The listener uses HTTP to communicate with clients and can accept connections on one or more IP address/port combinations. You can specify the port on which you want the listener or daemon to listen on. Normally, port 8888 or 9999 is the default port for administration of the Web Server.
• Web request brokers (WRB). WRBs are software components that are implemented as CORBA-compliant object request brokers. (CORBA is a industry standard for request brokers.) WRBs can send requests to and get responses from applications running on machines on the network. One WRB keeps track of execution of the entire system. It handles load-balancing tasks, tracks global resources such as available cartridges, and provides several system services that the cartridges can use.
• Cartridges (also called server-side applications). Cartridges, in this sense, are server-side applications that handle cartridge-specific requests from clients. Each cartridge functions independently. An application can be designed by using several cartridges, depending on the cartridge-specific requests. On one Web Application Server, you can have multiple cartridge instances for each cartridge type. The number of cartridge instances you choose to create and deploy would depend on your application and business needs.

Web Application Server comes bundled with several cartridges, of which the following are just some:

• Java Cartridge. This is needed to run Java applications on the server-side.
• PL/SQL Cartridge. This cartridge handles requests that need to run PL/SQL procedures, functions, and packages on the Oracle server.
• LiveHTML Cartridge. This cartridge parses HTML documents that contain server-side include (SSI) extensions.
• Oracle Worlds Cartridge. This cartridge enables designers to build dynamic three-dimensional worlds and environments by using VRML.
• Perl Cartridge. This runs Perl scripts.
• ODBC Cartridge. This enables connectivity to ODBC-compliant relational databases.

Handling Requests

When the Web Application Server receives a request from a client, the listener component processes the request first and determines how it should handle the request. If the request is for a static file (a file that exists on a file system), the listener fetches the file from the operating system and sends it to the client. If the request is to execute a CGI (Common Gateway Interface) file/script, the listener executes the file and sends the results back to the client.

If the request is for a cartridge, the listener performs the following:

• Routes the request through the Web Request Broker.
• Looks up the virtual path to determine the cartridge type that this request is for. (A virtual path is the syntax of the URL that's part of the request.)
• Calls the authentication service to authorize the client.
• Sends the request to an instance of the appropriate cartridge. The cartridge instance processes the request and returns the results to the client. The URL string has all the relevant information that tells the listener what type of cartridge and instance to which to connect.

The PL/SQL Cartridge

The PL/SQL Cartridge provides the environment for developing Web applications as PL/SQL procedures stored in an Oracle database server. (PL/SQL is Oracle Corporation's procedural language extension to SQL.) When you configure the PL/SQL Cartridge, you install packages that help generate HTML pages. These packages define procedures, functions, and datatypes you can use in your stored procedures. In each HTTP request for the PL/SQL Cartridge, the uniform resource locator (URL) specifies the PL/SQL agent (which contains connection information) and the name of the stored procedure to run. The URL can also contain values for any parameters required by the stored procedure.

Before you can use the PL/SQL Cartridge, you need to load the packages listed in this section into the database schemas from which you want to run the procedures. The packages define datatypes, functions, and procedures used by the cartridge, and you can use some of them in your Web application. The functions and procedures help you generate dynamic HTML pages that contain data retrieved from the database.

Oracle Web Application Servers comes with a set of prewritten PL/SQL packages called the PL/SQL toolkit. To install the PL/SQL Web Toolkit, use the PL/SQL agent administration forms that are created with the Web server installation. The installation procedure grants the CONNECT and RESOURCE roles to the database user and executes the $ORAWEB_HOME/ admin/owains.sql script, which installs the packages in the PL/SQL Web Toolkit. When the owains.sql script is run manually; you should launch it from Server Manager or SQL worksheet (part of Enterprise Manager). If you want to run it from SQL*Plus, see the header of the script for instructions.

The packages are installed in the user's schema. This ensures that the user can't use the subprograms in the packages to access data in another user's schema.

Figure 26.12 shows the components of an URL. The Web Application Server comes with the Web Application Server Manager, which is a set of HTML forms you use to configure the PL/SQL Cartridge, the PL/SQL agent, and the Data Access Descriptor (DAD). On these forms you enter information such as virtual paths for the PL/SQL Cartridge, the SQL*Net/Net8 service name for the DAD, and the error-reporting level for the PL/SQL agent. Figure 26.13 shows the configuration of a PL/SQL cartridge in the Oracle Web Application Architecture. Chapter 19, "Improving Throughput with SQL, PL/SQL, and Precompilers," and Appendix A, "Essential PL/SQL: Understanding Stored Procedures, Triggers, and Packages," provide more information on PL/SQL.

Figure 26.12: The basic components of an URL for the PL/SQL cartridge, showing the use of PL/SQL (functions, procedures, and packages) in the HTTP protocol. The parameters passed to the PL/SQL procedure act as query string, which can be called from HTML forms and other Web applications.

Figure 26.13: The Web listener is a common component of all cartridges (in this case, PL/SQL and Java cartridges). After the listener interprets a request, the request is routed to the Java cartridge or the PL/SQL cartridge.

Sequence of events observed when a PL/SQL Cartridge request is made

1. The listener component of the Web Application Server receives the request from a client and determines who should handle it. In this case, it forwards the request to the Web Request Broker (WRB) because the request is for a cartridge.
2. The WRB forwards the request to an available PL/SQL Cartridge.
3. The PL/SQL Cartridge retrieves the name of the PL/SQL agent from the URL (request). It uses the agent's configuration values to determine to which database server to connect and how to set up the PL/SQL client configuration.
4. The cartridge connects to the database with the PL/SQL agent's configured values, prepares the call parameters, and invokes the procedure in the database.
5. The procedure generates the HTML page, which can include dynamic data accessed from tables in the database as well as static data. ("Static data" here refers to HTML pages, images, or both, which exist on a file system that's mapped to by the URL. "Dynamic data" refers to content created on-the-fly from a database query.)
6. The output from the PL/SQL procedure is returned to the PL/SQL Cartridge and the client (browser) via a response buffer.

The Java Cartridge

Java is a object-oriented programming language developed by Sun Microsystems. Java has rapidly evolved into a variety of network computing and Web architectures. What makes Java so exciting is the promise of platform independence. In keeping with the popularity of Java, Oracle Corporation has developed a Java cartridge. An HTTP request routed to the Java Cartridge is processed by running a Java application that returns the HTTP response.

The Java Cartridge doesn't support Java applets (programs written in Java that can be downloaded over the network and run in a Web browser), but supports only Java applications. Java applications are loaded from a trusted file system (which is mapped to by the Web Server).

When the listener component of the Web Application Server receives requests for the Java Cartridge from browsers, the Java Cartridge interprets information in the URL to identify the Java application that should handle the request. The Java application then programmatically responds to the client, performs interim tasks, accesses a database, or performs other computing tasks. Ultimately, the Java application sends a response back through the Java Virtual Machine (JVM) and the Web Application Server to the client. Figure 26.13 shows the Java cartridge in the architecture of the Oracle Web Application Server.

Sequence of events observed when a Java Cartridge request is made

1. The Web Application Server receives the request identified by a URL-for example, http://servername.domainname/java/myfirstclass.
2. The listener knows by the URL string that the request needs to be routed to a cartridge, and routes the request to the WRB.
3. The WRB of the Web Application Server dispatches the request. The WRB examines the URL and determines which cartridge should handle the request. If the URL is under a virtual path that belongs to the Java Cartridge, the WRB dispatches the request to the Java Cartridge.
4. The WRB finds a Java Cartridge, if available, or starts one if none is available.
5. The Java Cartridge receives the request, parses the URL, and finds the name of the application (actually, a Java class) to call. Normally, the last part of an URL string is the Java class. In the URL mentioned in step 1, myfirstclass is the application (class).
6. The Java Cartridge loads the class and invokes it at its entry point, generates a response (including HTTP header and body), and returns the response. The Java Cartridge receives the response and returns it to the WRB, which forwards it to the browser client that made the request.

Oracle Advanced Queuing

Oracle Advanced Queuing (Oracle AQ) is an Oracle8 feature that integrates a messaging queuing system with the Oracle8 Server (Enterprise Edition). This allows you to store messages into queues that can be retrieved and processed when needed. The real advantage of advanced queuing is that it's an integrated and reliable queuing system that works without any extra middleware software. Applications access the queues through a PL/SQL interface.

Oracle AQ Components and Terminology

Oracle AQ has certain terms and definitions typical to a messaging system. Let's take a quick look at some of the terms:

• Messages. A message is the smallest measurable unit of information used by Oracle AQ for messaging. Messages are inserted into and retrieved from queues.
• Queues. A queue acts as a container for messages. All messages in a queue must have the same datatype.
• Queue tables. Queue data is stored in queue tables, which are database tables that contain one or more queues. A queue table has approximately 25 columns.
• Agents. An agent is basically a queue user. There are two categories of agents: enqueuing and dequeuing. Users who place messages on the queue are called enqueuing agents or "producers." Users to whom a message is directed are called "consumers." Consumers act as dequeuing agents and remove messages from the queue.
• Queue monitor. The queue monitor is a background process that monitors queues. Parameters monitored include message expiration, retry, and delay time. The presence of this optional process depends on the values set for the AQ_TM_PROCESS initialization parameter. The AQ_TM_PROCESS initialization parameter has to be initialized and set to a value exceeding 1 for the queuing statistics to be collected. Valid values for the AQ_TM_PROCESS parameter are as follows:
  • If AQ_TM_PROCESS=0 or is unspecified, no queue monitor processes are created.
  • If AQ_TM_PROCESS=1, one queue monitor process (QMN0) is created as a background process to monitor the messages.
  • If AQ_TM_PROCESS is set 2 through 10, that many number of QMNn processes are created.

Major Features of Oracle AQ

Oracle AQ has the following major features:

• SQL access. Messaging data is stored in tables and is accessible through standard SQL functions. All principles of data access and query optimization apply to messaging data.
• Structured payload. You can use Oracle8 object features to structure and manage the payload. Traditional datatypes as well as user-defined objects are used for message storage and retrieval.
• Integrated database-level operational support. All traditional database-level activities such as import/export, use of indexes for performance enhancement, deferred rollback, backup and recovery, use of Enterprise Manager, and other tools is facilitated. In other words, messaging data is treated just as conventional or traditional data is.
• Retention and message history. Messages can be retained even after being read. You can specify the duration for storage. This history can be used for data-warehousing, auditing, and for other tracking purposes.
• Integrated transactions. Systems design is simplified if messaging can be "tied" to transactions versus "external" messaging systems that depend on middleware such as Transaction Processing (TP) monitors. Systems configuration is simplified because there are fewer pieces of software to configure and manage.
• Tracking and event journals. Relationships of messages to one another can be established because the data is stored and manipulated as relational data. Applications use these relationships to create event journals that can be used for tracking and workflow scenarios.
• Windows of execution. A particular message can be designated to be delivered or dispatched in a defined window of time. You can also specify a delay time after which a message should be processed. The initialization parameter AQ_TM_PROCESS is used to enable time monitoring on queue messages.
• Multiple consumers per message. A message can be dispatched to multiple consumers.
• Navigation. Navigational control is enabled between messages. You can navigate between messages in a definite order or based on an order determined by snapshots.
• Message ordering. The order in which messages are consumed can be set in three ways: by using a sort order based on properties by which they're sorted, by using a priority that can be assigned to each message, and by using a sequence deviation that allows you to position a message in relation to other messages.
• Dequeue modes. Dequeuing is the process of removing a message from a queue. If a message is browsed, it remains in the queue; if it's read, it's considered as dequeued.
• Exception queues. If a message isn't used with a time limit or given constraints, the message can be moved to a user-specified exception queue.
• Message grouping. You can group messages belonging to one queue to form a set that can be consumed by only one user at a time.
• Retries with delays. If a message isn't consumed for some reason, an attempt is made to retry to submit the message up to the specified limit.
• Queuing statistics. Oracle AQ maintains queuing statistics in a dynamic table GV$AQ. These statistics contain information about the current state of the queuing system as well as time-interval statistics.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.