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

Using External Tables

Introduced in Oracle9i, Oracle’s external table feature offers a solution to define a table in the database while leaving the data stored outside of the database. Prior to Oracle Database 10g, external tables can be used only as read-only tables. In other words, if you create an external table for XML files, these files can be queries and the table can be joined with other tables. However, no DML operations, such as INSERT, UPDATE, and DELETE, are allowed on the external tables.

In Oracle Database 10g, you can define VARCHAR2 and CLOB columns in external tables to store XML documents. The following example shows how you can create an external table with a CLOB column to store the XML documents. First, you need to create a DIRECTORY to read the data files:

CREATE DIRECTORY data_file_dir AS
 'D:\xmlbook\Examples\Chapter9\src\xml';
GRANT READ, WRITE ON DIRECTORY data_file_dir TO demo;

Then, you can use this DIRECTORY to define an external table:

CREATE TABLE customer_xt (doc CLOB)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS
(
FIELDS (lobfn CHAR TERMINATED BY ',')
COLUMN TRANSFORMS (doc FROM lobfile (lobfn))
)
LOCATION ('xml.dat')
)
REJECT LIMIT UNLIMITED;

The xml.dat file follows:

customer1.xml
customer2.xml

If you describe the table, you can see the following definition:

SQL> DESC customer_xt;
Name               Null? Type
----------------------------------------- -------- ------------------------
DOC            CLOB

Then, you can query the XML document as follows:

SELECT XMLType(doc).extract('/Customer/EMAIL') 
FROM customer_xt;

Though the query requires run-time XMLType creation and XPath evaluation, this approach is useful when applications just need a few queries on the XML data and don’t want to upload the XML data into database. In Oracle Database 10g, you cannot create external tables that contain pre-defined XMLType column types.