DATAFILES Clause

1  DATAFILES  ( + , '
2.1 DISK
2.1 PIPE
1 :
2.1 fixed_path
2.1 formatted_path
1?  ; + ;
2.1 BLOBDIR
2.1 CLOBDIR
1 :
1 fixed_path
1 ' )

The database server does not verify that any file or pipe exists at the specified fixed_path or formatted_path, that the specified pipe is open, nor that the user has permission to access that file system. Subsequent operations on the external table will fail, however, unless the path is valid and, if a named pipe is being used, that it is open, when the database server attempts to read or write to the external table.

For examples of the DATAFILES clause, see External Table Examples.

Keyword

Description

CLOBDIR

Specifies the server directory in which the CLOB file is stored.

BLOBDIR

Specifies the server directory in which the BLOB file is stored. When creating queries, specify DISK followed by BLOBDIR followed by CLOBDIR. If BLOBDIR is omitted, BLOB files are stored the same directory as specified by the DISK clause. If both BLOBDIR and CLOBDIR are omitted, a new file is created for each BLOB or CLOB column and stored in the directory in which the DISK clause is specified.

In the following example, rows stored in /work1/exttab1.dat have their BLOBs located in /work1/blobdir1 and CLOBs in the /work1/clobdir1 directory.

Rows stored in /work1/exttab2.dat have their BLOBs located in the /work1 directory and CLOBs in the /work1/clobdir2 directory. Because the BLOBDIR clause is omitted, the BLOBs are stored in the directory where exttab2.dat is stored.

Rows stored in the /work1/exttab3.dat have their BLOBs and CLOBs located in the /work1 directory because both BLOBDIR and CLOBDIR are omitted.

CREATE EXTERNAL TABLE exttab (
       id    SERIAL,
       lobc  CLOB,
       lobb  BLOB)
USING (DATAFILES(
 "DISK:/work1/exttab1.dat;BLOBDIR:/work1/blobdir1;CLOBDIR:/work1/clobdir1",
 "DISK:/work1/exttab2.dat;CLOBDIR:/work1/clobdir2", 
 "DISK:/work1/exttab3.dat"),
 DELIMITER '|');