Oracle读写文件bfilename

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

fhandle utl_filefile_type;

begin

fhandle := utl_filefopen(UTL_FILE_DIR exampletxt w);

utl_fileput_line(fhandle eygle test write one);

utl_fileput_line(fhandle eygle test write two);

utl_filefclose(fhandle);

end;

/

PL/SQL procedure successfully completed

SQL> !

[oracle@jumper ]$ more /opt/oracle/utl_file/exampletxt eygle test write oneeygle test write two[oracle@jumper ]$

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

SQL> declare

fhandle utl_filefile_type;

fp_buffer varchar();

begin

fhandle := utl_filefopen (UTL_FILE_DIRexampletxt R);

utl_fileget_line (fhandle fp_buffer );

dbms_outputput_line(fp_buffer );

utl_fileget_line (fhandle fp_buffer );

dbms_outputput_line(fp_buffer );

utl_filefclose(fhandle);

end;

/

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_fileSYS

BDUMP_DIR /opt/oracle/admin/conner/bdumpSYS 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_fileSYS

BDUMP_DIR /opt/oracle/admin/conner/bdump

create or replace directory USER_DIR as E:\PLSQL\\;

DECLARE

v_content VARCHAR();

v_bfile BFILE;

amount INT;

offset INT :=;

BEGIN

v_bfile := bfilename(USER_DIRtestTXT); 注意这里的 User_dir 对应上面已经创建好啦的目录

amount :=DBMS_LOBgetlength(v_bfile);

DBMS_LOBOPEN(v_bfile);

DBMS_LOBREAD(v_bfileamountoffsetv_content);

DBMS_LOBclose(v_bfile);

DBMS_OUTPUTPUT_LINE(v_content);

END;