将Oracle数据库数据取至服务器--utl_file标准包和spool下载方式

1.取数到数据库服务器

配置一个目录（有读写权限）并给数据库用户授权

--select * FROM all_directories dir

create or replace directory CUX_LOG_DIR AS '/tmp/testdata'; --存放txt文件的目录,找一个有读写权限的目录

grant read, write on directory CUX_LOG_DIR to public;

调用utl_file标准包，生成文件。示例：

declare

l_loc all_directories.directory_name%TYPE := 'CUX_LOG_DIR';

l_file utl_file.file_type;

l_file_exsits BOOLEAN;

l_file_length NUMBER;

l_block_size BINARY_INTEGER;

l_buffer VARCHAR2(32767);

l_file_name VARCHAR2(300) := 'Z02_CASHFLOW' || '.txt';

BEGIN

l_file := utl_file.fopen(location => l_loc, --打开一个文件，基本上在我们对文件进行读写动作之前都需要先执行

filename => l_file_name,

open_mode => 'w');

l_buffer := 'TEST' || chr(9) || 'TEST2'; --取数并规定格式

utl_file.put(file => l_file,

buffer => l_buffer);

utl_file.fflush(file => l_file); --强制将缓冲的数据写入文件

utl_file.fclose_all;

end;

若出现UTL_FILE.INVALID_OPERATION异常，说明操作的目录权限不足，无法写入。

2.取数到应用服务器

脚本支持csv和txt

其中：

D:\DBoracle\lfc.csv 指定csv导出的路径

set colsep' '; //-域输出分隔符 

set newp none //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的 

set echo off; //显示start启动的脚本中的每个sql命令，缺省为on 

set echo on //设置运行命令是是否显示语句 

set feedback on; //设置显示“已选择XX行” 

set feedback off; //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行" 

set heading off; //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据 

set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。 

set linesize 80; //输出一行字符个数，缺省为80 

set numwidth 12; //输出number类型域长度，缺省为10 

set termout off; //显示脚本中的命令的执行结果，缺省为on 

set trimout on; //去除标准输出每行的拖尾空格，缺省为off 

set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off 

set serveroutput on; //设置允许显示输出类似dbms_output 

set timing on; //设置显示“已用时间：XXXX” 

set autotrace on-; //设置允许对执行的sql进行分析

示例：

set echo off --是否显示执行的命令内容

set feedback off --是否显示 * rows selected

set heading off --是否显示字段的名称

set verify off --是否显示替代变量被替代前后的语句

set trimspool on --去字段空格

set trimout on

set newp NONE --连续显示不要空行

set pagesize 2000 --页面大小

set linesize 300

set numwidth 15

spool /u01/oracle/testdata/z02_cashflow.txt

SELECT 'COMPANYCODE'|| CHR(9) ||'COMPANYDESC'|| CHR(9) ||'YEARID'|| CHR(9) ||'MONTHID'

|| CHR(9) ||'VERSION'|| CHR(9) ||'TIMEID'|| CHR(9) ||'PROJICTID'|| CHR(9) ||'PROJECTDESC'|| CHR(9) ||'AMOUNT'|| CHR(9) FROM dual;

SELECT l.companycode || chr(9) || l.companydesc || CHR(9) ||l.yearid || CHR(9) ||

l.monthid || chr(9) || l.version || CHR(9) ||l.timeid || CHR(9) ||

l.projectid || chr(9) || l.projectdesc || CHR(9) ||nvl(l.amount,0) || chr(9)

from z02_cashflow l;

spool off

exit

保存为.sql 文件

然后登陆应用服务器输入命令，即可下载数据至目标文件

有个问题，txt文件用记事本打开时会出现乱码和格式差异

生成文件后，增加命令转换成dos格式，unix2dos即可

同时增加抬头输出，注意属性set newp NONE 来控制不同块之间没有空行

功能可以实现，接下来使用shell脚本完成并发请求调用

编写prog文件，内容是shell脚本

prog文件可以直接被主机类型的并发请求调用。将其放到$CUX_TOP/bin目录下，权限755，并创建link

 ln -s $FND_TOP/bin/fndcpesr $CUX_TOP/bin/ downloadspool

小技巧，使用set -x 和 set +x启用shell的trace跟踪，可以方便诊断代码。查看Trace如下

挂上请求，类型为主机，运行请求。

检查在应用服务器目录下生成了对应数据文件以及格式是否规范