oracle 文件读写,Oracle读写文件bfilename

Oracle读写文件bfilename以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！

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_file file_type;

begin

fhandle := utl_file fopen( UTL_FILE_DIR example txt w );

utl_file put_line(fhandle eygle test write one );

utl_file put_line(fhandle eygle test write o );

utl_file fclose(fhandle);

end;

/

PL/SQL procedure successfully pleted

SQL> !

[oracle@jumper ]$ more /opt/oracle/utl_file/example txt eygle test write oneeygle test write o[oracle@jumper ]$

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

SQL> declare

fhandle   utl_file file_type;

fp_buffer varchar ( );

begin

fhandle := utl_file fopen ( UTL_FILE_DIR example txt R );

utl_file get_line (fhandle fp_buffer );

dbms_output put_line(fp_buffer );

utl_file get_line (fhandle fp_buffer );

dbms_output put_line(fp_buffer );

utl_file fclose(fhandle);

end;

/

eygle test write one

eygle test write o

PL/SQL procedure successfully pleted

可以查询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_DIR test TXT ); 注意这里的 User_dir 对应上面已经创建好啦的目录

amount :=DBMS_LOB getlength(v_bfile);

DBMS_LOB OPEN(v_bfile);

DBMS_LOB READ(v_bfile amount offset v_content);

DBMS_LOB close(v_bfile);

DBMS_OUTPUT PUT_LINE(v_content); lishixinzhi/Article/program/Oracle/201311/17151

分页：123