'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 27 - Understanding Oracle8 Options

Using Oracle8

Chapter 27

Understanding Oracle8 Options

The Parallel Server Option

Oracle's Parallel Server option allows multiple instances running on different nodes to access the same database (see Figure 27.1).

Figure 27.1 : Oracle Parallel Server allows multiple instances to access the same database.

Only one instance has access to the database in single-instance mode (also known as exclusive mode). Multiple instances accessing the same database provide the following advantages:

Use the INIT.ORA file for different instances
By keeping different INIT.ORA files for each Oracle instance in an OPS environment, you can optimize each instance's performance depending on its desired workload.

Hardware Requirements

Oracle uses global locks to preserve data integrity and consistency while allowing concurrent read/write access to the same data files. To enable simultaneous access to the data files and to implement global locks, Oracle Parallel Server requires the following functionality at the hardware level:

Disks Accessible from All Nodes

Oracle Parallel Server is available on several platforms where multinode systems are available from hardware vendors. Because the database in an Oracle Parallel Server environment is accessed by all instances running on all nodes, all data files, control files, and online redo log files need to be accessible to all nodes.

Instances share files in an OPS environment
Instances in an Oracle Parallel Server environment share all the data files and control files. However, only one instance can write to a set of online redo logs assigned to it at startup. In addition, each instance has its own initialization parameter file.

Various vendors have implemented this feature differently. These approaches can be divided into two categories:

Figure 27.2 : Disks in a shared-disk system are connected to nodes via a high-speed interconnect.

Figure 27.3 : Shared-nothing systems don't have a common resource.

High-Speed Interconnect

To coordinate concurrent access to the data files and to synchronize the data in the memory of all nodes, OPS uses the Integrated Distributed Lock Manager (IDLM). IDLM requires the high-speed interconnect between nodes for messaging traffic generated for communication among nodes for synchronization.

Software Requirements

Oracle Parallel Server also requires the following special software components:

Parallel Server Uses

As mentioned earlier, the use of Oracle Parallel Server option increases system availability (because of the increased redundancy) and provides more system resources to interact with the database.

OPS offers increased system availability
Increased system availability in an OPS environment stems mainly from the fact that if one instance of the database is down, chances are that other instances accessing the same database will make it available for users.

The increased system resources can be used to increase the throughput. You can do the following by using the additional CPU and memory available on the additional nodes:

Additional system resources (to access the same database) comes with an additional cost of synchronization among the concurrent activities. Some increased resources are used to meet the overhead of the synchronization. Thus, the net amount of additional system resources available for application processing is as follows: 

additional system resources available for processing =
resources added - resources to synchronize concurrent access

Therefore, OPS is advantageous as long as the cost of synchronization remains low. If the cost of synchronization becomes comparable to the resources added, the use of OPS isn't justified.

The resources consumed for synchronization depends on Oracle's internal architecture. A significant part of this cost, however, depends on the nature of the workload and the application architecture. OPS has been found suitable in the following situations:

Using Object Types with the Object Option

Oracle8 is an object-relational database because it allows user-defined object types. Oracle8 lets you define an object's attribute and the methods associated with it. For example, consider a part to be an object; its attribute can then be defined as part number, description, stock location, current inventory, and so on. Finding the location of a part, increasing the inventory on arrival of the part from the supplier, decreasing the inventory when the part is consumed, and so on are the methods you can associate with a part.

Security and privileges of objects
Object types are owned by the user who creates them and follow the Oracle's standard security and privilege mechanism. A user must be granted the EXECUTE privilege on the object types by the owner before he or she can use an object type defined by another user.

If you're involved with designing applications with a relational database, you might wonder why an object type should be used. Consider a large company, where each division and location has its own installed computer systems and information systems division. Division A needs to define a part number field for its application and decides that it should be a 12-digit number. Also consider that Division B independently defines the part number field to be VARCHAR2(12). Obviously, there's a lack of communication between the two groups. This type of inconsistency can cause problems while transferring the data between the applications. This problem won't arise if the corporate DBA defines a part_type object and all the divisions use the already-defined part_type objects in their applications. Thus, defining a part type promotes standards and uniformity. The standard method to manipulate the part_type object can also be defined centrally and reused by the individual divisions. The use of object types has the following advantages:

Creating and Using Object Types

An object type has a specification and a body. The specifications consist of the object's attributes and the declaration of intended methods to be used with the objects. The body consists of PL/SQL code for the methods associated with the object.

Consider the case of a research organization that needs to define an author object type. You could define an author object type as follows: 

create or replace type author_type as object (
Last_name      varchar2(20),
First_Name     varchar2(20),
author_title   varchar(20));

When it's defined, you can see the attributes of an object datatype by using SQL*Plus's DESC command: 

SQL> desc author_type
 Name                            Null?    Type
 ------------------------------- -------- ----
 LAST_NAME                                VARCHAR2(20)
 FIRST_NAME                               VARCHAR2(20)
 AUTHOR_TITLE                             VARCHAR2(20)

The preceding CREATE command creates an author_type datatype, which can further be referenced anywhere you would refer a standard datatype such as a number, CHAR, or VARCHAR2. For example, the following CREATE datatype command creates a paper_type datatype and uses the preceding author_type datatype definition:
The predefined author_type datatype
The AUTHOR_TYPE datatype as part of the new object type definition

Note the AUTHOR_TYPE datatype listed under the describe output's type column. When defined, the datatype can be used in an application. The following example creates a table by using the paper_type datatype:

Altering and dropping object types
When it's defined, you can drop a user object type by using the DROP TYPE command. You can't change an object type's existing attributes, but you can add new member subprogram specifications.
The defined datatype

Similarly, you can use paper_type in a PL/SQL program:
The defined datatype

You need to write a statement similar to the following in order to insert a row into the table paper_sales: 

insert into paper_sales values (123456789,999999,
paper_type(1111,'Research Paper',
author_type('Last','First','Professor'),'Oracle8',100),10);

To reference an element in a datatype, you have to prefix it with the datatype name. For example, to select the paper# paper name from the paper_sales table, you would write a query similar to the following: 

SQL> select sales_order#,x.paper.paper#,x.paper.name
     from paper_sales x ;

SALES_ORDER# PAPER.PAPER# PAPER.NAME
------------ ------------ ------------------------------
123456789                 1111 Research Paper

The following query selects the author's last and first name and title: 

select sales_order#,x.paper.paper#,x.paper.name,
        x.paper.author.last_name,
        x.paper.author.first_name,
        x.paper.author.author_title
from paper_sales x;
SALES_ORDER# PAPER.PAPER# PAPER.NAME   PAPER.AUTHOR.LAST_NA
------------ ------------ ------------ --------------------
PAPER.AUTHOR.FIRST_N PAPER.AUTHOR.AUTHOR_
-------------------- --------------------
   123456789         1111 Research Paper             Last
First                Professor

Defining and Managing Object Tables and Nested Tables

An object table is a database table that's defined with an object type only-it doesn't use any relational columns. You can nest one object table inside another via an object type. Nested tables are useful in a master-detail relationship. The following code creates an object type employee_type and then uses it to create an object table employee_list: 

create or replace type employee_type as object(
        EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2));

create or replace type employee_list
  as table of employee_type ;

An object table is shown as the list of object datatype in the DESC command's output:
The object table

The following SQL command creates the nested table dept by using the already created employee_list object table:

Using nested tables
Nested tables are a convenient way to store tables with master-detail relationship. In this example, information about all the employees in a department is contained within the department table itself. 

create table dept (
        dept_no number(3),
        dept_name varchar(30),
        employees employee_list)
nested table employees store as employees_list_table;

SQL> desc dept
 Name                            Null?    Type
 ------------------------------- -------- ----
 DEPT_NO                                  NUMBER(3)
 DEPT_NAME                                VARCHAR2(30)
 EMPLOYEES                                EMPLOYEE_LIST

Use the following command to insert rows into nested table: 

insert into dept values (
20,'Sales',
employee_list(
   employee_type
   (7369,'SMITH','CLERK','17-DEC-80',800,NULL),
   employee_type
   (7566,'JONES','MANAGER','2-APR-81',2975,NULL)));

To query contents of a nested table, you need to use the THE function: 

select e_list.ename from THE
(select employees from dept where dept_no=20) e_list;

Understanding Methods

A method is a PL/SQL procedure or function associated with the object. Oracle8 supports the following types of methods:

Constructor Method

Oracle automatically creates a constructor method for an object so that you can perform DML functions on the object. By default, the object's constructor method has the same name as the object itself, and all the object's attributes are available as parameters of the constructor method. The following example shows how constructor methods are used to insert data in the paper_sales table:

Constructor methods

Member Method

A member method is a stored procedure associated with an object type. Before you can define a member method, you need to associate it with the object as its attribute. You can do this in the CREATE TYPE statement or by using an ALTER TYPE statement: 

create or replace  type paper_type as object (
  paper# number(10),
  name       varchar2(30),
  author     author_type,
  subject varchar2(20),
  price number(5),
  stock_qty number(5),
member function STOCK_VALUE return number,
PRAGMA RESTRICT_REFERENCES( stock_value , WNDS, WNPS));

The member function definition can also have input parameters (although none are shown here) and it needs to specify the type of single return value.

PRAGMA RESTRICT_REFERENCES is a compiler directive that specifies what type of operations the function can perform. You need to specify this directive to prevent it from doing unwanted modifications to the object. Four options are available for PRAGMA RESTRICT_REFERENCES:

WNDSWrite no database state
WNPSWrite no package state
RNDSRead no database state
RNPSRead no package state

As soon as it's defined, the methods associated with an object are shown when you describe the object: 

SQL> desc paper_type
 Name                            Null?    Type
 ------------------------------- -------- ----
 PAPER#                                   NUMBER(10)
 NAME                                     VARCHAR2(30)
 AUTHOR                                   AUTHOR_TYPE
 SUBJECT                                  VARCHAR2(20)
 PRICE                                    NUMBER(5)
 STOCK_QTY                                NUMBER(5)

METHOD
------
 MEMBER FUNCTION STOCK_VALUE RETURNS NUMBER

After specifying the method as the object type attribute, the next step is to define the body of the PL/SQL procedure for the intended function. The following PL/SQL code calculates the STOCK_VALUE: 

create or replace type body paper_type as
member function stock_value
return number is
begin
        return self.price * self.stock_qty;
end stock_value;
end ;
/
Type body created.

When defined, the methods can be called like a PL/SQL program. However, each occurrence of an object has its own state. Because methods are used to manipulate an object's particular state, the methods need to reference a particular object occurrence. Listing 27.1 demonstrates how to use methods associated with an object. Note the usage of the STOCK_VALUE member method associated with the object PAPER_TYPE in lines 10 through 14.

LISTING 27.1  Using the MEMBER method
 

     01:     DECLARE
     02:     paper_type1 paper_type := paper_type(1,'Paper_1',
author_type('Last','First','Professor'),'Oracle8',100,10);
     03:     paper_type2 paper_type := paper_type(2,'Paper_2',
author_type('Last','First','Professor'),'Oracle8',200,20);
     04:     paper_type3 paper_type := paper_type(3,'Paper_3',
author_type('Last','First','Professor'),'Oracle8',300,30);
     05:     begin
     06:     dbms_output.put_line('Value for paper_type1 is:'||to_char(paper_type1.stock_value));
     07:     dbms_output.put_line('Value for paper_type2 is:'||to_char(paper_type2.stock_value));
     08:     dbms_output.put_line('Value for paper_type3 is:'||to_char(paper_type3.stock_value));
     09:     end ;
     10:     /
     11:     Value for paper_type1 is:1000
     12:     Value for paper_type2 is:4000
     13:     Value for paper_type3 is:9000
     14:     PL/SQL procedure successfully completed.

The MAP and ORDER Methods

An object type's MAP method doesn't have any input parameters. It returns a single scalar value, which can be used to compare object datatypes in SQL statements having predicates for equality, between and in, order by, group by, distinct, unique, and so on. Listing 27.2 shows how to define the MAP method for the paper_type object.

The MAP and ORDER methods
The ORDER method is similar to the MAP method, with this difference: It can take one input argument. The MAP method, on the other hand, doesn't take any input argument.

LISTING 27.2  Using the MAP method
 

     01:     create or replace  type paper_type as object (
     02:      paper# number(10),
     03:      name       varchar2(30),
     04:      author     author_type,
     05:      subject varchar2(20),
     06:      price number(5),
     07:      stock_qty number(5),
     08:     map member function return_paper# return number,
     09:     member function STOCK_VALUE return number,
     10:     PRAGMA RESTRICT_REFERENCES( stock_value , WNDS, WNPS))
     11:     /
     12:     
     13:     Type created.
     14:     
     15:     SQL> desc paper_type
     16:     Name                            Null?    Type
     17:     ------------------------------- -------- ----
     18:     PAPER#                                   NUMBER(10)
     19:     NAME                                     VARCHAR2(30)
     20:     AUTHOR                                   AUTHOR_TYPE
     21:     SUBJECT                                  VARCHAR2(20)
     22:     PRICE                                    NUMBER(5)
     23:     STOCK_QTY                                NUMBER(5)
     24:     
     25:     METHOD
     26:     ------
     27:     MAP MEMBER FUNCTION RETURN_PAPER# RETURNS NUMBER
     28:     
     29:     METHOD
     30:     ------
     31:     MEMBER FUNCTION STOCK_VALUE RETURNS NUMBER

Note the use of the MAP keyword in front of the MEMBER FUNCTION keyword (line 8) to define the MAP method. When defined, the MAP method can be used to order objects of that type. Here, return_paper# can be used to order the paper_type object selected from the tables papers.

Listing 27.3 explains the usage of the MAP method to use the ORDER clause in the SQL statement.

Using the SELF keyword
The use of SELF keyword is optional here. It references the object's current state. It's useful if you need to pass the method's output as an argument to another method or procedure.

LISTING 27.3  Using an ORDER clause in the MAP method
 

     01:     create or replace type body paper_type as
     02:     member function stock_value
     03:     return number is
     04:     begin
     05:         return self.price * self.stock_qty;
     06:     end stock_value;
     07:     map member function return_paper# return number is
     08:     begin
     09:         return self.paper#;
     10:     end return_paper#;
     11:     end ;
     12:     
     13:     create table papers of paper_type;
     14:     insert into papers values  (paper_type(1,'Paper_1',
			 author_type('Last','First','Professor'),'Oracle8',100,10));
     15:     insert into papers values  (paper_type(6,'Paper_6',
			 author_type('Last','First','Professor'),'Oracle8',600,60));
     16:     insert into papers values  (paper_type(8,'Paper_8',
			 author_type('Last','First','Professor'),'Oracle8',800,80));
     17:     insert into papers values  (paper_type(9,'Paper_9',
			 author_type('Last','First','Professor'),'Oracle8',900,90));
     18:     insert into papers values  (paper_type(4,'Paper_4',
			 author_type('Last','First','Professor'),'Oracle8',400,40));
     19:     
     20:     SQL> select * from papers order by 1 ;
     21:     
     22:      PAPER# NAME
     23:     ---------- ------------------------------
     24:     AUTHOR(LAST_NAME, FIRST_NAME, AUTHOR_TITLE)
     25:     -------------------------------------------------------
     26:     SUBJECT                   PRICE  STOCK_QTY
     27:     ---------------------- ---------- ----------
     28:     1 Paper_1
     29:     AUTHOR_TYPE('Last', 'First', 'Professor')
     30:     Oracle8                     100         10
     31:     
     32:          1 Paper_1
     33:     AUTHOR_TYPE('Last', 'First', 'Professor')
     34:     Oracle8                     100         10
     35:     
     36:          4 Paper_4
     37:     AUTHOR_TYPE('Last', 'First', 'Professor')
     38:     Oracle8                     400         40
     39:     
     40:          6 Paper_6
     41:     AUTHOR_TYPE('Last', 'First', 'Professor')
     42:     Oracle8                     600         60
     43:     
     44:          8 Paper_8
     45:     
     46:     AUTHOR_TYPE('Last', 'First', 'Professor')
     47:     Oracle8                     800         80
     48:     
     49:          9 Paper_9
     50:     AUTHOR_TYPE('Last', 'First', 'Professor')
     51:     Oracle8                     900         90
     52:     
     53:     
     54:     6 rows selected.

Creating and Managing Object Views

A view that allows you to define the object types via a relational table's underlying structure is known as an object view. Assume that you have an existing database table whose structure is as follows: 

SQL> describe paper
 Name                            Null?    Type
 ------------------------------- -------- ----
 PAPER#                                   NUMBER(10)
 NAME                                     VARCHAR2(30)
 SUBJECT                                  VARCHAR2(20)
 PRICE                                    NUMBER(5)
 STOCK_QTY                                NUMBER(5)

You can create an object named paper_type_v based on this table's column: 

create type paper_type_v  as object ( paper# number,
  name varchar(30),subject varchar2(20),
  price number, stock_qty number);

Use the following command to create an object view by using the object paper_type_v: 

create or replace view paper_ov of paper_type_v
with object oid (paper#) as
select paper#, name, subject, price, stock_qty
from paper ;

You can either insert data into paper table by using the object view paper_type_v, or you can update the paper table directly: 

insert into paper_ov values
    (paper_type_v(1,'Paper One','Using Oracle',9.99,10));

Because you can create the abstract datatype to insert data into the table via the object view, you can associate methods to manipulate the data in the table and thus standardize the application code. You can also use the INSTEAD OF trigger to update data in an object view.

The Partition Option

Whether it's a war or a bridge construction project, we humans have successfully adopted the "divide-and-conquer" strategy to manage things that become too large in volume and size. Oracle8's partition option is meant for a similar intent-for very large database objects. In a VLDB (very large database) environment, some objects tend to become very large, and it becomes increasingly difficult to manage them as a single entity. As the object gets bigger, the administrative tasks of restoring and backing up, loading data, building indexes, and so on become almost impossible; failure at any point forces you to repeat the operation. Imagine the ease of handling twenty 5GB pieces independently instead of handling a single 100GB table. Dividing the table into multiple partitions will provide the following advantages:

Partitions are transparent to the end user
When created, the underlying partitions of a table or an index are totally transparent to the application and users. However, users can use a partition in an SQL query's FROM clause instead of the table.

What Can Be Partitioned

Oracle8 allows you to partition only tables and the indexes. Other objects-clusters, nested tables, index-organized tables, snapshots, and snapshot logs-can't be partitioned. You also can't partition a table that's part of a cluster or one that contains a column of long, LOBs, or long raw datatypes or object datatypes. You can partition an index as long as it's not a cluster index or defined on a clustered table. You can partition a bitmap index only if it's a global bitmap index on a partitioned or a non-partitioned table. You can't create local bitmap indexes on individual partitions of a partitioned table.

A partitioned table can have a local or global partitioned or non-partitioned index. A non-partitioned table can contain a partitioned index. As shown in Figure 27.4, an index that refers to rows in a specific partition is known as the "local index." On the other hand, a "global index" refers to the rows from all partitions of a table. A global index can be partitioned.

Figure 27.4 : Partitions of a global index contain rows from any table partition.

Partitioning a Table

You can partition a table while creating it. Logical attributes such as column names, datatypes, and constraints of all the partitions of an object must be the same; physical attributes (such as storage parameters) for all an object's partitions could be different for each partition, however. The SQL command in Listing 27.4 creates a partitioned table with these characteristics.

Different partitions of an object can be placed in different tablespaces
You can specify the storage parameters of each and every partition of a table and an index in the partition's storage clause, which can include tablespace, PCTFREE, PCTUSED, initial extent, next extent, and so on.

LISTING 27.4  Creating a partitioned table
 

     01:     create table warehouse (
     02:         w_id            number,
     03:         w_ytd           number(12),
     04:         w_tax           number(4),
     05:         w_name          varchar2(10),
     06:         w_street_1      varchar2(20),
     07:         w_street_2      varchar2(20),
     08:         w_city          varchar2(20),
     09:         w_state         char(2),
     10:         w_zip           char(9)
     11:     )
     12:     partition by range (w_id)
     13:     (
     14:     partition ware_P1 values less than (50)
     15:     tablespace TS_ware_P1
     16:     pctfree 95 pctused 4
     17:     storage (initial 2m next 1m minextents 12 pctincrease 0)
     18:     ,
     19:     partition ware_P2 values less than (100)
     20:     tablespace TS_ware_P2
     21:     pctfree 95 pctused 4
     22:     storage (initial 2m next 1m minextents 12 pctincrease 0)
     23:     ,
     24:     partition ware_P3 values less than (150)
     25:     tablespace TS_ware_P3
     26:     pctfree 95 pctused 4
     27:     storage (initial 2m next 1m minextents 12 pctincrease 0)
     28:     ,
     29:     partition ware_P4 values less than (200)
     30:     tablespace TS_ware_P4
     31:     pctfree 95 pctused 4
     32:     storage (initial 2m next 1m minextents 12 pctincrease 0)
     33:     )
     34:     initrans 4
     35:     pctfree  95 pctused 4
     36:     storage (initial 2m next 1m pctincrease 0);

The command in Listing 27.4 creates a warehouse table with four partitions. The first partition contains rows with warehouses numbered 1 to 50, the second partition contains rows for warehouses 51 through 100, and so on. This scheme of partitioning is called "range partitioned."

Using ALTER to manage partitions
The ALTER TABLE and ALTER INDEX commands allow you to add, delete, move, modify, rename, split, and truncate partitions as and when needed.

You can use the command in Listing 27.5 to create local indexes (line 4 of Listing 27.4) on each partition of this table.

LISTING 27.5  Creating a partitioned index
 

     01:     create unique index tpc.iwarehouse on tpc.warehouse(w_id)
     02:        pctfree 1       initrans 4
     03:        nologging
     04:        local
     05:        (partition iware_P1
     06:        tablespace TS_widx_P1
     07:        storage (initial 200K next 20K pctincrease 0),
     08:        partition iware_P2
     09:        tablespace TS_widx_P2
     10:        storage (initial 200K next 20K pctincrease 0),
     11:        partition iware_P3
     12:        tablespace TS_widx_P3
     13:        storage (initial 200K next 20K pctincrease 0),
     14:        partition iware_P4
     15:        tablespace TS_widx_P4
     16:        storage (initial 200K next 20K pctincrease 0));

Advance Queuing

Oracle's advance queuing is an interprocess messaging mechanism. Messaging between processes is normally synchronous in a two- or three-tier application environment. In this type of mechanism, the client and the server follow a real-time handshake mechanism where the client sends a request to the server and then waits for acknowledgment from the server. The server responds to the client and in turn waits for its acknowledgment.

Queuing technology used in critical systems
Queuing technology is used to develop/implement high-end transaction processing solutions in banking, trading, and reservation systems, as well as other critical applications.

In each case, the sender of the message waits for the acknowledgment before proceeding with the next step. In certain types of processing this is a must; however, in many situations it may be acceptable for the sender to send the message and proceed without waiting for the acknowledgment. A queue mechanism can be used to exchange messages under such situations (see Figure 27.5).

Figure 27.5 : A queue acts as intermediate message storage between sender and receiver.

Understanding the Components of Oracle Advance Queuing

Oracle Advance Queuing's operating mechanism can be compared to that of a mailing service; its components are very similar to that of real-life mailing services. Oracle's Advance Queuing feature consists of queues (similar to mail boxes), messages composed of the control information and the data to be sent (the envelopes containing the letter and address information), enqueue and dequeue operations (dropping the letter in the mail box and retrieving the mail designated for you), and agents (the customers using the mail services).

Granting Necessary Roles and Privileges

Oracle creates two additional roles for advance queue operations:

Before users can use or administer the advance queuing features, they need to be granted these roles. The following command appoints the user aq_admin as the queue administrator: 

$sqlplus sys/password
SQL> grant AQ_ADMINISTRATOR_ROLE to aq_admin with admin option ;
SQL> execute dbms_aqadm.grant_type_access('aq_admin');

Note that the grant_type_access procedure is used to grant necessary privileges for the administrative operations involving a multiple-consumer queue. After these commands are executed, the user aq_admin can then assign privileges so that another user can utilize the Advance Queue option. User aq_admin can also use the Advance Queue feature with this role. We will use this user for the sample commands.

Figure 27.6 shows the sequence of operations necessary for using the advance queues.

Figure 27.6 : Perform this sequence of operations to use the advance queues.

Creating the Queue Table

The DBMS_AQADM package's create_queue_table procedure is used to create a queue table (see Listing 27.6).

LISTING 27.6  Creating an object to be used as a message
Creates event_object_t object datatype to be used as message in advance queue


The parameters for the create_queue_table procedure are as follows:

The command in Listing 27.6 creates sample_queue_table, which stores the user-defined datatype object event_object_t, which needs to be defined before queue creation. Users can find information about the user-owned queue from the data dictionary table USER_QUEUE_TABLES: 

SQL> select * from user_queue_tables ;

QUEUE_TABLE                    TYPE
------------------------------ -------
OBJECT_TYPE
-----------------------------------------------------------
SORT_ORDER             RECIPIEN MESSAGE_GROUP
---------------------- -------- -------------
USER_COMMENT
--------------------------------------------------
SAMPLE_QUEUE_TABLE              OBJECT
AQ_ADMIN.EVENT_OBJECT_T
PRIORITY, ENQUEUE_TIME SINGLE   NONE
sample queue table
Need to see all queue tables?
The DBA_QUEUE_TABLE dictionary view contains information about all the queue tables in the database.

You can use the dbms_aqadm.drop_queue_table procedure to drop a queue table.

Creating a Queue

When the queue table is created, users can create the desired queue by using the dbms_aqadm package's create_queue procedure (see Listing 27.7).

LISTING 27.7  The create_queue procedure
 

     01:     begin
     02:         dbms_aqadm.create_queue
     03:         (
     04:           queue_name            => 'SAMPLE_QUEUE',
     05:           queue_table           => 'SAMPLE_QUEUE_TABLE',
     06:           comment               => 'sample queue'
     07:         );
     08:     end ;
     09:     /

This procedure takes the following input parameters:

The data dictionary view USER_QUEUES has the following columns. You can choose either all the columns or select columns from this view to receive information about the queues you defined.

Need to see all defined queues?
The DBA_QUEUES view contains information about all queues defined in the database. 

SQL> desc user_queues
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(30)
 QUEUE_TABLE                     NOT NULL VARCHAR2(30)
 QID                             NOT NULL NUMBER
 QUEUE_TYPE                               VARCHAR2(15)
 MAX_RETRIES                              NUMBER
 RETRY_DELAY                              NUMBER
 ENQUEUE_ENABLED                          VARCHAR2(7)
 DEQUEUE_ENABLED                          VARCHAR2(7)
 RETENTION                                VARCHAR2(40)
 USER_COMMENT                             VARCHAR2(50)

Use the dbms_aqadm.drop_queue procedure to drop a queue.

Starting the Queue

After creating the queue, the queue administrator must start the queue before it can be used to store and retrieve messages. Use the dbms_aqadm.start_queue procedure to start the queue: 

begin
        dbms_aqadm.start_queue
        (
          queue_name    => 'SAMPLE_QUEUE'
        );
end;
/

The USER_QUEUES view's ENQUEUE_ENABLED and ENQUEUE_DISABLED columns show the start and stop status of user queues.

You can use the dbms_aqadm.stop_queue procedure to stop a started queue.

Enqueing and Dequeing Messages

The DBMS_AQ package has enqueue and dequeue procedures used for storing and retrieving messages through a queue. The sender process agent sends the message by using the following parameters of the enqueue procedure:

Input parameters for dequeue procedure
The dequeue procedure of the DBMS_AQ package takes input parameters similar to the one used for enqueue procedure, except that it uses dequeue_options instead of enqueue_options. Dequeue options, however, aren't a mandatory parameter.

Listing 27.8 shows how to send and receive messages by using advanced queuing.

LISTING 27.8  How to use queues for sending and receiving messages
 

     01:     file enqueu.sql
     02:     *****************************************************
     03:     declare
     04:     enqueue_options       dbms_aq.enqueue_options_t;
     05:     message_properties    dbms_aq.message_properties_t;
     06:     message_handle        RAW(16);
     07:     message                 aq_admin.event_object_t;
     08:     
     09:     begin
     10:     dbms_aq.enqueue(
     11:     queue_name            => 'sample_queue',
     12:     enqueue_options       => enqueue_options,
     13:     message_properties    => message_properties,
     14:     payload               => message,
     15:     msgid                 => message_handle);
     16:     
     17:     commit;
     18:     end;
     19:     /
     20:     *******************************************************
     21:     file dequeu.sql
     22:     *******************************************************
     23:     declare
     24:     dequeue_options      dbms_aq.dequeue_options_t;
     25:     message_properties   dbms_aq.message_properties_t;
     26:     message_handle       RAW(16);
     27:     message              event_object_t;
     28:     begin
     29:     
     30:     dbms_aq.dequeue(
     31:     queue_name           => 'sample_queue',
     32:     dequeue_options      => dequeue_options,
     33:     message_properties   => message_properties,
     34:     payload              => message,
     35:     msgid                => message_handle);
     36:     
     37:     dbms_output.enable;
     38:     dbms_output.put_line
     39:          ('Message retrieved from the queue>>>: '
     40:                     || message.event_call || '    '||
     41:                     message.num_arg||'   ' || 
     42:                       message.arguments);
     43:     commit;
     44:     end;
     45:     /
     46:     *******************************************************
     47:     
     48:     SQL> @enqueu.sql
     49:     
     50:     PL/SQL procedure successfully completed.
     51:     
     52:     SQL> @dequeu.sql
     53:     Message retrieved from the queue>>>:     Sample Message    1   First sample message
     54:     
     55:     PL/SQL procedure successfully completed.
     56:     
     57:     SQL>

Using Other DBMS_AQADM Functions

Many of the procedures available in this package have been discussed, and their usage should be obvious to you by now. The procedures not mentioned until now are as follows:

The DBMS_AQ Package

This package has procedures used for enqueue and dequeue operations. Please refer to the enqueue and dequeue example given earlier in this chapter for more details.

The ConText Option

An RDBMS allows you to store and retrieve data in tabular form. This works very well for most business information that's in the form of numbers and a character string of smaller length. With the proliferation of computer technology and the popularity of personal computers, however, the soft copy of textual documents such as memos, letters, news item, bulletins, electronic mails, HTML files, résumés, reports, and so on make up a very important part of the business and need efficient and effective handling. Oracle8's ConText option allows you to store and manipulate these textual documents in the following ways:

ConText is a separately priced cartridge
The ConText option is released as separate cartridge with Oracle8. This cartridge not only has powerful test/document storage retrieval capabilities, but also has powerful linguistic capabilities.

Table 27.1 shows the datatypes available.

TABLE 27.1  Storing text data in an Oracle database
Datatype:
Characteristics:
VARCHAR2Can store only 4,000 characters. Character strings functions can be used to manipulate the data.
LONGCan store up to 4GB of data. Doesn't allow string functions to be used directly. Stored text can be manipulated via ConText option.
LONG RAWCan store structured text documents. Doesn't allow string functions to be used directly. Stored text can be manipulated via the ConText option.

Figure 27.7 depicts these two methods storing text in the picture form.

Figure 27.7 : The Oracle8 database ConText option stores documents internally and externally.

You can view the Oracle ConText option as a set of procedures and PL/SQL programs. Once it's installed and configured, the ConText option provides application (front-end) developers with a set of functions and procedures; these things allow developers to retrieve and manipulate the text data stored in an Oracle database.

Text-enabling the database server
Before you can execute a text query, you need to text-enable the database instance by setting the initialization parameter TEXT_ENABLE to TRUE or by using the ALTER SESSION set text_enable=true; command.

Enable Oracle Server to process text documents

  1. Install the ConText option. (Please refer to the platform-specific Installation and Configuration Guide from Oracle.)
  2. Configure and set up the ConText option. (See the later section "Managing the Context Option.")
  3. Create a table with a text column to store the documents.
  4. Load the documents in the table. (See the later section "Loading the Data.")
  5. Create a policy on the text column containing the text data. (See "Creating a Policy" later in this chapter.)
  6. Create a text index on the column in which text is stored. (See the later section "Creating the Text Index.")

Setting Up the Sample Table for the Query Examples

The query examples use the table white_papers. The code in Listing 27.9 was used to set up the sample session.

LISTING 27.9  Preparing a table for ConText queries
Creates the table
Loads document file in sample table with ctxload context loader (document files located in directory from where context loader is being invoked)
Contents of the loader control file white_paper.ld
Document files located in the directory from where the context loader is being invoked
Creates policy for white_paper table
Creates text index for white_paper table's already-defined policy

Start a context server with DDL personality before defining a policy
To define a policy, you must have a context server with DDL personality running. You can check the status of the available context servers by using the ctxctl utility's STATUS command.

One-Step Query Examples

After the operations in Listing 27.9 are complete, you can perform the text queries on the designated column of a given table. Now use the table white_paper to learn the use of ConText queries.

The following query looks for white papers that contain the word Manager: 

select paper#,name,author from white_papers
  where contains (white_papers.papers,'Manager') > 0 ;

This query uses a simple method known as the one-step query. Because one-step queries are simpler to write, they're preferred for interactive use. They are slower, however, and the intermediate results aren't available for use. Oracle also offers two-step and in-memory query methods for advanced queries with improved performance; these query types are discussed later in this chapter.

The CONTAINS function lets you use several operators to combine multiple search words and conditions. Table 27.2 lists the important operators available.

TABLE 27.2  CONTAINS function operators
Operator:
Function:
AND or &
Selects the rows that contain both search conditions specified with AND
OR or |
 Selects the rows that contain either search condition specified
Minus or -
Selects a record if the score of the first record minus the score of the second record exceeds the threshold
ACCUM or ,
Selects a record if the sum of search scores exceed the threshold
;
Near; returns the score based on proximity of the specified words
$
Stem; selects the rows that have stem expansions of the specified word
!
Soundex; returns a record that contains words sounding similar to the specified word
?
Fuzzy; performs a fuzzy match (expands the word to include words spelled similarly)
>
Threshold; selects all records having scores exceeding the threshold
*
Weight; assigns different weights to the scores of the searches
:
Max; selects specified number of highest scoring records
%
Wildcard; performs a multiple-character wildcard search
-,
Performs a single-character wildcard search
()
Grouping; specifies the order in which search criteria is evaluated
{},
Escape; allows you to include a reserved word in a query by including it within braces
SYN
Searches for all words defined as synonyms of the specified word
RT
Searches for all words defined as related to the specified word
BT
Searches for words defined as broader terms for the specified word

By using the CONTAINS function in a one-step query, you can perform the following text type queries:

Scores returned by the CONTAINS function
The CONTAINS function of Oracle's ConText option returns an integer that you can use to compare the results of multiple hits from the same CONTAINS function or from multiple CONTAINS functions. 

select paper#,name,author from white_papers
  where contains 
  (white_papers.papers,'customer and packets') > 0;

select name,author from white_papers where
  contains (white_papers.papers,'{dog and cat}') > 0;

select name,author from white_papers where
  contains (white_papers.papers,'zip not dog') > 0;

SQL > select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'dog near zip') > 0
SQL> /

NAME                 AUTHOR                   SCORE0
-------------------- -------------------- ----------
IIII                 IIII                         97
JJJJ                 JJJJ                         93
KKKK                 KKKK                         73

SQL> select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'dog near zip') > 75
SQL> /

NAME                 AUTHOR                   SCORE0
-------------------- -------------------- ----------
IIII                 IIII                         97
JJJJ                 JJJJ                         93

select Name, Author
from WHITE_PAPERS
where CONTAINS (WHITE_PAPERS,'backup > 40') > 0;

SQL> select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'dog near zip:1') > 0;

SQL> select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'dog near zip:10') > 0;

select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'$cache') > 0;

SQL >select name,author,score(0)
  from white_papers where contains
  (white_papers.papers,'cac%') > 0;

Understanding the CONTAINS Function

By looking at the examples given in the previous sections, the CONTAINS function's simple syntax should be clear to you by now. The CONTAINS function's complete syntax is as follows:
Specifies table name and column name to be queried
Specifies text to be searched and conditions
Labels score generated by CONTAINS; has a default value of 0 in queries with single CONTAINS function and requires a specified label in a query with multiple CONTAINS functions
Specifies policy to be used for columns having multiple policies defined

Two-Step Query Example

The Oracle ConText option processes queries in two steps:

Text tables require primary keys
Any table that will be used for text searches by the Oracle ConText option must have a primary key defined before a policy and a text index can be created on it.

If you try to create a text policy for a table that doesn't have a primary key, errors similar to the following are displayed: 

SQL> execute ctx_ddl.create_policy
     ('PAPER2_policy','white_paper2.papers');
     begin ctx_ddl.create_policy
     ('PAPER2_policy','white_paper2.papers'); end;

*
ERROR at line 1:
ORA-20000: ConText error:
DRG-10503: textkey must be specified --          table has no primary key
ORA-06512: at "CTXSYS.DRUE", line 180
ORA-06512: at "CTXSYS.CTX_DDL", line 1329
ORA-06512: at line 1

In a one-step query, the intermediate table isn't preserved by Oracle and you can't reuse its results. In a two-step query, the user creates the intermediate query before the query can be run. Use the following command to create the intermediate table: 

create table query_temp(
  textkey varchar2(64),
  score   number,
  conid   number
  );

After creating the intermediate query table, you can proceed to execute the query: 

execute ctx_query.contains('WH_PPR_POLICY','dog','QUERY_TEMP');
select a.score, a.conid, b.paper#, b.author, b.name
  from query_temp a, white_papers b
  where  a.textkey = b.paper#;

In-Memory Query Example

The preceding example shows how an intermediate table is required to execute a two-step query. In-memory queries use a cursor in memory rather than in the database table to store the intermediate result (see Listing 27.10).

LISTING 27.10  An in-memory query
Defines the query search condition
Defines the cursor
Enables the output
Opens the cursor for intermediate results
Prints the column titles
Fetches the results in the cursor
Prints the results to output
Closes the cursor

Managing the ConText Option

Setting up and managing the ConText option can be divided into the following tasks:

Setting Up and Managing Context Users

When the ConText option is installed, it automatically creates CTXSYS and CTXDEMO users. The installation process also creates CTXADMIN, CTXAPP, and CTXUSR roles. Table 27.2 lists the functions and privileges of these users and roles.

TABLE 27.3  Predefined context users, roles, and their functions and privileges
Name:
Functions and Privileges:
CTXSYSOwns the ConText data dictionary; is given the CTXADMIN and DBA roles; can start and stop the context server processes
CTXDEMOOwns the sample ConText database objects; is given CTXAPP role
CTXADMINThe most privileged context role; can perform all ConText administration tasks
CTXAPPCan manage the ConText data dictionary, set up linguistics services, and perform text queries
CTXUSRShould be given to a normal ConText user who needs to perform ConText queries

You can create additional ConText option users and assign them the appropriate roles. See Chapter 9 "Creating and Managing User Accounts," for more information.

Starting, Stopping, and Managing Context Servers

Context servers are the background processes dedicated to performing context-related operations. These processes must be started before you can perform any text operations. A context server can load data in the database, create text index, and perform text queries. You can assign specific tasks to be performed by each context server by specifying the personality mask while starting the context server. The ctxctl context control utility lets you manage the context servers interactively.

The following help session shows how start and stop context servers query their status by using the ctxctl utility: 

*** ConText Option Servers Control ***

Servers on que_sun1.

Type help for a list of commands.

command> help

The following commands are available:

   help [command]                   - commands information
   status                           - show running servers
   start n [ling | query | ddl | dml |
                          load]...  - start n servers
   stop [pid]... | all              - stop server processes
   quit                             - terminate ctxctl
   exit                             - terminate ctxctl

command> start 1 query ddl dml
Enter ConText Option administrator password for V804><password>
Waiting for servers to start up..........

command> status
+-------+-------+-------+-------+------+-------+
|  PID  | LING. | QUERY |  DDL  |  DML |  LOAD |
+-------+-------+-------+-------+------+-------+
| 12981 |       |     X |     X |    X |       |
+=======+=======+=======+=======+======+=======+
| Total |     0 |     1 |     1 |    1 |     0 |
+-------+-------+-------+-------+------+-------+
command> stop 12981

You can also use ctxsrv to start and stop the context servers from the command line: 

ctxsrv -user ctxsys/password -personality QDML
Personality and personality masks
A context server process can perform any of the functions defined here. The type of functions performed by the server gives the server its personality. A personality mask is used to define the server's personality while starting the server. To change the personality of an existing context server, use the PL/SQL procedure execute ctx_adm.change_mask ('DRSRV_9999', 'QDML');, which allows an existing server, DRSRV_9999, to perform query, DDL, DML, and linguistics functions.

Defining Personality for Context Servers

You can divide the functions performed by context servers into five categories: 

ctxsrv -user ctxsys/password -personality D

ctxsrv -user ctxsys/password -personality M

ctxsrv -user ctxsys/password -personality Q

ctxsrv -user ctxsys/password -personality L
Linguistics queries
The Oracle ConText option supports linguistics queries on text tables. These queries let you search documents with a search criteria containing gists and themes. A detailed discussion on setting up tables for these queries and creating these queries is beyond the scope of this book, and it's recommended that you refer to suitable Oracle documentation for this.

The CTX_ALL_SERVERS view gives the status information of all started context servers. The CTX_SERVERS view provides the status information for the active context servers.

Managing Context Queues

Context servers are responsible for executing the text commands received from all context-user client processes. Oracle has implemented a queuing mechanism to allow the processing of requests received from multiple clients by few servers.

Viewing context queue information
You can use the CTX_ALL_DML_QUEUE,CTX_ALL_DML_SUM, and CTX_ALL_QUEUE data dictionary views to see the status of various context queues. CTX_USER_DML_QUEUE,CTX_USER_DML_SUM, and CTX_USER_QUEUE data dictionary views can be used to see user-level activity. Oracle also offers the CTX_SVC and CTX_ADM packages, which you can use to view queue information and administer queues.

Client processes put their processing requests in a queue known as the Text Request Queue. This queue is internally subdivided into multiple queues, with one queue for each function. Context servers regularly scan each request queue according to their personality and execute the desired operations. The context server informs the client process when the processing is finished. The Text Request Queue is used for query, DML, DDL, and linguistics operations only. The load operation isn't queued; instead, the server with the loader personality detects and processes the newly placed files as described in the preceding section.

Using the Context Loader to Load Data

The context loader is an easy-to-load utility that allows you to load formatted and text data into an Oracle table's LONG or LONG RAW columns. Listing 27.11 shows a simple usage of the context loader-loading the data in the database.

LISTING 27.11  Using the context loader to load data 

     01:     ctxload -user ctxdemo/ctxdemo -name white_papers 
	         -file white_paper.ld -separate
     02:     -log $HOME/vlunawat/ctxload.log
     03:     
     04:     $ more white_paper.ld
     05:     <textstart:PAPER#=1,NAME='AAAA',AUTHOR='AAAAA'>
     06:     doc1.rtf
     07:     <textend>
     08:     <textstart:PAPER#=2,NAME='BBBB',AUTHOR='BBBB'>
     09:     doc2.rtf
     10:     <textend>
     11:     <textstart:PAPER#=3,NAME='CCCC',AUTHOR='CCCC'>
     12:     doc3.doc
     13:     <textend>
     14:     <textstart:PAPER#=8,NAME='HHHH',AUTHOR='HHHH'>
     15:     doc8.doc
     16:     <textend>
     17:     <textstart:PAPER#=8,NAME='HHHH',AUTHOR='HHHH'>
     18:     doc8.doc
     19:     <textend>
Specifies the name of theloader log file

On line 1, -user specifies the username and the password, -name specifies the name of the database table in which data is loaded, and -file specifies the name of the loader control file. The contents of the loader control file used in the chapter's example are shown here.

Also on line 1: -separate specifies that the contents of the load file contain a pointer to a file holding the document and that each document is to be loaded in one row of the table.

Storing Documents in OS Files

To store the documents in OS files rather than in database tables, follow along with these prompts: 

SQL> create table docs (doc# number primary key,
     author varchar2(20), paper long);

Table created.

SQL> execute ctx_ddl.set_attribute
     ('PATH','/home/oracle/ctxdemo');

PL/SQL procedure successfully completed.

SQL> execute ctx_ddl.create_preference
     ('COMMON_DIR','comment','OSFILE');

PL/SQL procedure successfully completed.

SQL> insert into docs values
     (1,'ABC','/home/oracle/ctxdemo/doc2.doc');

1 row created.

SQL> insert into docs values
     (2,'BCD','/home/oracle/ctxdemo/doc1.rtf');

1 row created.

SQL> commit ;


Commit complete.

SQL> exec ctx_ddl.create_policy('DOC_POLICY','DOCS.PAPER');

PL/SQL procedure successfully completed.

SQL> execute ctx_ddl.create_index('DOC_POLICY');

PL/SQL procedure successfully completed.

© Copyright, Macmillan Computer Publishing. All rights reserved.