Oracle外部表ORACLE_DATAPUMP类型的创建语法详解
This chapter describes the ORACLE_DATAPUMP access driver. The following topics are discussed:
access_parameters Clause
Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver
Supported Datatypes
Unsupported Datatypes
Reserved Words for the ORACLE_DATAPUMP Access Driver
To use the information in this chapter, you must know enough about SQL to be able to create an external table and perform queries against it.
See Also:
Unsupported DatatypesAn external table supports a subset of all possible datatypes for columns. In particular, it supports character datatypes (except LONG), the RAW datatype, all numeric datatypes, and all date, timestamp, and interval datatypes.
This section describes how you can use the ORACLE_DATAPUMP access driver to unload and reload data for some of the unsupported datatypes, specifically:
BFILE
LONG and LONG RAW
Final object types
Tables of final object types
Unloading and Loading BFILE DatatypesThe BFILE datatype has two pieces of information stored in it: the directory object for the file and the name of the file within that directory object.
You can unload BFILE columns using the ORACLE_DATAPUMP access driver by storing the directory object name and the filename in two columns in the external table. The procedure DBMS_LOB.FILEGETNAME will return both parts of the name. However, because this is a procedure, it cannot be used in a SELECT statement. Instead, two functions are needed. The first will return the name of the directory object, and the second will return the name of the file.
The steps in the following extended example demonstrate the unloading and loading of BFILE datatypes.
Create a function to extract the directory object for a BFILE column. Note that if the column is NULL, then NULL is returned.
SQL> CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS 2 DIR_ALIAS VARCHAR2(255); 3 FILE_NAME VARCHAR2(255); 4 BEGIN 5 IF bf is NULL 6 THEN 7 RETURN NULL; 8 ELSE 9 DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name); 10 RETURN dir_alias; 11 END IF; 12 END; 13 /Function created.
Create a function to extract the filename for a BFILE column.
SQL> CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is 2 dir_alias VARCHAR2(255); 3 file_name VARCHAR2(255); 4 BEGIN 5 IF bf is NULL 6 THEN 7 RETURN NULL; 8 ELSE 9 DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name); 10 RETURN file_name; 11 END IF; 12 END; 13 /Function created.
You can then add a row with a NULL value for the BFILE column, as follows:
SQL> INSERT INTO PRINT_MEDIA (product_id, ad_id, ad_graphic) 2 VALUES (3515, 12001, NULL);1 row created.
You can use the newly created functions to populate an external table. Note that the functions should set columns ad_graphic_dir and ad_graphic_file to NULL if the BFILE column is NULL.
Create an external table to contain the data from the print_media table. Use the get_dir_name and get_file_name functions to get the components of the BFILE column.
SQL> CREATE TABLE print_media_xt 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE oracle_datapump 5 DEFAULT DIRECTORY def_dir1 6 LOCATION ('pm_xt.dmp') 7 ) AS 8 SELECT product_id, ad_id, 9 get_dir_name (ad_graphic) ad_graphic_dir, 10 get_file_name(ad_graphic) ad_graphic_file 11 FROM print_media;Table created.Create a function to load a BFILE column from the data that is in the external table. This function will return NULL if the ad_graphic_dir column in the external table is NULL.
SQL> CREATE FUNCTION get_bfile (dir VARCHAR2, file VARCHAR2) RETURNBFILE is 2 bf BFILE; 3 BEGIN 4 IF dir IS NULL 5 THEN 6 RETURN NULL; 7 ELSE 8 RETURN BFILENAME(dir,file); 9 END IF; 10 END; 11 /Function created.
The get_bfile function can be used to populate a new table containing a BFILE column.
SQL> CREATE TABLE print_media_int AS 2 SELECT product_id, ad_id, 3 get_bfile (ad_graphic_dir, ad_graphic_file) ad_graphic 4 FROM print_media_xt;Table created.
The data in the columns of the newly loaded table should match the data in the columns of the print_media table.
SQL> SELECT product_id, ad_id, 2 get_dir_name(ad_graphic), 3 get_file_name(ad_graphic) 4 FROM print_media_int 5 MINUS 6 SELECT product_id, ad_id, 7 get_dir_name(ad_graphic), 8 get_file_name(ad_graphic) 9 FROM print_media;no rows selected
The ORACLE_DATAPUMP access driver can be used to unload LONG and LONG RAW columns, but that data can only be loaded back into LOB fields. The steps in the following extended example demonstrate the unloading of LONG and LONG RAW datatypes.
If a table to be unloaded contains a LONG or LONG RAW column, then define the corresponding columns in the external table as CLOB for LONG columns or BLOB for LONG RAW columns.
SQL> CREATE TABLE long_tab 2 ( 3 key SMALLINT, 4 description LONG 5 );Table created.SQL> INSERT INTO long_tab VALUES (1, 'Description Text');1 row created.
Now, an external table can be created that contains a CLOB column to contain the data from the LONG column. Note that when loading the external table, the TO_LOB operator is used to convert the LONG column into a CLOB.
SQL> CREATE TABLE long_tab_xt 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE ORACLE_DATAPUMP 5 DEFAULT DIRECTORY def_dir1 6 LOCATION ('long_tab_xt.dmp') 7 ) 8 AS SELECT key, TO_LOB(description) description FROM long_tab;Table created.The data in the external table can be used to create another table exactly like the one that was unloaded except the new table will contain a LOB column instead of a LONG column.
SQL> CREATE TABLE lob_tab 2 AS SELECT * from long_tab_xt;Table created.
Verify that the table was created correctly.
SQL> SELECT * FROM lob_tab; KEY DESCRIPTION---------------------------------------- 1 Description Text
Final column objects are populated into an external table by moving each attribute in the object type into a column in the external table. In addition, the external table needs a new column to track whether the column object is atomically null. The following steps demonstrate the unloading and loading of columns containing final object types.
In the following example, the warehouse column in the external table is used to track whether the warehouse column in the source table is atomically NULL.
SQL> CREATE TABLE inventories_obj_xt 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE ORACLE_DATAPUMP 5 DEFAULT DIRECTORY def_dir1 6 LOCATION ('inv_obj_xt.dmp') 7 ) 8 AS 9 SELECT oi.product_id, 10 DECODE (oi.warehouse, NULL, 0, 1) warehouse, 11 oi.warehouse.location_id location_id, 12 oi.warehouse.warehouse_id warehouse_id, 13 oi.warehouse.warehouse_name warehouse_name, 14 oi.quantity_on_hand 15 FROM oc_inventories oi;Table created.The columns in the external table containing the attributes of the object type can now be used as arguments to the type constructor function when loading a column of that type. Note that the warehouse column in the external table is used to determine whether to call the constructor function for the object or set the column to NULL.
Load a new internal table that looks exactly like the oc_inventories view. (The use of the WHERE 1=0 clause creates a new table that looks exactly like the old table but does not copy any data from the old table into the new table.)
SQL> CREATE TABLE oc_inventories_2 AS SELECT * FROM oc_inventoriesWHERE 1 = 0;Table created.SQL> INSERT INTO oc_inventories_2 2 SELECT product_id, 3 DECODE (warehouse, 0, NULL, 4 warehouse_typ(warehouse_id, warehouse_name, 5 location_id)), quantity_on_hand 6 FROM inventories_obj_xt;1112 rows created.
Object tables have an object identifier that uniquely identifies every row in the table. The following situations can occur:
If there is no need to unload and reload the object identifier, then the external table only needs to contain fields for the attributes of the type for the object table.
If the object identifier (OID) needs to be unloaded and reloaded and the OID for the table is one or more fields in the table, (also known as primary-key-based OIDs), then the external table has one column for every attribute of the type for the table.
If the OID needs to be unloaded and the OID for the table is system-generated, then the procedure is more complicated. In addition to the attributes of the type, another column needs to be created to hold the system-generated OID.
The steps in the following example demonstrate this last situation.
Create a table of a type with system-generated OIDs:
SQL> CREATE TYPE person AS OBJECT (name varchar2(20)) NOT FINAL 2 /Type created.SQL> CREATE TABLE people OF person;Table created.SQL> INSERT INTO people VALUES ('Euclid');1 row created.Create an external table in which the column OID is used to hold the column containing the system-generated OID.
SQL> CREATE TABLE people_xt 2 ORGANIZATION EXTERNAL 3 ( 4 TYPE ORACLE_DATAPUMP 5 DEFAULT DIRECTORY def_dir1 6 LOCATION ('people.dmp') 7 ) 8 AS SELECT SYS_NC_OID$ oid, name FROM people;Table created.Create another table of the same type with system-generated OIDs. Then, execute an INSERT statement to load the new table with data unloaded from the old table.
SQL> CREATE TABLE people2 OF person;Table created.SQL> SQL> INSERT INTO people2 (SYS_NC_OID$, SYS_NC_ROWINFO$) 2 SELECT oid, person(name) FROM people_xt;1 row created.SQL> SQL> SELECT SYS_NC_OID$, name FROM people 2 MINUS 3 SELECT SYS_NC_OID$, name FROM people2;no rows selected
When identifiers (for example, column or table names) are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, it must be enclosed in double quotation marks. The following are the reserved words for the ORACLE_DATAPUMP access driver:
BADFILE
COMPATIBLE
DATAPUMP
DEBUG
INTERNAL
JOB
LATEST
LOGFILE
NOBADFILE
NOLOGFILE
PARALLEL
TABLE
VERSION
WORKERID