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_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

该问题可能由于转储文件的编码出现问题导致。你可以尝试使用WLS_TOOL工具来重编码，或者直接在SQL Plus中运行它。另外，可以检查转储文件本身是否有错误，比如是否有被破坏的部分。

---