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

回答如下：

1) 开始->运行->cmd.exe

2) d:

3) cd d:\text

4) sqlplus [username]/[password]@[tnsnames] （中括号内的值为你的数据库信息，请自行修改，此句后进入sqlplus状态）

5) set pagesize 1000 --确保不换行

6) set linesize 1000 --确保不折行

7) spool f1.txt

8) select [column1]||'^'||[column2]||'^'||[column3]||.... from emp-- （中括号内的字段为emp表的所有字段，请按顺序逐一穷举完毕）告察

9) spool off

10) drop table emp3 purge--如袜闭茄果此前未存在emp3表，此句可省

11) create table emp3 as select * from emp where 1=2--创建emp3，结构与emp一模一样

12) host --此句可暂时回到命令行状态

14) notepad mycontrol.ctl 编辑sql loader的控制文件

15) load data infile 'f1.txt' truncate into table emp3 fields terminated by '^' ([column1],[column2],[column3],...) --（中括号内的字段为emp表的所有字段，请按顺序逐一穷举完毕，然后存盘退出，回到命令行状态）

16) sqlldr userid=[username]/[password]@[tnsnames] control=mycontrol.ctl 运行sqlldr命令，以装载数据

17) exit 再次回到sqlplus环境

18) select * from emp3--检验工作成果

回答完毕，请给分。

--补充回答如下：

--第一步，生成emp.txt

sqlplus scott/tiger@tnsname

set pagesize 1000

set linesize 1000

--以下目录可自行设定，为讨论，不妨设为d:\temp

spool d:\temp\emp.txt

select

'^'||EMPNO

||'^'||ENAME

||'^'||JOB

||'^'||MGR

||'^'||HIREDATE

||'^'||SAL

||'^'||COMM

||'^'||DEPTNO||'^' --注意首尾处理，确保每个字段均被夹在'^'号中间，以简化后续计算

from emp order by empno

spool off

--第二步，整理emp.txt（ *** 作系统下用notepad打开此文件，掐头去尾，留下中间干净的数据）

--第三步，导入emp3表

--3.1 创建emp3表

declare

v_num integer

v_TableName varchar2(40)

v_PurgeClause varchar2(10)

begin

v_TableName:='emp3'

v_PurgeClause:=' purge'

select count(1) into v_num from user_tables where table_name=upper(v_Tablename)

if(v_num>0)then

execute immediate 'drop table '||v_TableName||v_PurgeClause

end if

end

create table emp3 as select * from emp where 1=2--oracle 11g以上，此处可能出现ORA-01536错，请自行解决

--3.2 创建一个oracle目录，指向 *** 作系统的目标目录，例如d:\temp，并授权给scott

-- 如果您的oracle是windows版本，那么可如下撰写

-- 如果您的oracle是linux之类的版本，做法类似，只不过需要先将前面的emp.txt ftp至linux系统对应目录，此处态档不赘述此类情形

create or replace directory empdir as 'd:\temp'

grant read on directory empdir to scott

--3.3 开始利用utl_file灌录数据

declare

v_fp utl_file.file_type

v_line varchar2(1024)

v_int1 integer

v_int2 integer

--由于oracle可以隐式转换，字段变量可简单定义成varchar2

v_EMPNOvarchar2(100)

v_ENAMEvarchar2(100)

v_JOB varchar2(100)

v_MGR varchar2(100)

v_HIREDATE varchar2(100)

v_SAL varchar2(100)

v_COMM varchar2(100)

v_DEPTNO varchar2(100)

begin

v_fp:=utl_file.fopen('EMPDIR','emp.txt','r')--此处务必大写目录名，否则出错

loop

begin

utl_file.get_line(v_fp,v_line)

exception

when others then

dbms_output.put_line('处理完毕')

exit

end

--第一个比较特殊处理

v_int1:=instr(v_line,'^',1,1)

v_int2:=instr(v_line,'^',1,2)

v_empno:=substr(v_line,v_int1+1,v_int2-v_int1-1)

--以后均统一处理

v_int1:=v_int2