一、
教你一招不用编程序的:
1: 在pl/sql dev里面把这个表的内容SELECT出来;
2:拷贝,复制到EXCEL中
3:把EXCEL文件另存为 .CSV文件
4:用记事本把.CSV文件打开!
出来了
----------------太傻蛋了
二、
set linesize 1000
set pagesize 0
set echo off
set termout off
set heading off
set feedback off
spool c:\test\try2.txt
select id||','||user_name||','||age from
us;
spool off
/
------------------------生成的txt需要掐头去尾,对超大表没法搞
三、使用utl_file包
#首先声明存储生成txt文件的目录,注意oracle要有写权限,其权限不能由自己赋给自己,必须由sysbas赋给
create directory DIR_DUMP as '/home/oracle/';
conn / as sysdba
grant read,write on directory dir_dump to
psbc; (或者是public;)
CREATE OR REPLACE PROCEDURE xixi
IS
declare testjiao_handle UTL_FILE.file_type;
BEGIN
testjiao_handle :=
UTL_FILE.FOPEN('DIR_DUMP','testjiao.txt','w');
FOR x IN
(SELECT * FROM TESTJIAO) LOOP
UTL_FILE.PUT_LINE(testjiao_handle,x.ID || ',' || x.RQ ||',');
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
UTL_FILE.FCLOSE(testjiao_handle);
END;
/
#执行发现编译未成功
show error
发现testjiao_handle UTL_FILE.file_type,已经写到命名块里了,不需要declare了
CREATE OR REPLACE PROCEDURE xixi
IS
testjiao_handle UTL_FILE.file_type;
BEGIN
testjiao_handle :=
UTL_FILE.FOPEN('DIR_DUMP','testjiao.txt','w');
FOR x IN
(SELECT * FROM TESTJIAO) LOOP
UTL_FILE.PUT_LINE(testjiao_handle,x.ID || ',' || x.RQ ||',');
END
LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
UTL_FILE.FCLOSE(testjiao_handle);
END;
/
执行成功了
exec xixi
txt文件也生成了,但是里面没有数据。为什么?
因为把 Fclose写进异常里了,必须退出这个session,才会写进txt文件。
exit
发现txt有数据了
因为退出以后 那个 Fopen 就自动关了 否则 那个文件一直是被它打开并没有关闭
###还要继续完善这个存储过程,要把 Fclose 拿出来
###########################################################
CREATE OR REPLACE PROCEDURE xixi
IS
testjiao_handle UTL_FILE.file_type;
BEGIN
testjiao_handle :=
UTL_FILE.FOPEN('DIR_DUMP','testjiao.txt','w');
FOR x IN
(SELECT * FROM TESTJIAO) LOOP
UTL_FILE.PUT_LINE(testjiao_handle,x.ID || ',' || x.RQ ||',');
END
LOOP;
UTL_FILE.FCLOSE(testjiao_handle);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
END;
/
############################################################
exec xixi
more testjiao.txt
1,10-NOV-09,
2,20-DEC-10,
3,15-SEP-10,
这次真正ok,了。
感谢null网友的指导,在此向你致敬。
#可查看directory里全部定义路径
select * from dba_directories;
drop directory
exp_dir;
最后转下
UTL_File的使用
数据库 2009-11-30
16:31:34 阅读79
评论0 字号:大中小
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