Oracle读取文件

Create directory让我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性。
其语法为:
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

本案例具体创建如下:

create or replace directory exp_dir as '/tmp';

目录创建以后，就可以把读写权限授予特定用户，具体语法如下:
GRANT READ[,WRITE] ON DIRECTORY directory TO username;

例如:

grant read, write on directory exp_dir to eygle;

此时用户eygle就拥有了对该目录的读写权限。

让我们看一个简单的测试:

SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file'; Directory created. SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 5 utl_file.put_line(fhandle , 'eygle test write one'); 6 utl_file.put_line(fhandle , 'eygle test write two'); 7 utl_file.fclose(fhandle); 8 end; 9 / PL/SQL procedure successfully completed. SQL> ! [oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt eygle test write one eygle test write two [oracle@jumper 9.2.0]$

类似的我们可以通过utl_file来读取文件:

SQL> declare 2 fhandle utl_file.file_type; 3 fp_buffer varchar2(4000); 4 begin 5 fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R'); 6 7 utl_file.get_line (fhandle , fp_buffer ); 8 dbms_output.put_line(fp_buffer ); 9 utl_file.get_line (fhandle , fp_buffer ); 10 dbms_output.put_line(fp_buffer ); 11 utl_file.fclose(fhandle); 12 end; 13 / eygle test write one eygle test write two PL/SQL procedure successfully completed.

可以查询dba_directories查看所有directory.

SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------------------------ SYS UTL_FILE_DIR /opt/oracle/utl_file SYS BDUMP_DIR /opt/oracle/admin/conner/bdump SYS EXP_DIR /opt/oracle/utl_file

可以使用drop directory删除这些路径.

SQL> drop directory exp_dir;

Directory dropped

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            UTL_FILE_DIR                   /opt/oracle/utl_file
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump

2011-01-27更新（此更新转自“骨骨的学习笔记”）

 
 

     
     

      
      
General Information
Note: O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user
Source
{ORACLE_HOME}/rdbms/admin/utlfile.sql
First Availability
7.3.4
      
      
init.ora Parameters
utl_file_dir
utl_file_dir=c:/oraload
utl_file_dir=c:/temp
utl_file_dir=*
Open Modes
 
          
A
Append Text
AB
Append Byte Mode
R
Read Text
RB
Read Byte Mode
W
Write Text
WB
Write Byte Mode
      
      
FCLOSE
Close A File Opened By UTL_FILE
utl_file.fclose(<file_handle>)
see FOPEN demo
 
FCLOSE_ALL
Close All Files Opened By UTL_FILE
utl_file.fclose_all;
set serveroutput on

DECLARE
vInHandle utl_file.file_type;
vOutHandle utl_file.file_type; 
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
vOutHandle := utl_file.fopen('ORALOAD', 'out.txt', 'W');

IF utl_file.is_open(vInHandle) THEN
    utl_file.fclose_all;
    dbms_output.put_line('Closed All');
END IF;
END fopen;
/
 
FCOPY
Copies a contiguous portion of a file to a newly created file
utl_file.fcopy (
location   IN VARCHAR2,
filename   IN VARCHAR2,
dest_dir   IN VARCHAR2,
dest_file IN VARCHAR2,
start_line IN PLS_INTEGER DEFAULT 1,
end_line   IN PLS_INTEGER DEFAULT NULL);
-- demo requires creating directory CTEMP ... see link at bottom of page

BEGIN
utl_file.fcopy('ORALOAD', 'dump.txt', 'ORALOAD', 'didit.txt');
END;
/
 
FFLUSH
Physically writes pending data to the file identified by the file handle
utl_file.fflush (<file_handle>);
See Write demo
 
FGETATTR
Reads and returns the attributes of a disk file
utl_file.fgetattr(
location    IN VARCHAR2, 
filename    IN VARCHAR2, 
exists      OUT BOOLEAN, 
file_length OUT NUMBER, 
blocksize   OUT NUMBER);
set serveroutput on

DECLARE
ex    BOOLEAN;
flen NUMBER;
bsize NUMBER; 
BEGIN
utl_file.fgetattr('ORALOAD', 'test.txt', ex, flen, bsize);

IF ex THEN
    dbms_output.put_line('File Exists');
ELSE
    dbms_output.put_line('File Does Not Exist');
END IF;
dbms_output.put_line('File Length: ' || TO_CHAR(flen));
dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/
 
FGETPOS
Returns the current relative offset position within a file, in bytes
utl_file.fgetpos(fileid IN file_type) RETURN PLS_INTEGER;
See Read-Write demo
 
FOPEN
Open A File For Read Operations
utl_file.fopen(
<file_location IN VARCHAR2>, 
<file_name     IN VARCHAR2>,
<open_mode     IN VARCHAR2>,
<max_linesize IN BINARY_INTEGER>)
RETURN <file_type_package_data_type;
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(250);
BEGIN
vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
LOOP
    BEGIN
      utl_file.get_line(vInHandle, vNewLine);
      dbms_output.put_line(vNewLine);
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
END LOOP;
utl_file.fclose(vInHandle);
END fopen;
/
Open A File For Write Operations
<file_handle> := utl_file.fopen(<file_location, file_name, 'w')
 
FOPEN_NCHAR
Open a file to read or write a text file in Unicode instead of in the database charset
 
FREMOVE
Delete An Operating System File
utl_file.fremove (location IN VARCHAR2, filename IN VARCHAR2);
BEGIN
utl_file.fremove('ORALOAD', 'dump.txt');
END fremove;
/
 
FRENAME
Rename An Operating System File
utl_file.frename (
location IN VARCHAR2,
filename IN VARCHAR2, 
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);
BEGIN
utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);
END frename;
/
 
FSEEK
Adjusts the file pointer forward or backward within the file by the number of bytes specified
utl_file.fseek (
fid             IN utl_file.file_type,
absolute_offset IN PL_INTEGER DEFAULT NULL,
relative_offset IN PLS_INTEGER DEFAULT NULL);
See Read-Write demo
 
GETLINE
Read a Line from a file
utl_file.getline (
file     IN FILE_TYPE,
buffer   OUT VARCHAR2,
linesize IN NUMBER,
len      IN PLS_INTEGER DEFAULT NULL);
See Read demos
 
GETLINE_NCHAR
Same as GETLINE except can be used to read Unicode rather than the database's character set
 
GET_RAW
Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
utl_file.get_raw (
fid IN utl_file.file_type, 
r   OUT NOCOPY RAW, 
len IN PLS_INTEGER DEFAULT NULL);
See UTL_MAIL demo
 
IS_OPEN
Returns True If A File Handle Is Open: Otherwise False
utl_file.is_open (file IN FILE_TYPE) RETURN BOOLEAN;
See FCLOSE_ALL Demo
 
NEW_LINE
Writes one or more operating system-specific line terminators to a file
utl_file.NEW_LINE (file IN FILE_TYPE, lines IN NATURAL := 1);
See Read Demo
 
PUT
Writes a string to a file
utl_file.put(
file   IN FILE_TYPE,
buffer IN VARCHAR2);
See Write demo
 
PUTF
A PUT procedure with formatting
utl_file.putf(
file   IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . . 
arg5   IN VARCHAR2 DEFAULT NULL]);
See Write demo
 
PUT_LINE
Writes a line to a file. Appends an operating system-specific line terminator
utl_file.put_line(
file      IN FILE_TYPE,
buffer    IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);
See Read-Write demo
 
PUT_NCHAR
Writes a Unicode string to a file
 
PUT_RAW
Accepts as input a RAW data value and writes the value to the output buffer
utl_file.PUT_RAW (
fid       IN utl_file.file_type,
r         IN RAW, 
autoflush IN BOOLEAN DEFAULT FALSE);
See extract_blob Demo
 
PUT_LINE_NCHAR
Writes a Unicode line to a file
 
PUTF_NCHAR
Writes a Unicode string to a file