Perl Cd Bookshelf [Electronic resources] نسخه متنی

Setting Up the Oracle XML Database

To make sure that you can run the examples in the upcoming chapters, you need to have access to Standard or Enterprise Edition of the Oracle Database 10g database and follow the setup procedures explained in this section.

Installing Oracle XML Database

If you installed the Oracle Database 10g database using Database Configuration Assistant (DBCA), you do not need to perform extra steps to set up Oracle XML DB because it is set up by default. Otherwise, after the database installation, you need to perform the following steps to enable the Oracle XML DB functionality:

Create an Oracle XML DB tablespace (as XDB) for Oracle XML DB Repository.

Enable protocol access.

You need to connect as the SYS user and run the SQL scripts in the $ORACLE_HOME/rdbms/ admin (or %ORACLE_HOME%\rdbms\admin for Windows) directory. These steps assume your Oracle Database 10g installation is in D:\oracle, which is the Oracle home directory.

Log in to a SQL*Plus session and connect as the SYS user with SYSDBA privilege:

OSPrompt> SQLPLUS "SYS/<sys_password> as sysdba"

Create a new XDB tablespace as follows:

CREATE TABLESPACE XDB LOGGIN
DATAFILE ''''''''D:\oracle\oradata\ORCLX\xdb.dbf''''''''
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 100M
EXTENT MANAGEMENT LOCAL;

Then, run the catqm.sql script to create the XDB database registry, PL/SQL packages, and so forth:

SQL> @catqm.sql <XDBUSER_password> <XDB_TS_NAME> <TEMP_TS_NAME>

For example, the following command creates the XDB user using the XDB tablespace and sets the temporary tablespace to be TEMP (the password for the XDB user is XDBPW):

SQL> @catqm XDBPW XDB TEMP

After all the tablespaces and built-in PL/SQL packages are created, you can reconnect to SQL*Plus as a SYS user with the SYSDBA privilege and run the catxdbj.sql script to load XML DB Java libraries:

SQL> @catxdbj

If you like to install the XDK packages, such as the DBMS_XMLQUERY and DBMS_XMLSAVE PL/SQL packages, you need to run initxml.sql as follows:

SQL> @initxml.sql

Finally, you need to shut down and restart the database.

Now, you have set up the database schema for Oracle XML DB, created the supporting PL/SQL packages, and set up the associated component registries in the Oracle database. Next, you need to set up the XML DB HTTP and FTP listeners to allow users to connect to XML DB Repository through the WebDAV/HTTP and FTP protocols.

First, you need to make sure the Oracle Net Services are started. To check the status, you can use the lsnrctl status command in a command-line window.

As shown in Figure 8-1, the XML DB service, orclXXDB, (<Oracle SID>XDB naming convention), and the XML DB HTTP and FTP listeners have started. By default, the HTTP listener listens on port 8080 and the FTP listener uses port 2100.

Figure 8-1: Oracle Net Services status

If the status is not as shown in Figure 8-1, you need to perform the following steps, assuming you have the default port 1521 registered for the database listener:

Initializing the Oracle JVM

If the Oracle JVM is not initialized, you must initialize it before loading the Java package for the XSU PL/SQL packages. To initialize the JVM:

Run the initjvm.sql and initdbj.sql scripts in the $ORACLE_HOME/javavm/install (%ORACLE_HOME%\javavm\install for Windows) directory.

When you upgrade the XDK PL/SQL packages, you need to uninstall the XDK with the rmxml.sql utility and reload the new XDK PL/SQL packages into Oracle JServer with the initxml.sql utility in the $ORACLE_HOME/rdbms/admin directory. However, you do not need to reinitialize the Oracle JVM during this reloading process.

By default, the XDK PL/SQL packages are loaded into the SYS or SYSTEM user. However, for easier maintenance when reloading Java packages is needed, it is suggested to load them into a specific user schema. For example, you can create a user named XDK and load the Java classes to this user''''''''s schema. This keeps them separated from the other Java classes, thus eliminating potential compatibility problems during upgrades. You should also notice that, by default, the public synonyms are created for the package names, and the execution privileges are granted to PUBLIC.

To set up the TCP dispatcher, you need to add the dispatchers parameter to a server parameter file (SPFILEORCLX.ORA) first:

dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"

For example, assuming your instance is ORCLX, you need to run the following command as the SYS user:

 SQL> ALTER SYSTEM SET dispatchers=''''''''(PROTOCOL=TCP)(SERVICE=ORCLXXDB)'''''''' SCOPE=SPFILE;
SQL> SHOW PARAMETERS dispatchers;

Then, you need to stop and restart the listener by running the following in the command-line window:

OSPrompt> lsnrctl stop
OSPrompt> lsnrctl start

Finally, you need to shut down and restart the database from SQL*Plus:

SQL> shutdown immediate;
SQL> startup

To make sure the listeners work properly and the XML DB service is registered, you can check the listener status by running the following command:

OSPrompt> lsnrctl status

You should now be able to see the XML DB service and the HTTP and FTP listeners up and running.

If the database listener does not use the default 1521 port (i.e., 1581), the XML DB service and FTP and HTTP listeners cannot be dynamically registered to the listener. Therefore, you need extra setup steps. For example, if you have the following Oracle Net Service defined in $ORACLE_ HOME/network/admin/tnsname.ora (or %ORACLE_HOME%\network\admin\tnsname.ora on Windows):

ORCLX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop)(PORT = 1581)) 
)
(CONNECT_DATA =
(SERVER = DEDICATED) 
(SERVICE_NAME = ORCLX) 
)
)

and you have the listener defined in the following section of the listener.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) 
) 
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mylaptop)(PORT = 1581)) 
) 
) 
)

then you need to configure the LOCAL_LISTENER parameter in the initialization parameter file referring to the TNSNAME entry specified in the tnsnames.ora that points to the correct listener. You can run the following command in SQL*Plus to update the SPFILE file:

SQL> ALTER SYSTEM SET dispatchers=''''''''(PROTOCOL=TCP)(LISTENER=ORCLX)'''''''' SCOPE=SPFILE; 
SQL> SHOW PARAMETERS local_listener;

Finally, you also need to shut down and restart the database as follows:

SQL> shutdown immediate; 
SQL> startup

Oracle XML DB allows you to change the default configurations of the HTTP and FTP ports. However, that process is not discussed here, because the preceding setups are enough for you to get started to run the XML DB samples. Such management and setup details are discussed in Chapter 12, which presents some tips and techniques for using the XML DB features.

Installing the Sample Database Schemas

The Oracle sample database schemas are used in the examples for the book. However, the installation of the sample schema is an option when creating a database with DBCA in Oracle Database 10g. If the sample schemas are not installed during database creation, you need to start SQL*Plus to run the following command in the $ORACLE_HOME or %ORACLE_HOME% directory:

SQL> @demo\schema\mksample <SYS_PASSWORD> <SYSTEM_PASSWORK> HR OE PM QS SH BI EXAMPLE TEMP D:\temp\log

The mksample.sql script requires you to supply the SYS and SYSTEM password followed by the password for the six sample schema users, HR, OE, PM, QS, SH, and BI. To help remember the password for each user, you can specify the password same as the user names. For example, the password for user HR is HR. Then, you need to specify the default tablespace (i.e., EXAMPLE), the temporary tablespace (i.e., TEMP), and the directory where you want the log file to be generated for the sample schema installation.

Upon installation, the sample schemas are created. The six sample schemas build up a general database schema for company management, which consists of different divisions represented by different users:

HR The Human Resource division contains information about the company’s employees and the organizational structure.

OE The Ordering Entry division tracks the product inventory and fulfills purchase orders of products through different channels.

PM The Product Media division maintains the product-related information.

QS The Quality Shipping division manages the shipping of the products to customers.

SH The Sale History division tracks business statistics.

BI The Business Intelligence division provides decision support information.

The XML generation and Object-Relational (O-R) XMLType storage examples will use the HR schema, and the XML-based content management samples will be built on the PM schema.

Creating a User to Run the XMLType Samples

You need to create a temporary user account to run the examples of XMLType in this chapter. For example, you can run the following SQL commands in SYS user to create a user named demo:

CREATE USER demo IDENTIFIED BY demo
DEFAULT TABLESPACE USERS
QUOTA 50M ON USERS
TEMPORARY TABLESPACE TEMP;

This user needs to have the following privileges:

CREATE SESSION

CREATE TABLE

CREATE PROCEDURE

CREATE TYPE

CTXAPP ROLE

You can grant the privileges as follows:

GRANT CREATE SESSION TO demo;
GRANT CREATE TABLE TO demo;
GRANT CREATE PROCEDURE to demo;
GRANT CREATE TYPE TO demo;
GRANT CTXAPP TO demo;

The first three privileges are granted for basic DDL operations. The third one has to be granted to create object types during the XML Schema registration. The CTXAPP role is used to create and synchronize Oracle Text indexes.

Setting Up a WebDAV Folder

WebDAV support in Oracle XML DB allows you to create WebDAV folders in the Windows environment. It makes accessing the Oracle XML DB Repository just like accessing any other folder on your disk drive. The following instructions work through the procedure to set up a WebDAV Folder in a Windows XP system:

From the START menu, open My Network Places.

Click Add A Network Place, which allows you to pick up the kind of network place to add.

Click Next, choose Choose Another Network Connect, and then type http:// <host-name>:8080/ as the address. You can also use 127.0.0.1 or localhost for your hostname.

Click Next. A window pops up asking for login information. You can type any valid database username and password here to log in to XML DB. Note that different users may have different views of data because of the security protection in the XML DB Repository.

Click OK. In the next wizard window, type in the name XML DB Repository and click Next.

Deselect the Open Folder By Default option and then click Finish to complete the wizard.

Back in the window My Network Places window, you will now see that the folder XML DB Repository has been created, as shown in Figure 8-2.

Figure 8-2: WebDAV folder created

Double-click the XML DB Repository folder icon. You will be asked to log in. You can use the same database username and password to log in. The web folder opens, as shown in Figure 8-3.

Figure 8-3: Example Oracle XML DB Repository

Now that you have created the WebDAV folder, any application that supports WebDAV, such as Microsoft Word, can use this folder to access or save files in the XML DB Repository. The setup of the folder is complete and will be used later.