论将数据从数据库导出为dat格式.docx

论将数据库中的数据导出为dat格式一、写作目的：在生产过程中，往往需要将数据库中的某个表或者视图中的数据导出为某固定格式的文本文件，如txt、dat或xls等格式。本文以导出dat文件为例，详述在EBS环境中，如何根据客户需求将数据库中满足条件的的数据导出为dat文件。二、客户需求：财务系统每日向ODS系统提供债券模块信息表XXBOCQ_BND_BOOK_B全量数据，保留原格式，不做任何筛选转换调整。1、文件命名和格式： 格式： dat文件、ok文件和tar文件。命名： XXBOCQ_BND_BOOK_B.dat和finish_日期.ok（如：finish_20160525.ok）和fin_日期.tar (如：fin_20160525.tar);注：dat文件为全量数据文件，每个字段之间以“|”隔开，并且每行数据结尾以“|”结尾。ok文件为生成dat文件后生成的就绪文件。tar文件为将dat文件打包后的文件，20160525为数据库当前日期。 2、文件存放路径：应用服务器的/ebsap/appl/outpathfile路径下，如果没有ODSFile目录，则创建ODSFile目录，每日在ODSFile目录下，创建以当日日期为名称的目录，存放dat文件，ok文件以及tar文件。 3、文件字符集：传输文件采用GBK字符集编码。三、实现思路：整体思路：先通过提交EBS请求来生成dat文件和ok文件，然后再通过一个请求将生成的dat文件打包，并在固定的路径下生成目标目录，并将tar文件移动到目标路径。第一步：先生成dat文件。有两种生成dat文件方式： 1：通过oracle的spool命令，生成dat文件。 2：通过oracle的utl_file包生成dat文件。下面两种方法都做一介绍。第二步：生成tar文件，并移动至目标路径。通过shell编程，将文件tar包并移动。四、实现方法： A、通过spool命令生成dat文件。1、spool简介：通过spool 命令，可以将select 数据库的内容写到文件中，通过在sqlplus设置一些参数，使得按指定方式写到文件中。spool命令的语法如下：spool file_name create|replace|append off|out其中file_name指定一个操作系统文件create将创建一个指定的file_name文件replace如果指定的文件已经存在，则替换该文件append将内容附加到一个已经存在的文件中off停止将sql*plus中的输出结果复制到file_name文件中，并关闭该文件。out启动该功能，将sql*plus中的输出结构复制到file_name中。2.spool常用的设置 set colsep ; /域输出分隔符set echo off;/显示start启动的脚本中的每个sql命令，缺省为on set feedback off;/回显本次sql命令处理的记录条数，缺省为onset heading off; /输出域标题，缺省为onset pagesize 0;/输出每页行数，缺省为24,为了避免分页，可设定为0set termout off; /显示脚本中的命令的执行结果，缺省为on set trimout on;/去除标准输出每行的拖尾空格，缺省为off set trimspool on;/去除重定向（spool）输出每行的拖尾空格，缺省为off 输出文本数据的建议格式： SQL*PLUS环境设置SET NEWPAGE NONE SET HEADING OFF SET SPACE 0 SET PAGESIZE 0 SET TRIMOUT ON SET TRIMSPOOL ON SET LINESIZE 2500 此值根据具体数据情况设定，太小易被截断，太大会降低导出的速度。3、编写shell脚本生成dat文件。脚本文件为XXBOCQ.prog文件，将其挂一个EBS请求，通过EBS请求来执行其中的内容，主要生成XXBOCQ_BND_BOOK_B.dat文件。XXBOCQ.prog脚本如下：LOGIN=$1FND=$5YJH=$6DATE=$7echo login:$LOGINecho filename:$FNDecho okname:$YJHecho date:$DATEexport NLS_LANG=American_America.ZHS16GBKcd /ebsap/appl/outfilepathsqlplus -s $LOGIN /dev/null END SET NEWPAGE NONE SET LINESIZE 2000 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET TERMOUT OFF SET TRIMSPOOL ON SET WRAP OFF SET SERVEROUTPUT OFF SPOOL $FND SELECT BN.BOND_ID | | | BN.TRANSACTION_ID | | | BN.ACTIVITY_CODE | | | to_char(BN.AS_OF_DATE,yyyymmdd) | | | BN.INITIAL_COST | | | BN.INVEST_GAIN | | | BN.RECEIVABLE_INT | | | BN.INT_ADJUST | | | BN.MKT_VALUE_CHANGE | | | BN.ACCOUNTABLE_INT | | | BN.MKT_VALUE | | | BN.AMORTIZED_COST | | | BN.INTEREST_INCOME | | | BN.REPRICED | | | BN.SORT_SEQ | | | BN.MKT_VALUE_CHG_GAIN | | | BN.CAP_ACCUMULATION | | | BN.TRANS_FEE | | | BN.OTHER_PAYABLE | | | BN.DEVALUE_RESERVE | | | BN.DEVALUE_LOST | | | BN.ISSUE_FEE | | FROM xxbocq_bnd_book_b BN WHERE 1 = 1; SPOOL OFF; /ENDif -f $FND ; then touch $YJH; if ! -d /ebsap/appl/outfilepath/ODS_DataFile/$DATE ; then mkdir -p /ebsap/appl/outfilepath/ODS_DataFile/$DATE fi mv $FND /ebsap/appl/outfilepath/ODS_DataFile/$DATE mv $YJH /ebsap/appl/outfilepath/ODS_DataFile/$DATE cd /ebsap/appl/outfilepath/$DATE tar cvf $DATE_fin.tar *.datelse echo NO FILE OUTPUT; exit 1;fiecho FTP OKexit 0通过以上脚本可以生成dat文件和ok文件，并且将dat文件打包，并且将tar文件和ok文件移动到目标路径/ebsap/appl/outfilepath/ODS_DataFile/$DATE下。其中FND=$5 是由请求传进来的参数，是dat文件的名称：XXBOCQ_BND_BOOK_B.datYJH=$6 是请求传进来的参数，是ok文件的名称:finish_fin.ok；DATE=$7 是请求传进来的参数，日结日期。通过export NLS_LANG=American_America.ZHS16GBK这一命令将dat文件的字符集设置为GBK。在EBS中将以上脚本可以创建为一个并发请求，可执行为：。如何通过另一个请求调用xxbocq请求:在中建一个包cux_xxbocq_bnd_pkg ，写一个存储过程xxbocq_bnd_book，通过fnd_request.submit_request函数提交并发请求xxbocq，执行以上代码，直接在应用服务器上输出目标文件。cux_xxbocq_bnd_pkg包代码如下：create or replace package cux_xxbocq_bnd_pkg is procedure xxbocq_bnd_book(errbuf OUT VARCHAR2, retcode OUT VARCHAR2);end cux_xxbocq_bnd_pkg;create or replace package body cux_xxbocq_bnd_pkg is procedure xxbocq_bnd_book(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) is v_date varchar2(10); v_filename varchar2(100); v_okname varchar2(100); v_request_id number; v_resualt boolean; x_phase varchar2(20); x_status VARCHAR2(20); x_dev_phase VARCHAR2(20); x_dev_status VARCHAR2(20); x_message VARCHAR2(20); v_okfile utl_file.file_type; begin select to_char(t.trx_date,yyyymmdd) INTO v_date from cux_transaction_date_t t where t.status in (P,S); v_filename := XXBOCQ_BND_BOOK_B.dat; v_okname := finish_fin.ok; FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 数据生成完成 | v_filename); v_request_id := fnd_request.submit_request(CUX, -application, XXBOCQ, - , -description, , -start_time, FALSE, -sub_request, v_filename, v_okname, v_date, chr(0), , , , , -每行个参数 , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ); commit; FND_FILE.PUT_LINE(fnd_file.OUTPUT, 传数请求ID: | v_request_id); V_RESUALT := fnd_concurrent.wait_for_request(v_request_id, 1, 0, x_phase, x_status, x_dev_phase, x_dev_status, x_message); commit;exception when others then retcode := 2; errbuf := SQLERRM;end cux_xxbocq_bnd_pkg；在EBS中，建立一个并发请求CUX:向ODS系统输出数据，执行上述代码，调用xxbocq程序，完整的完成向ODS传数的母的。下面介绍fnd_request.submit_request和fnd_concurrent.wait_for_request的用法。关于 fnd_request.submit_request 的用法 FND_REQUEST.SUBMIT_REQUEST 函数是用来提交一个请求的 , 它返回一个 NUMBER 值 . 具体调用如下 :result := fnd_request.submit_request(application CHAR, -AP 模快 program CHAR, - 应用程序 description CHAR, - 请求说明 ( 可选 ) start_time CHAR, -RUN 时间 ( 可选 ) sub_request BOOLEAN, - 是否作为子请求提交 argument1 CHAR, - 参数 1 argument2 CHAR, - 参数 2 argument3 CHAR, - 参数 3 argument4 CHAR, - 参数 4 argument5 CHAR, - 参数 5. argument100 CHAR);说明： 在用 fnd_request.submit_request 的时候，第五个参数用 false ，不要被参数名称误导； 这个函数有 105 个参数，前面五个定义请求本身，后面 100 个是传递给请求的具体参数，都是 Char 类型， 我们需要转换，默认值是 chr(0) ，代表这个参数不用传递给调用的请求； 在 Package 里面调用只需要传递需要的参数个数，因为它有默认值指示结束； 在 form 里面则不行，要写满 105 个，而且我们参数结束之后要用一个 chr(0) 来表示结束。FND_REQUEST.SUBMIT_REQUEST是一种通过后台方式提交请教的方法，可以在pkg和form中使用，在form中使用要将参数写全。FND_CONCURRENT.WAIT_FOR_REQUEST是一个等待当前请求运行完毕的程序，可以利用这个等待当前的请求程序运行完毕再运行下面的程序。Fnd_concurrent.wait_for_request返回Boolean值，主要参数如下： function FND_CONCURRENT.WAIT_FOR_REQUEST (request_id IN number default NULL, -请求ID interval IN number default 60, -检查时间间隔 max_wait IN number default 0, -最大等待时间 phase OUT varchar2, status OUT varchar2, dev_phase OUT varchar2, -请求运行阶段 dev_status OUT varchar2, -各个阶段状态 message OUT varchar2 -运行完成后输出信息) return boolean; dev_phase有Pending,Running,Complete,Inactive等几种，每种对应不同的Dev-Status，比如Complete阶段后就有Normal,Error,Warning,Cancelled,Terminated等几种状态。B、通过utl_file包输出dat文件:1、首先设置utl_file_dir参数：首先通过showparameterutl_file查看当前utl_file_dir目录，还可以通过以下语句重新设置utl_file_dir参数:altersystemsetutl_file_dir= /EBSDB/OUTFILEPATH/ODSFILEPATHscope=spfile需要注意的是，设置完成后要重启数据库，否则不生效。2、utl_file包简介： A写出文件使用utl_file写出文件，通过查询库中内容，写出到指定服务器路径下，总体过程如下： （1）通过UTL_FILE.FOPEN方法找到对应路径，创建文件，并且给出写入规则。 （2）通过UTL_FILE.PUT_LINE方法向文件中写入内容（UTL_FILE.PUT_LINE写入VARCHAR2类型数据，UTL_FILE.PUT_RAW方法是写入RAW类型的数据，一般来说RAW容量更大，用的更加广泛），这里由于ORACLE有长度限制，一般采用循环方式分批写入。 （3）写入完成后，通过UTL_FILE.FCLOSE方法关闭文件，结束写出。B 读入文件使用utl_file读入文件，通过读取指定文件，将读取的内容写入库中，总体过程如下： （1）通过UTL_FILE.FOPEN方法找到对应路径，读取文件（文件一定要存在），并且给出读入规则。 （2）通过UTL_FILE.GET_LINE方法循环向变量中写入内容（UTL_FILE.PUT_LINE写入VARCHAR2类型数据，UTL_FILE.PUT_RAW方法是写入RAW类型的数据，一般来说RAW容量更大，用的更加广泛），这里由于逐行读取，所以要循环操作（报NO_DATA_FOUND异常，即没有数据后跳出循环）。 （3）将内容变量INSERT到指定库表内 （4）写入完成后，通过UTL_FILE.FCLOSE方法关闭文件，结束读入。3、通过创建一个包XXBOCQ_BND_PKG,通过utl_file包，将数据写入到XXBOCQ_BND_BOOK_B.dat中。并且生成ok文件。包XXBOCQ_BND_PKG如下：create or replace package XXBOCQ_BND_PKG is PROCEDURE XXBOCQ(ERROR_CODE OUT VARCHAR2, ERROR_BUFF OUT VARCHAR2);end XXBOCQ_BND_PKG;create or replace package body XXBOCQ_BND_PKG is PROCEDURE XXBOCQ(ERROR_CODE OUT VARCHAR2, ERROR_BUFF OUT VARCHAR2) IS CURSOR BO_CUR IS SELECT BN.BOND_ID, BN.TRANSACTION_ID, BN.ACTIVITY_CODE, to_char(BN.AS_OF_DATE, yyyymmdd), BN.INITIAL_COST, BN.INVEST_GAIN, BN.RECEIVABLE_INT, BN.INT_ADJUST, BN.MKT_VALUE_CHANGE, BN.ACCOUNTABLE_INT, BN.MKT_VALUE, BN.AMORTIZED_COST, BN.INTEREST_INCOME, BN.REPRICED, BN.SORT_SEQ, BN.MKT_VALUE_CHG_GAIN, BN.CAP_ACCUMULATION, BN.TRANS_FEE, BN.OTHER_PAYABLE, BN.DEVALUE_RESERVE, BN.DEVALUE_LOST, BN.ISSUE_FEE FROM xxbocq_bnd_book_b BN WHERE 1 = 1; v_filename VARCHAR2(10); v_okname VARCHAR2(10); v_file utl_file.file_type; v_buffer VARCHAR2(4000); v_filepath VARCHAR2(200) := /EBSDB/OUTFILEPATH/ODSFILEPATH; BEGIN v_filename := XXBOCQ_BND_BOOK_B.dat; v_okname := finish_fin.ok; FOR BN IN BO_CUR LOOP v_buffer := ; v_buffer := convert(BN.BOND_ID | | | BN.TRANSACTION_ID | | | BN.ACTIVITY_CODE | | | to_char(BN.AS_OF_DATE,yyyymmdd) | | | BN.INITIAL_COST | | | BN.INVEST_GAIN | | | BN.RECEIVABLE_INT | | | BN.INT_ADJUST | | | BN.MKT_VALUE_CHANGE | | | BN.ACCOUNTABLE_INT | | | BN.MKT_VALUE | | | BN.AMORTIZED_COST | | | BN.INTEREST_INCOME | | | BN.REPRICED | | | BN.SORT_SEQ | | | BN.MKT_VALUE_CHG_GAIN | | | BN.CAP_ACCUMULATION | | | BN.TRANS_FEE | | | BN.OTHER_PAYABLE | | | BN.DEVALUE_RESERVE | | | BN.DEVALUE_LOST | | | BN.ISSUE_FEE | | , ZHS16GBK); utl_file.put_line(v_file, v_buffer); END LOOP; UTL_FILE.fclose(V_FILE); END;end XXBOCQ_BND_PKG;至此，已经将数据库表中的数据导入到dat文件内，并且已经生成了ok文件，还需要将dat文件打包，并且将文件放在应用服务器上。注：目前生成的dat文件和ok